='".$_POST['from_date']."' and date(tb.order_date_time) <='".$_POST['to_date']."'"; $incr = 0; $alpha = 0; $beta = 0; $htmlTable= ''; $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); $sheet->setCellValue('A1', 'S.No'); $sheet->setCellValue('B1', 'PRODUCT NAME'); $sheet->setCellValue('C1', 'COMPANY'); $sheet->setCellValue('D1', 'PATIENT GST'); $sheet->setCellValue('E1', 'PATIENT CASH'); $sheet->setCellValue('F1', 'DEALER GST'); $sheet->setCellValue('G1', 'DEALER CASH'); $sheet->setCellValue('H1', 'HOSPITAL GST'); $sheet->setCellValue('I1', 'HOSPITAL CASH'); $sheet->getStyle('A1:I1')->getFill() ->setFillType(Fill::FILL_SOLID) ->getStartColor()->setARGB('FFFFA500'); // ORANGE $get_prod = mysqli_query($conn, "SELECT `name`, `product_id`, `product_url`, `vstatus` FROM shop_product"); while($set_prod = mysqli_fetch_assoc($get_prod)){ // echo "SELECT * FROM tbl_confirmorder tb inner join tbl_order tc on tb.orderid=tc.orderid where tb.orderstatus not in (90) and tc.productid='".$set_prod['product_id']."' $cond"; $get_order = mysqli_query($conn, "SELECT * FROM tbl_confirmorder tb inner join tbl_order tc on tb.orderid=tc.orderid where tb.orderstatus not in (90) and tc.productid='".$set_prod['product_id']."' $cond"); $check_rows = mysqli_num_rows($get_order); $get_comp = mysqli_query($conn, "SELECT * FROM tbl_company where id='".$set_prod['vstatus']."'"); $set_comp = mysqli_fetch_assoc($get_comp); $comp_name = $set_comp['name']; if($check_rows > 0){ $qty_total=0; $pat_gst=0; $pat_cash=0; $dealer_gst=0; $dealer_cash=0; $hosp_gst = 0; $hosp_cash = 0; $get_qty = mysqli_query($conn, "SELECT tc.qty, tc.freeqty, tb.offer FROM tbl_confirmorder tb inner join tbl_order tc on tb.orderid=tc.orderid where tb.orderstatus not in (90) and tc.productid='".$set_prod['product_id']."' $cond"); while($set_qty = mysqli_fetch_assoc($get_qty)){ if($set_qty['offer'] == 0 || $set_qty['offer'] == ''){ $qty_total+=($set_qty['qty'] + $set_qty['freeqty']); }else{ $qty_total+=$set_qty['qty']; } } // patient gst $get_qty = mysqli_query($conn, "SELECT tb.userid, tc.qty, tc.freeqty, tb.offer FROM tbl_confirmorder tb inner join tbl_order tc on tb.orderid=tc.orderid where tb.orderstatus not in (90) and tb.userid not in(11363) and tb.refid not like '%CO%' and tc.productid='".$set_prod['product_id']."' $cond"); while($set_qty = mysqli_fetch_assoc($get_qty)){ $get_user = mysqli_query($conn, "SELECT * FROM shop_user where new_status=0 and user_id='".$set_qty['userid']."'"); $pat_rows = mysqli_num_rows($get_user); if($pat_rows > 0){ if($set_qty['offer'] == 0 || $set_qty['offer'] == ''){ $pat_gst+=($set_qty['qty'] + $set_qty['freeqty']); }else{ $pat_gst+=$set_qty['qty']; } } } // patient cash $get_qty = mysqli_query($conn, "SELECT tb.userid, tc.qty, tc.freeqty, tb.offer FROM tbl_confirmorder tb inner join tbl_order tc on tb.orderid=tc.orderid where tb.orderstatus not in (90) and tb.userid not in(11363) and tb.refid like '%CO%' and tc.productid='".$set_prod['product_id']."' $cond"); while($set_qty = mysqli_fetch_assoc($get_qty)){ $get_user = mysqli_query($conn, "SELECT * FROM shop_user where new_status=0 and user_id='".$set_qty['userid']."'"); $pat_rows = mysqli_num_rows($get_user); if($pat_rows > 0){ if($set_qty['offer'] == 0 || $set_qty['offer'] == ''){ $pat_cash+=($set_qty['qty'] + $set_qty['freeqty']); }else{ $pat_cash+=$set_qty['qty']; } } } // Dealer GST $get_qty = mysqli_query($conn, "SELECT tb.userid, tc.qty, tc.freeqty, tb.offer FROM tbl_confirmorder tb inner join tbl_order tc on tb.orderid=tc.orderid where tb.orderstatus not in (90) and tb.userid not in(11363) and tb.refid not like '%CO%' and tc.productid='".$set_prod['product_id']."' $cond"); while($set_qty = mysqli_fetch_assoc($get_qty)){ $get_user = mysqli_query($conn, "SELECT * FROM shop_user where new_status in ($dealer_arr) and user_id='".$set_qty['userid']."'"); $pat_rows = mysqli_num_rows($get_user); if($pat_rows > 0){ if($set_qty['offer'] == 0 || $set_qty['offer'] == ''){ $dealer_gst+=($set_qty['qty'] + $set_qty['freeqty']); }else{ $dealer_gst+=$set_qty['qty']; } } } // Dealer Cash $get_qty = mysqli_query($conn, "SELECT tb.userid, tc.qty, tc.freeqty, tb.offer FROM tbl_confirmorder tb inner join tbl_order tc on tb.orderid=tc.orderid where tb.orderstatus not in (90) and tb.userid not in(11363) and tb.refid like '%CO%' and tc.productid='".$set_prod['product_id']."' $cond"); while($set_qty = mysqli_fetch_assoc($get_qty)){ $get_user = mysqli_query($conn, "SELECT * FROM shop_user where new_status in ($dealer_arr) and user_id='".$set_qty['userid']."'"); $pat_rows = mysqli_num_rows($get_user); if($pat_rows > 0){ if($set_qty['offer'] == 0 || $set_qty['offer'] == ''){ $dealer_cash+=($set_qty['qty'] + $set_qty['freeqty']); }else{ $dealer_cash+=$set_qty['qty']; } } } // Hospital gst $get_qty = mysqli_query($conn, "SELECT tb.userid, tc.qty, tc.freeqty, tb.offer FROM tbl_confirmorder tb inner join tbl_order tc on tb.orderid=tc.orderid where tb.orderstatus not in (90) and tb.userid in (11363,7444) and tb.refid not like '%CO%' and tc.productid='".$set_prod['product_id']."' $cond"); while($set_qty = mysqli_fetch_assoc($get_qty)){ if($set_qty['offer'] == 0 || $set_qty['offer'] == ''){ $hosp_gst+=($set_qty['qty'] + $set_qty['freeqty']); }else{ $hosp_gst+=$set_qty['qty']; } } // Hospital cash $get_qty = mysqli_query($conn, "SELECT tb.userid, tc.qty, tc.freeqty, tb.offer FROM tbl_confirmorder tb inner join tbl_order tc on tb.orderid=tc.orderid where tb.orderstatus not in (90) and tb.userid in (11363,7444) and tb.refid like '%CO%' and tc.productid='".$set_prod['product_id']."' $cond"); while($set_qty = mysqli_fetch_assoc($get_qty)){ if($set_qty['offer'] == 0 || $set_qty['offer'] == ''){ $hosp_cash+=($set_qty['qty'] + $set_qty['freeqty']); }else{ $hosp_cash+=$set_qty['qty']; } } $htmlTable.= ' '; $alpha = $alpha + 1; $sheet->setCellValue("A$alpha",++$beta); $sheet->setCellValue("B$alpha", strtoupper($set_prod['name'].' '.$set_prod['product_url'])); $sheet->setCellValue("C$alpha", $comp_name); $sheet->setCellValue("D$alpha", $pat_gst); $sheet->setCellValue("E$alpha", $pat_cash); $sheet->setCellValue("F$alpha", $dealer_gst); $sheet->setCellValue("G$alpha", $dealer_cash); $sheet->setCellValue("H$alpha", $hosp_gst); $sheet->setCellValue("I$alpha", $hosp_cash); } } $htmlTable.='

S.No	PRODUCT NAME	COMPANY	QTY	PAT-GST	PAT-CASH	DEAL-GST	DEAL-CASH	HOSP-GST	HOSP-CASH
'.($alpha=$incr=$incr+1).'	'.strtoupper($set_prod['name'].' '.$set_prod['product_url']).'	'.$comp_name.'	'.$qty_total.'	'.$pat_gst.'	'.$pat_cash.'	'.$dealer_gst.'	'.$dealer_cash.'	'.$hosp_gst.'	'.$hosp_cash.'

'; $savePath = __DIR__ . '/productwise_report/prod_report.xlsx'; $writer = new Xlsx($spreadsheet); $writer->save($savePath); echo $htmlTable; ?>