Jump to content

thara

Members
  • Posts

    566
  • Joined

Everything posted by thara

  1. Wow.... Thats the query I needed.. Many many thanks sir.
  2. Sorry for not giving a clear explanation sir. I meant "partial paid" and "partial due", suppose the store made a sale worth 1000, where the customer pays 700 and keeps the remaining 300 as payable to the store. Accordingly partial paid = 700 partial due = 300
  3. Sir, I think that's how it should be. But the relevant calculations should be changed as follows. Total Cash Sales = (cash(paid) + cash(partial paid) + cash on delivery (paid)) Total Credit Sales = (cash(due) + cash(partial due) + Cash on Delivery (due)) Total Cash on Delivery Sales = (cash on Delivery (paid) + Cash on Delivery (due)) Total card sales = (card(paid)
  4. Sir, I really want to get a sales report for the current date. Through this report, I want to get the following data. Total Cash Sales = (cash(paid) + card (paid) + cash on delivery (paid)) Total Credit Sales = (cash(due) + cash(partial) + Cash on Delivery (due)) Total Cash on Delivery Sales = (Cash on Delivery (paid) + Cash on Delivery (due)) Total card sales Sir, is it possible to get those data in a single query?
  5. Ooops! Yes sir It should be OR not AND... This is my updated query and its result as below. Also I changed GROUP BY to payment_method_id. SELECT DATE(bv.sale_time) , CASE WHEN p.payment_method_id IN (1, 2) THEN sum(amount_due) ELSE 0 END AS total_cash_sales , CASE p.payment_method_id WHEN 4 THEN sum(amount_due) ELSE 0 END AS total_credit_sales FROM basket_amount_due bv JOIN basket bt USING(basket_id) LEFT JOIN basket_payment p USING (basket_id) JOIN payment_method m USING(payment_method_id) WHERE DATE(bv.sale_time) = CURDATE() GROUP BY p.payment_method_id; +--------------------+------------------+--------------------+ | DATE(bv.sale_time) | total_cash_sales | total_credit_sales | +--------------------+------------------+--------------------+ | 2021-09-18 | 3042.10 | 0.00 | | 2021-09-18 | 2070.00 | 0.00 | | 2021-09-18 | 0.00 | 1688.40 | +--------------------+------------------+--------------------+ 3 rows in set (0.011 sec)
  6. Thank you Sir. select * from payment_method; +-------------------+-------------------+ | payment_method_id | method | +-------------------+-------------------+ | 1 | CASH | | 2 | Credit/Debit Card | | 3 | Store Credit | | 4 | Cash on Delivery | | 5 | Gift card | | 6 | Credit | +-------------------+-------------------+ 6 rows in set (0.001 sec) select * from basket_payment; +-------------------+-------------------+-----------+-------------+----------------+---------------------+--------------+ | basket_payment_id | payment_method_id | basket_id | amount_paid | amount_balance | date_paid | payment_note | +-------------------+-------------------+-----------+-------------+----------------+---------------------+--------------+ | 1 | 1 | 1 | 1170.00 | 0.00 | 2021-09-18 12:19:04 | | | 2 | 1 | 2 | 0.00 | 0.00 | 2021-09-18 12:19:39 | | | 3 | 1 | 4 | 1000.00 | 0.00 | 2021-09-18 12:20:22 | | | 4 | 4 | 5 | 990.00 | 0.00 | 2021-09-18 12:21:47 | | | 5 | 4 | 7 | 0.00 | 0.00 | 2021-09-18 12:23:33 | | | 8 | 2 | 10 | 2070.00 | 0.00 | 2021-09-18 12:29:45 | | +-------------------+-------------------+-----------+-------------+----------------+---------------------+--------------+ 6 rows in set (0.001 sec)
  7. I have some sales data as below: SELECT bv.sale_time , amount_due , round(sum(amount_paid), 2) as paid , m.payment_method_id , m.method , bt.payment_status FROM basket_amount_due bv JOIN basket bt USING(basket_id) LEFT JOIN basket_payment p USING (basket_id) JOIN payment_method m USING(payment_method_id) GROUP BY bv.basket_id; +---------------------+------------+---------+-------------------+-------------------+----------------+ | sale_time | amount_due | paid | payment_method_id | method | payment_status | +---------------------+------------+---------+-------------------+-------------------+----------------+ | 2021-09-18 12:19:04 | 1170.00 | 1170.00 | 1 | CASH | paid | | 2021-09-18 12:19:39 | 756.60 | 0.00 | 1 | CASH | due | | 2021-09-18 12:20:22 | 1115.50 | 1000.00 | 1 | CASH | partial | | 2021-09-18 12:21:47 | 990.00 | 990.00 | 4 | Cash on Delivery | paid | | 2021-09-18 12:23:33 | 698.40 | 0.00 | 4 | Cash on Delivery | due | | 2021-09-18 12:29:45 | 2070.00 | 2070.00 | 2 | Credit/Debit Card | paid | +---------------------+------------+---------+-------------------+-------------------+----------------+ 6 rows in set (0.004 sec) My question is, Now I need to get total sales by payment method and payment status. That mean I want, total cash sales (= cash(paid) + Card (paid) + Cash on Delivery (paid)) total credit sales (= cash(due) + cash(partial) + Cash on Delivery (due)) total cash on delivery sales (= Cash on Delivery (paid) + Cash on Delivery (due)) total card sales This is the query I have so far.. SELECT DATE(bv.sale_time) , CASE p.payment_method_id WHEN (1 AND 2) THEN sum(amount_due) ELSE 0 END AS total_cash_sales , CASE p.payment_method_id WHEN 4 THEN sum(amount_due) ELSE 0 END AS total_credit_sales FROM basket_amount_due bv JOIN basket bt USING(basket_id) LEFT JOIN basket_payment p USING (basket_id) JOIN payment_method m USING(payment_method_id) WHERE DATE(bv.sale_time) >= CURDATE() AND DATE(bv.sale_time) < CURDATE() + INTERVAL 1 DAY GROUP BY bv.sale_time; Hope somebody may help me out to figure this out.
  8. You can also try CSS "rem" unit for font-size. According to the W3C spec the definition for one rem unit is: This means that 1rem equals the font size of the html element (which for most browsers has a default value of 16px). Eg: .textNewLine { font-size: .735rem; width: 312px; max-width: 312px; font-family: "Lucida Console", "Courier New", monospace; resize: none; overflow: hidden; white-space: break-spaces; } Same result is giving on firefox as well as chrome.
  9. Sir, I do not see any error in the query pointed out by you. Sir, Is it possible to change that single query by setting the permissions to 1 only for the parent and child modules selected by the user?
  10. Sir, I am having a problem with your above single query solution. As you have mentioned, it become permission column to 1, for selected parents and its all child modules. Look at this Example: Query Result: SELECT m.module_id , name , IFNULL(parent, 0) , CASE WHEN p2.module_id IS NULL THEN CASE WHEN p.module_id IS NULL THEN 0 ELSE 1 END ELSE 1 END as permission FROM module m LEFT JOIN user_permission p ON m.module_id = p.module_id AND p.user_id = 38 LEFT JOIN user_permission p2 ON m.parent = p2.module_id AND p2.user_id = 38 ORDER BY m.module_id ; +-----------+------------------+-------------------+------------+ | module_id | name | IFNULL(parent, 0) | permission | +-----------+------------------+-------------------+------------+ | 1 | User Modules | 0 | 0 | | 2 | Items | 1 | 0 | | 3 | Add New Item | 2 | 0 | | 4 | View Item | 2 | 0 | | 5 | Category | 2 | 0 | | 6 | Brand | 2 | 0 | | 7 | Unit | 2 | 0 | | 8 | Purchase | 1 | 1 | | 9 | Add New Purchase | 8 | 1 | | 10 | view Purchase | 8 | 1 | | 11 | Due Invoice | 8 | 1 | | 12 | Return | 8 | 1 | | 13 | Purchase Log | 8 | 1 | | 14 | Inventory | 1 | 0 | | 15 | Stock Transfer | 14 | 0 | | 16 | Stock Adjustment | 14 | 0 | | 17 | Batch Control | 14 | 0 | | 18 | Stock Take | 14 | 0 | +-----------+------------------+-------------------+------------+ Sir, How can we make permision column to 1 only for selected modules?
  11. Sir, I tried it something like this: My parent and child module arrays changed as below: [pmod] => Array ( [2] => 1 [8] => 1 [14] => 1 ) [cmod] => Array ( [8] => Array ( [9] => 1 [10] => 1 [11] => 1 ) [14] => Array ( [15] => 1 [16] => 1 ) ) This is my PHP: // Insert into "user_permission" table: if(!empty($_POST['pmod']) && !empty($_POST['cmod'])) { $modCheked=[]; foreach ($_POST['cmod'] as $pid => $child) { foreach ($child as $cid => $value) { array_push($modCheked, $pid, $cid); $modules = array_unique($modCheked); } } $sql = "INSERT INTO user_permission (module_id,user_id) VALUES (?,?)"; $stmt = $pdo->prepare($sql); foreach ($modules as $k => $mid) { $module_id = intval($mid); $stmt->execute([$module_id, $lastInsertID]); } } From this code, it is inserted all IDs from `cmod` array into DB.
  12. Sir, I have showing a message to user that saying don't select only parent in my front end. But if they select only parent deliberately or by mistake, I need to ignore it from backend. Thats why I am looking for such a solution.
  13. Thank you sir, One thing, Can we set all permission to 0 including parent, if a user select only parent module. In this case it is not needed parent permission, if user select only parent.
  14. Thank you Sir, I have tried, but my array_chunk was in wrong loop. (I have tried it inside second foreach) I have another question to clarify with you. When I trying to edit user selected checkboxes, I am currently using 2 queries like this: // Get All Modules: $sql ="SELECT module_id, name, IFNULL(parent, 0) FROM module ORDER BY module_id"; $res = $pdo->query($sql); while (list($id, $name, $parent) = $res->fetch(PDO::FETCH_NUM)) { $data[$parent][] = array('id'=>$id, 'name'=>$name); } // Gell User Selected Modules: $sql ="SELECT module_id FROM user_permission WHERE user_id = ? ORDER BY module_id"; $stmt = $pdo->prepare($sql); $stmt->execute([$pageID]); while (list($module_id) = $stmt->fetch(PDO::FETCH_NUM)) { $mid[] = $module_id; } To display selected checkbox in edit form, I am doing it like this inside 1st and 3rd foreach. $checked = (in_array($main['id'], $mid)) ? " checked='checked'" : ''; $checked = (in_array($m['id'], $mid)) ? " checked='checked'" : ''; Sir, may I know, is there a way to do this with single query? Left Join didn't work for me.
  15. Sir, I need single <tr> with 4 <td> for one module. (for parent and child). Chuck of 3 childs need to be get into one td. That is the issue I have.
  16. Yes Sir I tried with array_chunk, but I couldn't get it to work as l expected.
  17. Thank you Sir. Your demostration was really helped me to get it started. This is how I tried it so far: $sql ="SELECT module_id , name , IFNULL(parent, 0) FROM module ORDER BY module_id"; $stmt = $mysqli->prepare($sql); if ($stmt) { $stmt->execute(); $stmt->store_result(); $numrows = $stmt->num_rows; if ($numrows >= 1) { $stmt->bind_result($modId,$name,$parent); while ($stmt->fetch()) { $modList[$parent][] =['id' => $modId, 'name' => $name]; } $stmt->close(); unset($stmt); } } $tbl = '<table class="table table-bordered">'; foreach ($catList[1] as $main) { $tbl .= "<tr><td>{$main['name']}</td><td>"; //echo $main['name'] . '<br>'; foreach ($catList[$main['id']] as $mod) { //$modItem = array_chunk($mod, 3); $tbl .= "{$mod['name']}"; //echo "- {$mod['name']}<br>"; } $tbl .= "</td></tr>"; } $tbl .= "</table>"; echo $tbl; This is ok, But there is a problem. Sir may I know how I get 3 sub modules per each <td> in the table?
  18. Sir, I have checked that your above linked post, before asking this. But I couldn't figure it out.
  19. I do have a sql table named "module" and a parent module may have a sub module. Each parent module can have a maximum of 9 sub modules of three per <td>. This is how this "module" table looks like: +-----------+------------------+----------+------+--------+---------------------+ | module_id | name | page_url | icon | parent | created_date | +-----------+------------------+----------+------+--------+---------------------+ | 1 | User Modules | | NULL | NULL | 2021-07-21 11:46:16 | | 2 | Items | | NULL | 1 | 2021-07-21 11:46:16 | | 3 | Add New Item | | NULL | 2 | 2021-07-21 11:46:16 | | 4 | View Item | | NULL | 2 | 2021-07-21 11:46:16 | | 5 | Category | | NULL | 2 | 2021-07-21 11:46:16 | | 6 | Brand | | NULL | 2 | 2021-07-21 11:46:16 | | 7 | Unit | | NULL | 2 | 2021-07-21 11:46:16 | | 8 | Purchase | | NULL | 1 | 2021-07-21 11:46:16 | | 9 | Add New Purchase | | NULL | 8 | 2021-07-21 11:46:16 | | 10 | view Purchase | | NULL | 8 | 2021-07-21 11:46:16 | | 11 | Due Invoice | | NULL | 8 | 2021-07-21 11:46:16 | | 12 | Return | | NULL | 8 | 2021-07-21 11:46:16 | | 13 | Purchase Log | | NULL | 8 | 2021-07-21 11:46:16 | | 14 | Inventory | | NULL | 1 | 2021-07-21 11:46:16 | | 15 | Stock Transfer | | NULL | 14 | 2021-07-21 11:46:16 | | 16 | Stock Adjustment | | NULL | 14 | 2021-07-21 11:46:16 | | 17 | Batch Control | | NULL | 14 | 2021-07-21 11:46:16 | | 18 | Stock Take | | NULL | 14 | 2021-07-21 11:46:16 | +-----------+------------------+----------+------+--------+---------------------+ My desired HTML table layout looks somthing similar to this attach image. I would like to know how we can do it in php, if is possible. Thank you.
  20. Actually sir, category is not null, only brand would be NULL. then SKU should be : BI-IK0013
  21. I need to create the unique SKU (Stock Keeping Unit) name for each products mixed with product.id,product.name,product.category and product.brand. There is no problem to do it, i.e.: SKU for a product with id=13, name=Bio Clean green and category=INSECTS KILLERS brand=HARPIC becomes : BI-IKHA0013 This is how I tried: $inm = "Bio Clean green500ml"; $cnm = "INSECTS KILLERS"; $bnm = "HARPIC "; echo SKU_gen($inm, $cnm,$bnm,20).'<br>'; function SKU_gen($pname, $cat=null, $brand=null, $id = null, $l = 2){ $results = ''; // empty string $str1 = array_shift(explode(' ',$pname)); $str1 = strtoupper(substr($str1, 0, $l)); $str2 = array_shift(explode(' ',$cat)); $str2 = strtoupper(substr($str2, 0, $l)); $str3 = array_shift(explode(' ',$brand)); $str3 = strtoupper(substr($str3, 0, $l)); $id = str_pad($id , 4, 0, STR_PAD_LEFT); $results .= "{$str1}-{$str2}{$str3}{$id}"; return $results; } But this function is not working as expected when category of brand values become NULL.
  22. Sir, Thanks for response I was thinking about your first option and setting procut category to a new common one. Sir is there a way to do this by modifing above query ? Thank you.
  23. Sir, lets assume, I will delete category 16 from the category table, according to my table it is a parent category. If there are two products belonging to that category in the product table, then what happens to the two category ids in that product, the category corresponding to that category id is not in the category table because it has already been deleted from the category table. Isn't that right?
  24. Yes sir its working... Thank you. One thing, I really want to see if the products related to this deleted category_id are in the product table and if so, set the category_id of those products to a general category. In this example, it is category number 1. Sir, Is there any possible way to modify this single query to reset the category_id in the product table also? Thanks again.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.