get_results( "SELECT *,wp_affiliate_wp_referrals.referral_id AS resellerReferralId , wp_affiliate_wp_referrals.affiliate_id AS resellerAffiliateId, (SELECT wp_affiliate_wp_customers.user_id FROM wp_affiliate_wp_customers WHERE wp_affiliate_wp_customers.customer_id = wp_affiliate_wp_referrals.customer_id) as resellerWpUserId , (SELECT wp_users.user_email FROM wp_users WHERE wp_users.ID = resellerWpUserId) as resellerWpEmailAddress, (SELECT wp_users.user_login FROM wp_users WHERE wp_users.ID = resellerWpUserId) as resellerAccessCode, wp_affiliate_wp_referrals.description as productName , pm1.post_id as wpOrderId ,pm1.meta_value as buyerWpUserId, wp_affiliate_wp_affiliates.affiliate_id as buyerAffiliateId, (SELECT wp_users.user_email FROM wp_users WHERE wp_users.ID = buyerWpUserId) as buyerWpEmailAddress , pm2.meta_value as tr FROM `wp_posts` LEFT JOIN wp_postmeta pm1 ON wp_posts.ID = pm1.post_id AND pm1.meta_key = '_customer_user' LEFT JOIN wp_affiliate_wp_affiliates ON wp_affiliate_wp_affiliates.user_id = pm1.meta_value LEFT JOIN wp_affiliate_wp_referrals ON wp_affiliate_wp_referrals.affiliate_id = wp_affiliate_wp_affiliates.affiliate_id LEFT JOIN wp_postmeta pm2 ON wp_posts.ID = pm2.post_id AND pm2.meta_key = '_paid_date' WHERE pm2.meta_value >= '".$startDate."' AND pm2.meta_value <= '".$endDate."' AND pm1.meta_value != ''
UNION
SELECT *,wp_affiliate_wp_referrals.referral_id AS resellerReferralId , wp_affiliate_wp_referrals.affiliate_id AS resellerAffiliateId, (SELECT wp_affiliate_wp_customers.user_id FROM wp_affiliate_wp_customers WHERE wp_affiliate_wp_customers.customer_id = wp_affiliate_wp_referrals.customer_id) as resellerWpUserId, (SELECT wp_users.user_email FROM wp_users WHERE wp_users.ID = resellerWpUserId) as resellerWpEmailAddress, (SELECT wp_users.user_login FROM wp_users WHERE wp_users.ID = resellerWpUserId) as resellerAccessCode, wp_affiliate_wp_referrals.description as productName, pm1.post_id as wpOrderId ,pm1.meta_value as buyerWpUserId, wp_affiliate_wp_affiliates.affiliate_id as buyerAffiliateId, (SELECT wp_users.user_email FROM wp_users WHERE wp_users.ID = buyerWpUserId) as buyerWpEmailAddress , pm2.meta_value as tr FROM `wp_posts` RIGHT JOIN wp_postmeta pm1 ON wp_posts.ID = pm1.post_id AND pm1.meta_key = '_customer_user' RIGHT JOIN wp_affiliate_wp_affiliates ON wp_affiliate_wp_affiliates.user_id = pm1.meta_value RIGHT JOIN wp_affiliate_wp_referrals ON wp_affiliate_wp_referrals.affiliate_id = wp_affiliate_wp_affiliates.affiliate_id RIGHT JOIN wp_postmeta pm2 ON wp_posts.ID = pm2.post_id AND pm2.meta_key = '_paid_date' WHERE pm2.meta_value >= '".$startDate."' AND pm2.meta_value <= '".$endDate."' AND pm1.meta_value != '' ORDER by tr ASC", OBJECT );
foreach ($results as $post)
{
$post->tomNotes = "";
$orderId = $post->wpOrderId;
$description = $post->productName;
$checkOrderId = get_post_meta($orderId, '_subscription_renewal', true);
$checkOrderId2 = get_post_meta($orderId, '_requires_manual_renewal', true);
// if($checkOrderId || $checkOrderId2) {
// foreach($inclusiveProducts as $product) {
// if(strtoupper($product) == strtoupper($description)) {
// get transaction Id
$orderId = $post->wpOrderId;
$userId = $post->buyerWpUserId;
/////////////getting entry URL/////////////
$createLeadRequest = get_post_meta( $orderId, 'createLeadRequest' ,true );
if($createLeadRequest) {
$createLeadRequest = json_decode($createLeadRequest);
$entryUrl = $createLeadRequest->trackingInfo->entryUrl;
$post->entryUrl = $entryUrl;
}
// ---------------------------------------
// VERY IMPORTANT (getting referrer) start
// ---------------------------------------
$queryForGettingCustId = "SELECT customer_id FROM wp_affiliate_wp_customers WHERE user_id = '".$userId."'";
$queryForGettingCustIdR = $wpdb->get_results($queryForGettingCustId);
if(!empty($queryForGettingCustIdR)) {
$refCustomerId = $queryForGettingCustIdR[0]->customer_id;
// $post->resellerAffiliateId = $refCustomerId;
//getting affiliateId from CustomerId
$queryForGettingAffId = "SELECT affiliate_id FROM `wp_affiliate_wp_referrals` WHERE customer_id = '".$refCustomerId."'";
$queryForGettingAffIdR = $wpdb->get_results($queryForGettingAffId);
if(!empty($queryForGettingAffIdR)) {
$refAffiliateId = $queryForGettingAffIdR[0]->affiliate_id;
//getting ref ref
$queryForGettingrefInfo = "SELECT * FROM `wp_affiliate_wp_affiliates` WHERE affiliate_id = '".$refAffiliateId."'";
$queryForGettingrefInfoR = $wpdb->get_results($queryForGettingrefInfo);
if(!empty($queryForGettingrefInfoR)) {
$resellerWPUserID = $queryForGettingrefInfoR[0]->user_id;
$resellerWpemailAddress = $queryForGettingrefInfoR[0]->payment_email;
$post->resellerAffiliateId = $refAffiliateId;
$post->resellerWpUserId = $resellerWPUserID;
$post->resellerWpEmailAddress = $resellerWpemailAddress;
$post->comStatus = "WP Affiliate";
} else {
if(isset($post->entryUrl)){
$url = $post->entryUrl;
$urlCopy = explode("/", $url);
// print_r($urlCopy);
$urlStatus = true;
foreach($urlCopy as $key => $val){
if($val == "ref"){
$resellerAccessCode = $urlCopy[$key+1];
$urlStatus = true;
return;
} else {
$urlStatus = false;
}
}
if(!$urlStatus) {
$parts = parse_url($url);
parse_str($parts['query'], $query);
$resellerAccessCode = $query['ref'];
}
if($resellerAccessCode != null || !empty($resellerAccessCode)) {
$queryForGettingReUId = "SELECT ID FROM `wp_users` WHERE user_login = '".$resellerAccessCode."'";
$queryForGettingReUIdR = $wpdb->get_results($queryForGettingReUId);
if(!empty($queryForGettingReUIdR)) {
$resellerWPUserIDByAc = $queryForGettingReUIdR[0]->ID;
$queryForGettingrefInfoByAc = "SELECT * FROM `wp_affiliate_wp_affiliates` WHERE user_id= '".$resellerWPUserIDByAc."'";
$queryForGettingrefInfoByAcR = $wpdb->get_results($queryForGettingrefInfoByAc);
if(!empty($queryForGettingrefInfoByAcR)) {
$resellerWPUserID = $queryForGettingrefInfoByAcR[0]->user_id;
$resellerWpemailAddress = $queryForGettingrefInfoByAcR[0]->payment_email;
$post->resellerAffiliateId = $queryForGettingrefInfoByAcR[0]->affiliate_id;
$post->resellerWpUserId = $resellerWPUserID;
$post->resellerWpEmailAddress = $resellerWpemailAddress;
$post->comStatus = "Entry URL";
}
}
}
}
}
}
}
// ---------------------------------------
// VERY IMPORTANT (getting referrer) end
// ---------------------------------------
// $order = wc_get_order( $orderId );
// $order_data = $order->get_data();
$date_created = $post->post_date;
// $test = date_format("2020-02-02","Y/m/d H:i:s");
$time = strtotime($date_created);
$newformat = date('m/d/yy h:m A',$time);
$post->date_created = $newformat;
$cFirstName = get_post_meta( $orderId, '_billing_first_name' ,true );
$cLastName = get_post_meta( $orderId, '_billing_last_name' ,true );
$orderTotal = get_post_meta( $orderId, '_order_total' ,true );
$transactionId = get_post_meta( $orderId, '_transaction_id' ,true );
$customerUser = get_post_meta( $orderId, '_customer_user' ,true );
$authorizationCode = get_post_meta( $orderId, '_wc_authorize_net_cim_credit_card_authorization_code' ,true );
$chargeCaptured = get_post_meta( $orderId, '_wc_authorize_net_cim_credit_card_charge_captured' ,true );
$getUFirstName = get_user_meta( $userId, 'first_name', true );
$getUlastName = get_user_meta( $userId, 'last_name', true );
$post->customerName = $getUFirstName . " " . $getUlastName;
$getUNiceName = get_user_meta( $userId, 'nickname', true );
$post->affNiceName = $getUNiceName;
$post->cFirstName = $getUFirstName;
$post->cLastName = $getUlastName;
$post->orderTotal = $orderTotal;
$post->transactionId = $transactionId;
$post->authorizationCode = $authorizationCode;
///add reseller full name
if(isset($post->resellerWpUserId)) {
$pUserId = $post->resellerWpUserId;
$getPFirstName = get_user_meta( $pUserId, 'first_name', true );
$getPlastName = get_user_meta( $pUserId, 'last_name', true );
$post->resellerFullName = $getPFirstName . " " . $getPlastName;
$post->resellerFirstName = $getPFirstName;
$post->resellerLastName = $getPlastName;
$post->getPNiceName = get_user_meta( $pUserId, 'nickname', true );
} else {
$post->resellerFullName = "Codebreaker Corporate";
$post->resellerFirstName = "Codebreaker";
$post->resellerLastName = "Corporate";
$post->resellerWpUserId = 11;
$post->resellerWpEmailAddress = "bank@bankcode.com";
$post->getPNiceName = "Bank";
}
///
if($chargeCaptured) {
$post->chargeCaptured = "CMPLT";
} else {
$post->chargeCaptured = "DECLINED";
}
$post->amount = round($post->amount);
$postStatus = get_post_status($orderId );
$post->orderSatus = $postStatus;
$post->customerUser = $customerUser;
$emailAddress = $post->buyerWpEmailAddress;
$post->cv = round($orderTotal);
if($post->cv == 1) {
$post->cv = 0;
}
foreach($roundValues as $cv) {
if($orderTotal == $cv) {
$post->cv = $orderTotal + 1;
}
}
// $post->newRate = $post->rate / 100;
// $comn = $post->orderTotal * $post->newRate;
// $post->amount = round($comn);
$resellerWpEmailAddress = $post->resellerWpEmailAddress;
foreach($emails20 as $rate) {
if(strtoupper($resellerWpEmailAddress) == strtoupper($rate)) {
$post->newRate = 0.2;
$comn = $post->orderTotal * 0.2;
$post->amount = round($comn);
}
}
foreach($fortyPercentUsers as $rate) {
if(strtoupper($resellerWpEmailAddress) == strtoupper($rate)) {
$post->newRate = 0.4;
$comn = $post->orderTotal * 0.4;
$post->amount = round($comn);
}
}
// ---------------------------------------------
$transactionDate = get_post_meta( $orderId, '_paid_date' ,true );
$post->transactionDate = $transactionDate;
array_push($originalData, $post);
}
// } }
$originalDataCopy = $originalData;
$names = [];
$withUniqueNames = array_reduce(
$originalDataCopy,
function ($withUniqueNames, $object) use (&$names) {
if (!in_array($object->transactionId, $names)) {
$names[] = $object->transactionId;
$withUniqueNames[] = $object;
}
return $withUniqueNames;
},
[]
);
$originalDatatest = [];
$ahmad = [];
// echo "";
// print_r($withUniqueNames);
// echo "
";
foreach($withUniqueNames as $key1 => $val) {
if($val->chargeCaptured != "DECLINED") {
if($val->post_status == "wc-processing") {
// if($val->orderTotal != 1.00 ) {
// $buyerWpUserId = $val->buyerWpUserId;
// $rs = $wpdb->get_results("SELECT post_id FROM wp_postmeta WHERE meta_key = '_customer_user' AND meta_value = '".$buyerWpUserId."' ORDER BY post_id ASC LIMIT 1");
// if (!empty($rs)) {
// $parentWPOrderId = $rs[0]->post_id;
// } else {
$parentWPOrderId = 0;
// }
$orderId = $val-> wpOrderId;
$order = wc_get_order ($orderId);
$items = $order-> get_items ();
$itemsCount = count($items);
$i = 0;
if($itemsCount > 1) {
unset($results[$val]);
foreach ( $items as $key => $item ) {
$postCopy = $val;
$postCopy->orderTotal = $item->get_subtotal();
$postCopy->description = $item->get_name();
$postCopy->parentWPOrderId = $parentWPOrderId;
$orderTotal = $postCopy->orderTotal;
$postCopy->cv = round($orderTotal);
if($postCopy->cv == 1) {
$postCopy->cv = 0;
}
foreach($roundValues as $cv) {
if($orderTotal == $cv) {
$postCopy->cv = $orderTotal + 1;
}
}
$newRate = $postCopy->newRate;
$newRate = str_replace("%", "", $newRate);
$newRate = $newRate * 100;
$newRate = $newRate / 100;
$comn = $postCopy->orderTotal * $newRate;
$postCopy->amount = round($comn);
$postCopy = (array) $postCopy;
$ahmad[] = $postCopy;
}
} else {
foreach ( $items as $key => $item ) {
$postCopy = $val;
$postCopy->description = $item->get_name();
$postCopy->parentWPOrderId = $parentWPOrderId;
$postCopy = (array) $postCopy;
$ahmad[] = $postCopy;
}
}
// }
}
}
}
$results = $ahmad;
// echo "";
// print_r($results);
// echo "
";
// echo "before: ".count($results);
$ahmadkhalid = [];
// foreach($results as $k=>$v) {
// // echo $k.$v->description."
";
// foreach($inclusiveProducts as $product) {
// if(strtoupper($v['description']) === strtoupper($product)){
// // echo "inc: ".$product."
";
// // echo "db: ".$v['description']."
";
// // echo $product."
";
// // unset($withUniqueNames[$k]); //Delete from Array
// $ahmadkhalid[] = (array) $v;
// }
// }
// }
// echo "After: ".count($ahmadkhalid);
// echo "";
// print_r($ahmadkhalid);
// echo "
";
$reader = IOFactory::createReader('Xlsx'); // get report data
$spreadsheet = $reader->load("orderstemplate.xlsx"); // load the xlsx template
$startDate1 = strtotime($startDateName); // set tab name
$startDate1 = date('M d', $startDate1);
$endDate1 = strtotime($endDateName);
$endDate1 = date('M d, Y', $endDate1);
$tabName = $startDate1." to ".$endDate1;
$fileStartDate = strtotime($startDateName);
$fileStartDate = date('Y-md', $fileStartDate);
$fileEndDate = strtotime($endDateName);
$fileEndDate = date('md', $fileEndDate);
$fileName = "OrdersCommissionReport-".$fileStartDate."-to-".$fileEndDate.".xlsx";
// $fileName = str_replace("_", "", $fileName);
$spreadsheet->getActiveSheet()->setTitle($tabName);
//add the content
$currentStartRow = 2;
$currentContentRow = 2;
foreach($results as $result) {
$spreadsheet->getActiveSheet()->insertNewRowBefore($currentContentRow+1,1);
$transactionDate = strtotime($result['transactionDate']);
$transactionDateNew = strtotime($result['transactionDate']);
$transactionDateNew = date('m/d/yy h:i:sa', $transactionDateNew);
$transactionDate = date('m/d/yy h:i:sa', $transactionDate);
$transactionDatePST = date('m/d/yy', strtotime($transactionDateNew . ' -7 hours'));
$transactionTimePST = date('h:i:sa', strtotime($transactionDateNew . ' -7 hours'));
$date_created = strtotime($result['date_created']);
$date_created = date('m/d/yy', $date_created);
$date_createdDatePST = date('m/d/yy', strtotime($date_created . ' -7 hours'));
$date_createdTimePST = date('h:i:sa', strtotime($date_created . ' -7 hours'));
$tr = strtotime($result['tr']);
$tr = date('m/d/yy h:i:sa', $tr);
$spreadsheet-> getActiveSheet()
->setCellValue('A'.$currentContentRow , $result['wpOrderId'])
->setCellValue('B'.$currentContentRow , $transactionDatePST)
->setCellValue('C'.$currentContentRow , $transactionTimePST)
->setCellValue('D'.$currentContentRow , "")
->setCellValue('E'.$currentContentRow , $result['orderTotal'])
->setCellValue('F'.$currentContentRow , $result['cv'])
// ->setCellValue('E'.$currentContentRow , $result['parentOrderId'])$postCopy->parentOrderId = $parent_order_id;
->setCellValue('G'.$currentContentRow , $result['parentWPOrderId'])
->setCellValue('H'.$currentContentRow , $date_createdDatePST)
->setCellValue('I'.$currentContentRow , $date_createdTimePST)
->setCellValue('J'.$currentContentRow , $result['description'])
->setCellValue('K'.$currentContentRow , $result['buyerWpUserId'])
->setCellValue('L'.$currentContentRow , $result['post_status'])
->setCellValue('M'.$currentContentRow , $result['post_type'])
->setCellValue('N'.$currentContentRow , $result['comStatus'])
->setCellValue('O'.$currentContentRow , $result['buyerWpEmailAddress'])
->setCellValue('P'.$currentContentRow , $result['affNiceName'])
->setCellValue('Q'.$currentContentRow , str_replace('\' ', '\'', ucwords(str_replace('\'', '\' ', strtolower($result['customerName'])))))
->setCellValue('R'.$currentContentRow , str_replace('\' ', '\'', ucwords(str_replace('\'', '\' ', strtolower($result['cFirstName'])))))
->setCellValue('S'.$currentContentRow , str_replace('\' ', '\'', ucwords(str_replace('\'', '\' ', strtolower($result['cLastName'])))))
->setCellValue('T'.$currentContentRow , str_replace('\' ', '\'', ucwords(str_replace('\'', '\' ', strtolower($result['resellerFullName'])))))
->setCellValue('U'.$currentContentRow , str_replace('\' ', '\'', ucwords(str_replace('\'', '\' ', strtolower($result['resellerFirstName'])))))
->setCellValue('V'.$currentContentRow , str_replace('\' ', '\'', ucwords(str_replace('\'', '\' ', strtolower($result['resellerLastName'])))))
->setCellValue('W'.$currentContentRow , $result['resellerWpUserId'])
->setCellValue('X'.$currentContentRow , $result['resellerWpEmailAddress'])
->setCellValue('Y'.$currentContentRow , $result['getPNiceName'])
->setCellValue('Z'.$currentContentRow , "")
->setCellValue('AA'.$currentContentRow , "")
->setCellValue('AB'.$currentContentRow , "")
->setCellValue('AC'.$currentContentRow , $result['entryUrl'])
->setCellValue('AD'.$currentContentRow , $result['resellerAffiliateId'])
->setCellValue('AE'.$currentContentRow , $result['chargeCaptured'])
->setCellValue('AF'.$currentContentRow , $result['newRate'])
->setCellValue('AG'.$currentContentRow , $result['transactionId'])
->setCellValue('AH'.$currentContentRow , $result['authorizationCode'])
->setCellValue('AI'.$currentContentRow , $result['amount'])
->setCellValue('AJ'.$currentContentRow , "");
$currentContentRow++;
}
//remove
//set the header first, so the result will be treated as an xlsx file.
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
//make it an attachment so we can define filename
header('Content-Disposition: attachment;filename="'.$fileName.'"');
//create IOFactory object
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
ob_end_clean();
$writer->save('php://output');
exit();
//save into php output
?>