Member Avatar for nishita_1
nishita_1 15 Newbie Poster
FPDF Output Problem using below PHP code

Please Help me. I am coding PHP using below code but out put is not perfect. i am also attach output picture below.

<?php    

require_once 'core.php';
include_once("../fpdf182/fpdf.php");
$pdf = new FPDF();
  $pdf->AddPage();
$pdf->setFont("Arial","B",16);

$orderId = $_POST['orderId'];

$sql = "SELECT orders.order_id, orders.order_date, customer.customer_name, customer.customer_mobile, orders.sub_total, orders.vat, orders.total_amount, orders.discount, orders.grand_total, orders.paid, orders.due, orders.payment_place,orders.gstn FROM orders INNER JOIN customer ON customer.customer_id = orders.client_name WHERE orders.order_id = $orderId";

$orderResult = $connect->query($sql);
$orderData = $orderResult->fetch_array();

$orderNo = $orderData[0];
$orderDate = $orderData[1];
$clientName = $orderData[2];
$clientContact = $orderData[3]; 
$subTotal = $orderData[4];
$vat = $orderData[5];
$totalAmount = $orderData[6]; 
$discount = $orderData[7];
$grandTotal = $orderData[8];
$paid = $orderData[9];
$due = $orderData[10];
$payment_place = $orderData[11];
$gstn = $orderData[12];

if ($orderDate && $orderNo) {

  $pdf->Cell(190,10,"Invoice",0,1,"C");

  $pdf->Cell(50,10,"Date",0,0);
  $pdf->Cell(50,10,": ".$orderDate,0,1);
  $pdf->Cell(50,10,"Customer Name",0,0);
  $pdf->Cell(50,10,": ".$clientName,0,1);

  $pdf->Cell(50,10,"",0,1);

  $pdf->Cell(10,10,"#",1,0,"C");
  $pdf->Cell(70,10,"Product Name",1,0,"C");
  $pdf->Cell(30,10,"Quantity",1,0,"C");
  $pdf->Cell(40,10,"Price",1,0,"C");
  $pdf->Cell(40,10,"Total (Tk)",1,1,"C");

$orderItemSql = "SELECT order_item.product_id, order_item.rate, order_item.quantity, order_item.total,
product.product_name FROM order_item
   INNER JOIN product ON order_item.product_id = product.product_id 
 WHERE order_item.order_id = $orderId";
$orderItemResult = $connect->query($orderItemSql);
$row = $orderItemResult->fetch_array(); 

for ($i=1; $i < count($row[0]) ; $i++) { 
    $pdf->Cell(10,10, ($i+1) ,1,0,"C");
    $pdf->Cell(70,10, $row[4][$i],1,0,"C");
    $pdf->Cell(30,10, $row[2][$i],1,0,"C");
    $pdf->Cell(40,10, $row[1][$i],1,0,"C");
    $pdf->Cell(40,10, ($row[2][$i] * $row[1][$i]) ,1,1,"C");
  }

  $pdf->Cell(50,10,"",0,1);

  $pdf->Cell(50,10,"Sub Total",0,0);
  $pdf->Cell(50,10,": ".$subTotal,0,1);
  $pdf->Cell(50,10,"Gst Tax",0,0);
  $pdf->Cell(50,10,": ".$vat,0,1);
  $pdf->Cell(50,10,"Discount",0,0);
  $pdf->Cell(50,10,": ".$discount,0,1);
  $pdf->Cell(50,10,"Net Total",0,0);
  $pdf->Cell(50,10,": ".$grandTotal,0,1);
  $pdf->Cell(50,10,"Paid",0,0);
  $pdf->Cell(50,10,": ".$paid,0,1);
  $pdf->Cell(50,10,"Due Amount",0,0);
  $pdf->Cell(50,10,": ".$due,0,1);
  $pdf->Cell(50,10,"Payment Type",0,0);
  $pdf->Cell(50,10,": ".$payment_place,0,1);

  $pdf->Cell(180,10,"Signature",0,0,"R");

$pdf->Output();

}
?>

Output :

output.PNG

So Please Please as early as possible.

Web Development javascript pdf php sql
Member Avatar for nishita_1
nishita_1 15 Newbie Poster
PHP CHART.JS From Sql Database date range limitation Process

please help me. below chart.js works very well but here show all date data from database but i want only last fifteen days data to show. How can i show last 15 days sale reports. i am attach code below. thanks

chart_edit_result.PNG

I an developed an chart.js for my daily sale table from sql . every think works good but chart show all data but i need last 15 days data as well. what can i do now.

<?php
    /* Database connection settings */
    $host = 'localhost';
    $user = 'root';
    $pass = '';
    $db = 'store';
    $mysqli = new mysqli($host,$user,$pass,$db) or die($mysqli->error);

    $data1 = '';
    $data2 = '';

    //query to get data from the table
    $sql = "SELECT order_date, SUM(grand_total) AS total_grand  FROM orders GROUP BY order_date ";

    $result = mysqli_query($mysqli, $sql);

    //loop through the returned data
    while ($row = mysqli_fetch_array($result)) {

        $data1 = $data1 . '"'. $row['order_date'].'",';
        $data2 = $data2 . '"'. $row['total_grand'] .'",';
    }

    $data1 = trim($data1,",");
    $data2 = trim($data2,",");
?>

<!DOCTYPE html>
<html>
    <head>

        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <script type="text/javascript" src="Chart.bundle.min.js"></script>

        <style type="text/css">         
            body{
                font-family: Arial;
                margin: 80px 100px 10px 100px;
                padding: 0;
                color: white;
                text-align: center;
                background: #555652;
            }

            .container {
                color: #E8E9EB;
                background: #222;
                border: #555652 1px solid;
                padding: 10px;
            }
        </style>

    </head>

    <body>     
        <div class="container"> 

            <canvas id="chart" style="width: 100%; height: 65vh; background: #222; border: 1px solid #555652; margin-top: 10px;"></canvas>

            <script>
                var ctx = document.getElementById("chart").getContext('2d');
                var myChart = new Chart(ctx, {
                type: 'line',
                data: {
                    labels: [<?php echo $data1; ?> ],
                    datasets: 
                    [{ …
Web Development javascript javascript-jquery mysql php
Member Avatar for nishita_1
nishita_1 15 Newbie Poster
Re: Mysql query for sum two table data by product wise

DEAR AndrisP, Thanks for your reply. i am already solve this problem using below code. 

SELECT I.product_id, 
       COALESCE(S.sale, 0) AS sale, 
       COALESCE(P.purchase, 0) AS purchase
FROM Product I
LEFT JOIN (
    SELECT product_id, SUM(quantity) AS sale
    FROM order_item 
    GROUP BY product_id
) S ON S.product_id = I.product_id
LEFT JOIN (
    SELECT product_id, SUM(quantity) AS purchase
    FROM pur_item
    GROUP BY product_id
) P ON P.product_id = I.product_id

Dear sir, AndrisP Thanks a lot. you are very helpfully person and your code is perfect. Sir can you tell me, if i want to total sum of sale and purchase date wise. then what code needed. i am already made another table which content date. Thank you

Databases client-server mssql mysql php sql
Member Avatar for nishita_1
nishita_1 15 Newbie Poster
Re: Mysql query for sum two table data by product wise

Dear AndrisP sir if i use below code the sum of sale and purchase product wise perfect but No 5 product missing for no purchase only sale but i want to show all product. what ever purchase or sale are zero.
SELECT * FROM ( SELECT product_id, SUM(quantity) AS sale FROM order_item group by product_id ) P
JOIN ( SELECT product_id, SUM(quantity) AS purchase FROM pur_item group by product_id ) S
JOIN ( SELECT product_id as Pid FROM product GROUP BY Pid ) I ON I.Pid = P.product_id AND S.product_id = P.product_id

real_result.PNG

Dear AndrisP sir if is use your below code then result is like below . i don't know sum of sale and purchase is not perfect. please help me sir its very urgent for my sql query.
select
p.product_id
,sum(s.quantity) sale
,sum(c.quantity) purchase
from product p
left join pur_item c on c.product_id = p.product_id
left join order_item s on s.product_id = p.product_id
where c.quantity is not null or s.quantity is not null
group by p.product_id;

your_result.PNG

please anybody help me i want result for all item sum of product wise data what ever sale or purchase made

Databases client-server mssql mysql php sql
Member Avatar for nishita_1
nishita_1 15 Newbie Poster
Re: Mysql query for sum two table data by product wise

MR Pritaeas provide below code its works but item 4 did't show in output because sale raw is empty. but i want all data show like output table product wise. please help me my project submit earlyer. 

SELECT * 
FROM (
    SELECT product_id, SUM(quantity) AS purchase
    FROM purchase 
    GROUP BY product_id
) P
JOIN (
    SELECT product_id, SUM(quantity) AS sale
    FROM sale
    GROUP BY product_id
) S ON S.product_id = P.product_id
Databases client-server mssql mysql php sql
Member Avatar for nishita_1
nishita_1 15 Newbie Poster
Mysql query for sum two table data by product wise

Dear sir, Please help me about below query. here is two table sale and purchase. i want product wise result in output table like below picture. please help me

22222.jpg

Databases client-server mssql mysql php sql
Member Avatar for nishita_1
nishita_1 15 Newbie Poster
Re: mysql query for sum two table data

Dear sir please help me, here product id 4 is not entry to sale column but i want to show purchase column result as like below picture. sir please help be as before code. thanks

22222.jpg

Databases mssql mysql php sql
Member Avatar for nishita_1
nishita_1 15 Newbie Poster
Re: mysql query for sum two table data

please any one help me

Databases mssql mysql php sql
pritaeas commented: If you need more help, explain what's wrong with my solution. +15
Member Avatar for nishita_1
nishita_1 15 Newbie Poster
mysql query for sum two table data

please provide me sql query for below output sum query please help me

93852217_246197733117734_2443195689677619200_n.jpg

Databases mssql mysql php sql