public function updateReturnItem($post)
{
$db = $this->getAdapter();
$db_global = new Application_Model_DbTable_DbGlobal();
$session_user = new Zend_Session_Namespace('auth');
$userName = $session_user->user_name;
$GetUserId = $session_user->user_id;
$idrecord = $post['v_name'];
$datainfo = array("contact_name" => $post['contact'], "phone" => $post['txt_phone'], "add_name" => $post["vendor_address"]);
//updage vendor info
$db_global->updateRecord($datainfo, $idrecord, "vendor_id", "tb_vendor");
unset($datainfo);
$return_id = $post["id"];
$data_update = array("vendor_id" => $post["v_name"], "return_no" => $post['retun_order'], "date_return" => $post["return_date"], "payment_method" => $post["payment_name"], "currency_id" => $post["currency"], "remark" => $post["return_remark"], "user_mod" => $GetUserId, "timestamp" => new Zend_Date(), "paid" => $post["paid"], "all_total" => $post["all_total"], "balance" => $post["all_total"] - $post["paid"]);
$db_global->addRecord($data_update, "tb_return");
$db_global->updateRecord($data_update, $return_id, "return_id", "tb_return");
unset($data_update);
/////
$sql_item = "SELECT iv.ProdId, iv.QuantityOnHand,iv.QuantityAvailable,rv.location_id,rv.qty_return FROM tb_return_vendor_item AS rv\n \tINNER JOIN tb_inventorytotal AS iv ON iv.ProdId = rv.pro_id WHERE rv.return_id = {$return_id}";
$rows_return = $db_global->getGlobalDb($sql_item);
if ($rows_return) {
foreach ($rows_return as $row_return) {
$qty_on_order = array("QuantityOnHand" => $row_return["QuantityOnHand"] + $row_return["qty_return"], "QuantityAvailable" => $row_return["QuantityAvailable"] + $row_return["qty_return"], "Timestamp" => new zend_date());
//update total stock
$db_global->updateRecord($qty_on_order, $row_return["ProdId"], "ProdId", "tb_inventorytotal");
unset($qty_on_order);
$rowitem_exist = $db_global->porductLocationExist($row_return["ProdId"], $row_return["location_id"]);
if ($rowitem_exist) {
$updatedata = array('qty' => $rowitem_exist["qty"] + $row_return["qty_return"], "last_usermod" => $GetUserId, "last_mod_date" => new Zend_Date());
//update stock product location
$db_global->updateRecord($updatedata, $rowitem_exist["ProLocationID"], "ProLocationID", "tb_prolocation");
unset($updatedata);
}
}
}
$sql = "DELETE FROM tb_return_vendor_item WHERE return_id IN ({$return_id})";
$db_global->deleteRecords($sql);
$ids = explode(',', $post['identity']);
//add order in tb_inventory must update code again 9/8/13
foreach ($ids as $i) {
$add_data = array("return_id" => $return_id, "pro_id" => $post["item_id_" . $i], "location_id" => $post["LocationId_" . $i], "qty_return" => $post["qty_return_" . $i], "price" => $post["price_" . $i], "sub_total" => $post["sub_total_" . $i], "return_remark" => $post["remark_" . $i]);
$db->insert("tb_return_vendor_item", $add_data);
$rows = $db_global->inventoryLocation($post["LocationId_" . $i], $post["item_id_" . $i]);
if ($rows) {
$updatedata = array('qty' => $rows["qty"] - $post["qty_return_" . $i], "last_usermod" => $GetUserId, "last_mod_date" => new Zend_Date());
//update stock product location
$db_global->updateRecord($updatedata, $rows["ProLocationID"], "ProLocationID", "tb_prolocation");
unset($updatedata);
$qty_on_return = array("QuantityOnHand" => $rows["QuantityOnHand"] - $post["qty_return_" . $i], "QuantityAvailable" => $rows["QuantityAvailable"] - $post["qty_return_" . $i], "Timestamp" => new zend_date());
//update total stock
$db_global->updateRecord($qty_on_return, $post["item_id_" . $i], "ProdId", "tb_inventorytotal");
unset($qty_on_return);
//add return history
$data_history = array('transaction_type' => 4, 'pro_id' => $post["item_id_" . $i], 'date' => new Zend_Date(), 'location_id' => $post["LocationId_" . $i], 'Remark' => $post['remark_' . $i], 'qty_edit' => $post["qty_return_" . $i], 'qty_before' => $rows["qty"], 'qty_after' => $rows["qty"] - $post["qty_return_" . $i], 'user_mod' => $GetUserId);
$db->insert("tb_move_history", $data_history);
unset($data_history);
} else {
$insertdata = array('pro_id' => $post["item_id_" . $i], 'LocationId' => $post["LocationId_" . $i], 'qty' => -$post["qty_return_" . $i]);
//update stock product location
$db->insert("tb_prolocation", $insertdata);
unset($insertdata);
//add return history
$data_history = array('transaction_type' => 4, 'pro_id' => $post["item_id_" . $i], 'date' => new Zend_Date(), 'location_id' => $post["LocationId_" . $i], 'Remark' => $post['remark_' . $i], 'qty_edit' => $post["qty_return_" . $i], 'qty_before' => 0, 'qty_after' => -$post["qty_return_" . $i], 'user_mod' => $GetUserId);
$db->insert("tb_move_history", $data_history);
unset($data_history);
$rows_stock = $db_global->InventoryExist($post["item_id_" . $i]);
if ($rows_stock) {
$dataInventory = array('QuantityOnHand' => $rows_stock["QuantityOnHand"] - $post["qty_return_" . $i], 'QuantityAvailable' => $rows_stock["QuantityAvailable"] - $post["qty_return_" . $i], 'Timestamp' => new Zend_date());
$db_global->updateRecord($dataInventory, $rows_stock["ProdId"], "ProdId", "tb_inventorytotal");
unset($dataInventory);
} else {
$addInventory = array('ProdId' => $post["item_id_" . $i], 'QuantityOnHand' => -$post["qty_return_" . $i], 'QuantityAvailable' => -$post["qty_return_" . $i], 'Timestamp' => new Zend_date());
$db->insert("tb_inventorytotal", $addInventory);
unset($addInventory);
}
}
}
}
public function updateBrandAction()
{
$db = new Application_Model_DbTable_DbGlobal();
if ($this->getRequest()->isPost()) {
$data = $this->getRequest()->getPost();
$_arr = array("parent_id" => $data['Parentbrand'], "Name" => $data['brandName'], "IsActive" => $data['status'], "Timestamp" => new Zend_Date());
$db->updateRecord($_arr, $data['id'], "branch_id", "tb_branch");
}
$this->_redirect("product/Index/brand");
}
public function quoteUpdate($data)
{
$db = $this->getAdapter();
$db_global = new Application_Model_DbTable_DbGlobal();
$session_user = new Zend_Session_Namespace('auth');
$userName = $session_user->user_name;
$GetUserId = $session_user->user_id;
$idrecord = $data['customer_id'];
$datainfo = array("contact_name" => $data['contact'], "phone" => $data['txt_phone'], "add_name" => $data['remark_add']);
$itemid = $db_global->updateRecord($datainfo, $idrecord, "customer_id", "tb_customer");
$id = $data["id"];
$sql_del = "DELETE FROM tb_sales_order_item WHERE order_id IN ({$id})";
$db_global->deleteRecords($sql_del);
$sql_sale = "DELETE FROM tb_sales_order WHERE order_id IN ({$id})";
$sql_del_history = "DELETE FROM tb_order_history WHERE type = 2 AND order_id IN ({$id})";
$info_order = array("customer_id" => $data['customer_id'], "LocationId" => $data['LocationId'], "sales_ref" => $data['sales_ref'], "date_order" => $data['order_date'], "payment_method" => $data['payment_name'], "currency_id" => $data['currency'], "remark" => $data['remark'], "user_mod" => $GetUserId, "timestamp" => new Zend_Date(), "net_total" => $data['net_total'], "discount_type" => $data['discount_type'], "discount_value" => $data['discount_value'], "discount_real" => $data["discount_real"], "paid" => $data['paid'], "all_total" => $data['all_total'], "balance" => $data['all_total'] - $data['paid']);
//and info of order
$id_order = $db_global->addRecord($info_order, "tb_sales_order");
unset($info_order);
$ids = explode(',', $data['identity']);
foreach ($ids as $i) {
$data_item[$i] = array('order_id' => $id_order, 'pro_id' => $data['item_id_' . $i], 'qty_order' => $data['qty' . $i], 'price' => $data['price' . $i], 'total_befor' => $data['total' . $i], 'disc_type' => $data['dis-type-' . $i], 'disc_value' => $data['dis-value' . $i], 'sub_total' => $data['after_discount' . $i]);
$db->insert("tb_sales_order_item", $data_item[$i]);
unset($data_item[$i]);
//add history order
$data_history[$i] = array('pro_id' => $data['item_id_' . $i], 'type' => 2, 'order' => $id_order, 'customer_id' => $data['customer_id'], 'date' => new Zend_Date(), 'status' => 1, 'order_total' => $data['all_total'], 'qty' => $data['qty' . $i], 'unit_price' => $data['price' . $i], 'sub_total' => $data['after_discount' . $i]);
$order_history = $db->insert("tb_order_history", $data_history[$i]);
unset($data_history[$i]);
//update stock total inventory (QTY Reserved)
}
}
public function cancelCustomerOrder($data)
{
try {
$db_global = new Application_Model_DbTable_DbGlobal();
$db = $this->getAdapter();
$db->beginTransaction();
$session_user = new Zend_Session_Namespace('auth');
$GetUserId = $session_user->user_id;
$id_order_update = $data['id'];
$sql_item = "SELECT iv.ProdId, iv.QuantityOnHand, iv.QuantityAvailable, sum(so.qty_order) AS qtysold,\n\t\t\t\t so.price, so.total_befor, so.sub_total, s.LocationId\n\t\t\t\tFROM tb_sales_order AS s,tb_sales_order_item AS so\n\t\t\t\t, tb_inventorytotal AS iv WHERE iv.ProdId = so.pro_id AND so.order_id=s.order_id AND so.order_id ={$id_order_update} GROUP BY so.pro_id";
$rows_sold = $db_global->getGlobalDb($sql_item);
if ($rows_sold) {
foreach ($rows_sold as $row_sold) {
//just add to stock inventory tmp then withdrawal
$qty_on_order = array("QuantityOnHand" => $row_sold["QuantityOnHand"] + $row_sold["qtysold"], "QuantityAvailable" => $row_sold["QuantityAvailable"] + $row_sold["qtysold"], "Timestamp" => new Zend_Date());
//update total stock
$db_global->updateRecord($qty_on_order, $row_sold["ProdId"], "ProdId", "tb_inventorytotal");
$row_get = $db_global->porductLocationExist($row_sold["ProdId"], $row_sold["LocationId"]);
if ($row_get) {
$qty_on_location = array("qty" => $row_get["qty"] + $row_sold["qtysold"], "last_usermod" => $GetUserId, "last_mod_date" => new Zend_Date());
//update total stock
$db_global->updateRecord($qty_on_location, $row_get["ProLocationID"], "ProLocationID", "tb_prolocation");
}
///note history
$data_history = array('pro_id' => $row_sold["ProdId"], 'type' => 2, 'order' => $id_order_update, 'customer_id' => $data['customer_id'], 'date' => new Zend_Date(), 'status' => 6, 'order_total' => $row_sold["qtysold"], 'qty' => $row_sold['qtysold'], 'unit_price' => $row_sold['price'], 'sub_total' => $row_sold['sub_total']);
$db->insert("tb_order_history", $data_history);
}
}
// $sql_sales= "DELETE FROM tb_sales_order WHERE order_id IN ($id_order_update)";
// $db_global->deleteRecords($sql_sales);
$update = array("status" => 6);
$db_global->updateRecord($update, $id_order_update, "order_id", "tb_sales_order");
// $sql= "DELETE FROM tb_sales_order_item WHERE order_id IN ($id_order_update)";
// $db_global->deleteRecords($sql);
$db->commit();
} catch (Exception $e) {
$db->rollBack();
}
}
public function updateReturnItemIn($post)
{
try {
$db = $this->getAdapter();
$db->beginTransaction();
$db_global = new Application_Model_DbTable_DbGlobal();
$session_user = new Zend_Session_Namespace('auth');
$userName = $session_user->user_name;
$GetUserId = $session_user->user_id;
$idrecord = $post['v_name'];
// $datainfo=array(
// "contact_name" => $post['contact'],
// "phone" => $post['txt_phone'],
// "add_name" => $post["vendor_address"]
// );
// //updage vendor info
// $db_global->updateRecord($datainfo,$idrecord,"vendor_id","tb_vendor");
// unset($datainfo);
$return_id = $post["id"];
$old_location = $post["old_location"];
$sql_item = "SELECT\n\t \t(SELECT p.pro_id FROM tb_product AS p WHERE p.pro_id = rvi.`pro_id` LIMIT 1) AS pro_id\n\t \t\t\n\t \t,(SELECT p.qty_onorder FROM tb_product AS p WHERE p.pro_id = rvi.`pro_id` LIMIT 1) AS qty_onorder\n\t \n\t \t,(SELECT p.qty_onhand \tFROM tb_product AS p WHERE p.pro_id = rvi.`pro_id` LIMIT 1) AS qty_onhand\n\t \n\t \t,(SELECT p.qty_available \tFROM tb_product AS p WHERE p.pro_id = rvi.`pro_id` LIMIT 1) AS qty_available\n\t \t\t\n\t \t, SUM(rvi.`qty_return`) AS qty_return FROM\n \n \ttb_return_vendor_item_in AS rvi WHERE rvi.return_id = {$return_id} GROUP BY rvi.pro_id";
$rows_return = $db_global->getGlobalDb($sql_item);
// print_r($rows_return);
if ($rows_return) {
foreach ($rows_return as $row_return) {
$qty_stock = $row_return["qty_onhand"] - $row_return["qty_return"];
$rows = $db_global->inventoryLocation($old_location, $row_return["pro_id"]);
$qty_location = $rows["qty"] - $row_return["qty_return"];
//print_r($rows);exit();
if ($qty_stock < 0) {
Application_Form_FrmMessage::message("Your product stock is less than return");
Application_Form_FrmMessage::redirectUrl("/purchase/return/return-in");
//exit();
} elseif ($qty_location < 0) {
Application_Form_FrmMessage::message("You Items is less than item return");
Application_Form_FrmMessage::redirectUrl("/purchase/return/return-in");
//exit();
} else {
$qty_on_order = array("qty_onhand" => $row_return["qty_onhand"] - $row_return["qty_return"], "qty_available" => $row_return["qty_available"] - $row_return["qty_return"], "last_mod_date" => new Zend_date());
//update total stock
$db_global->updateRecord($qty_on_order, $row_return["pro_id"], "pro_id", "tb_product");
unset($qty_on_order);
$rowitem_exist = $db_global->porductLocationExist($row_return["pro_id"], $old_location);
if ($rowitem_exist) {
$updatedata = array('qty' => $rowitem_exist["qty"] - $row_return["qty_return"], "last_usermod" => $GetUserId, "last_mod_date" => new Zend_Date());
//update stock product location
$db_global->updateRecord($updatedata, $rowitem_exist["ProLocationID"], "ProLocationID", "tb_prolocation");
unset($updatedata);
}
}
}
}
$data_update = array("vendor_id" => $post["v_name"], "date_return_in" => $post["return_date"], "remark" => $post["return_remark"], "user_mod" => $GetUserId, "timestamp" => new Zend_Date(), "location_id" => $post["LocationId"], "all_total" => $post["all_total"]);
$db_global->updateRecord($data_update, $return_id, "returnin_id", "tb_return_vendor_in");
unset($data_update);
$sql = "DELETE FROM tb_return_vendor_item_in WHERE return_id IN ({$return_id})";
$db_global->deleteRecords($sql);
$delete_history = "DELETE FROM tb_return_history WHERE return_id IN({$return_id})";
$db_global->deleteRecords($delete_history);
$ids = explode(',', $post['identity']);
//add order in tb_inventory must update code again 9/8/13
foreach ($ids as $i) {
$add_data = array("return_id" => $return_id, "pro_id" => $post["item_id_" . $i], "qty_return" => $post["qty_return_" . $i], "price" => $post["price_" . $i], "sub_total" => $post["sub_total_" . $i], "return_remark" => $post["remark_" . $i]);
$db->insert("tb_return_vendor_item", $add_data);
$add_data = array("return_id" => $return_id, "pro_id" => $post["item_id_" . $i], "qty_return" => $post["qty_return_" . $i], "price" => $post["price_" . $i], "sub_total" => $post["sub_total_" . $i], "return_remark" => $post["remark_" . $i]);
$db->insert("tb_return_vendor_item_in", $add_data);
$rows = $db_global->inventoryLocation($post["LocationId"], $post["item_id_" . $i]);
if ($rows) {
$qty_on_return = array("qty_onhand" => $rows["qty_onhand"] + $post["qty_return_" . $i], "qty_available" => $rows["qty_available"] + $post["qty_return_" . $i], "last_mod_date" => new Zend_date());
//update total stock
$db_global->updateRecord($qty_on_return, $post["item_id_" . $i], "pro_id", "tb_product");
unset($qty_on_return);
$updatedata = array('qty' => $rows["qty"] + $post["qty_return_" . $i], "last_usermod" => $GetUserId, "last_mod_date" => new Zend_Date());
//update stock product location
$db_global->updateRecord($updatedata, $rows["ProLocationID"], "ProLocationID", "tb_prolocation");
unset($updatedata);
unset($qty_on_return);
//add return history
$data_history = array('transaction_type' => 4, 'pro_id' => $post["item_id_" . $i], 'date' => new Zend_Date(), 'location_id' => $post["LocationId_" . $i], 'Remark' => $post['remark_' . $i], 'qty_edit' => $post["qty_return_" . $i], 'qty_before' => $rows["qty"], 'qty_after' => $rows["qty"] - $post["qty_return_" . $i], 'user_mod' => $GetUserId);
$db->insert("tb_move_history", $data_history);
unset($data_history);
$history = array("return_id" => $return_id, "return_no" => $post["retun_order"], "pro_id" => $post["item_id_" . $i], "location_id" => $post["LocationId"], "return_type" => 2, "vendor_id" => 1, "return_date" => $post["return_date"], "qty_return" => $post["qty_return_" . $i], "price" => $post["price_" . $i], "total_amount" => $post["sub_total_" . $i], "user_mod" => $GetUserId, "remark" => $post["return_remark"]);
$db->insert("tb_return_history", $history);
unset($history);
} else {
// Application_Form_FrmMessage::message("Your product in stock is not exist");
// Application_Form_FrmMessage::redirectUrl("/purchase/return");
// exit();
$row_location = $db_global->productLocation($post["LocationId"], $post["item_id_" . $i]);
if ($row_location) {
$updatedata = array('qty' => $rows["qty"] + $post["qty_return_" . $i], "last_usermod" => $GetUserId, "last_mod_date" => new Zend_Date());
//update stock product location
$db_global->updateRecord($updatedata, $row_location["ProLocationID"], "ProLocationID", "tb_prolocation");
unset($updatedata);
} else {
$insertdata = array('pro_id' => $post["item_id_" . $i], 'LocationId' => $post["LocationId_" . $i], 'qty' => -$post["qty_return_" . $i]);
}
//update stock product location
$db->insert("tb_prolocation", $insertdata);
unset($insertdata);
//add return history
$data_history = array('transaction_type' => 4, 'pro_id' => $post["item_id_" . $i], 'date' => new Zend_Date(), 'location_id' => $post["LocationId_" . $i], 'Remark' => $post['remark_' . $i], 'qty_edit' => $post["qty_return_" . $i], 'qty_before' => 0, 'qty_after' => -$post["qty_return_" . $i], 'user_mod' => $GetUserId);
$db->insert("tb_move_history", $data_history);
unset($data_history);
$history = array("return_id" => $return_id, "return_no" => $post["retun_order"], "pro_id" => $post["item_id_" . $i], "location_id" => $post["LocationId"], "return_type" => 2, "vendor_id" => 1, "return_date" => $post["return_date"], "qty_return" => $post["qty_return_" . $i], "price" => $post["price_" . $i], "total_amount" => $post["sub_total_" . $i], "user_mod" => $GetUserId, "remark" => $post["return_remark"]);
$db->insert("tb_return_history", $history);
unset($history);
$rows_stock = $db_global->InventoryExist($post["item_id_" . $i]);
if ($rows_stock) {
$dataInventory = array('qty_onhand' => $rows_stock["qty_onhand"] - $post["qty_return_" . $i], 'qty_available' => $rows_stock["qty_available"] - $post["qty_return_" . $i], 'last_mod_date' => new Zend_date());
$db_global->updateRecord($dataInventory, $rows_stock["pro_id"], "pro_id", "tb_product");
unset($dataInventory);
} else {
$addInventory = array('pro_id' => $post["item_id_" . $i], 'qty_onhand' => -$post["qty_return_" . $i], 'qty_available' => -$post["qty_return_" . $i], 'last_mod_date' => new Zend_date());
$db->insert("tb_product", $addInventory);
unset($addInventory);
}
}
}
$db->commit();
} catch (Exception $e) {
$db->rollBack();
echo $e->getMessage();
}
}
public function calCulatePayment($post)
{
$db_global = new Application_Model_DbTable_DbGlobal();
$session_user = new Zend_Session_Namespace('auth');
$userName = $session_user->user_name;
$GetUserId = $session_user->user_id;
$data_update = array("paid" => $post['amount_paid'], "all_total" => $post['all_total'], "balance" => $post['all_total'] - $post['amount_paid'], "user_mod" => $GetUserId, "timestamp" => new Zend_Date());
$succ = $db_global->updateRecord($data_update, $post['purchase_id'], "order_id", "tb_purchase_order");
return $succ;
}
public function updateProduct($post)
{
try {
//use try on 21-11-13
$db = $this->getAdapter();
$db->beginTransaction();
$db_global = new Application_Model_DbTable_DbGlobal();
$session_user = new Zend_Session_Namespace('auth');
$userName = $session_user->user_name;
$GetUserId = $session_user->user_id;
$GetUserLever = $session_user->level;
$user_location = $session_user->location_id;
$photoname = str_replace(" ", "_", $post['txt_name']) . '.jpg';
$upload = new Zend_File_Transfer();
$upload->addFilter('Rename', array('target' => PUBLIC_PATH . '/images/fi-upload/' . $photoname, 'overwrite' => true), 'photo');
$receive = $upload->receive();
if ($receive) {
$post['photo'] = $photoname;
}
unset($post['MAX_FILE_SIZE']);
$data = array('item_name' => $post['txt_name'], 'item_code' => $post['txt_code'], 'item_code' => $post['product_size'], 'photo' => $post['photo'], 'brand_id' => $post['branch_id'], 'cate_id' => $post['category'], 'stock_type' => $post['stock_type'], 'measure_id' => $post['measure_unit'], 'qty_perunit' => $post['qty_perunit'], 'label' => $post['label_perunit'], 'is_avaliable' => $post['status'], 'unit_sale_price' => $post['unit_sale_price'], 'price_per_qty' => $post['unit_sale_price'] / $post['qty_perunit'], 'remark' => $post['remark'], 'last_usermod' => $GetUserId, 'last_mod_date' => new Zend_Date());
$id = $post['id'];
$itemid = $db_global->updateRecord($data, $id, "pro_id", "tb_product");
//for update product by user level
if ($GetUserLever == 1 or $GetUserLever == 2) {
$_model = new Application_Model_DbTable_DbGlobal();
$identitys = explode(',', $post['identity']);
$qtyonhand = 0;
foreach ($identitys as $i) {
$qtyonhand = $qtyonhand + $post['qty_' . $i];
if (empty($post['item_price' . $i])) {
$post['item_price' . $i] = 0;
}
$_rs = $_model->QtyProLocation($id, $post['location_id_' . $i]);
if ($_rs !== "") {
if ($_rs['qty'] !== $post['qty_' . $i]) {
$_arr = array('qty' => $post['qty_' . $i], 'qty_warn' => $post['qty_warnning' . $i], 'unit_sale_price' => $post['item_price' . $i], 'price_per_qty' => $post['item_price' . $i] / $post['qty_perunit'], 'last_usermod' => $this->getUserId(), 'last_mod_date' => new Zend_Date());
$db_global->updateRecord($_arr, $_rs['ProLocationID'], "ProLocationID", "tb_prolocation");
//code here
$_arr_history = array('transaction_type' => 1, 'pro_id' => $id, 'date' => new Zend_Date(), 'location_id' => $post['location_id_' . $i], 'qty_edit' => $_rs['qty'] . " -> " . $post['qty_' . $i], 'qty_before' => $_rs['qty'], 'qty_after' => $post['qty_' . $i], 'user_mod' => $this->getUserId());
$db->insert("tb_move_history", $_arr_history);
}
} else {
$dataproduct = array('pro_id' => $id, 'LocationId' => $post['location_id_' . $i], 'qty' => $post['qty' . $i], 'qty_warn' => $post['qty_warnning' . $i], 'unit_sale_price' => $post['item_price' . $i], 'price_per_qty' => $post['item_price' . $i] / $post['qty_perunit'], 'last_usermod' => $this->getUserId(), 'last_mod_date' => new Zend_Date());
//add qty to product location
$db->insert("tb_prolocation", $dataproduct);
//add history
$data_history = array('transaction_type' => 1, 'pro_id' => $id, 'date' => new Zend_Date(), 'location_id' => $post['location_id_' . $i], 'qty_before' => 0, 'qty_edit' => "0 -> " . $post['qty' . $i], 'qty_after' => $post['qty' . $i], 'user_mod' => $this->getUserId());
$db->insert("tb_move_history", $data_history);
unset($dataproduct);
unset($data_history);
}
}
$_rs = $db_global->getQtyFromProductById($id);
if (!empty($_rs)) {
$_qty_deffer = $qtyonhand - $_rs['qty_onhand'];
$_qtydata = array('qty_onhand' => $qtyonhand, 'qty_available' => $_rs['qty_available'] + $_qty_deffer);
$where = $db->quoteInto("pro_id=?", $id);
$this->update($_qtydata, $where);
}
} else {
$identitys = explode(',', $post['identity']);
$qty_onhand = 0;
foreach ($identitys as $i) {
$qty = $post['qty_' . $i];
//not yet
if (empty($post['item_price' . $i])) {
$post['item_price' . $i] = 0;
}
$rows_exist = $db_global->porductLocationExist($id, $post["location_id_" . $i]);
$_arr = array('qty' => $post['qty_' . $i], 'qty_warn' => $post['qty_warnning' . $i], 'unit_sale_price' => $post['item_price' . $i], 'price_per_qty' => $post['item_price' . $i] / $post['qty_perunit'], 'last_usermod' => $this->getUserId(), 'last_mod_date' => new Zend_Date());
}
if ($rows_exist !== "") {
$db_global->updateRecord($_arr, $rows_exist["ProLocationID"], "ProLocationID", "tb_prolocation");
unset($arr);
if ($rows_exist["qty"] !== $qty) {
$data_history = array('transaction_type' => 1, 'pro_id' => $id, 'date' => new Zend_Date(), 'location_id' => $user_location, 'Remark' => $post['remark'], 'qty_edit' => $rows_exist["qty"] . " -> " . $qty, 'qty_before' => $rows_exist["qty"], 'qty_after' => $qty, 'user_mod' => $GetUserId);
$db->insert("tb_move_history", $data_history);
unset($data_history);
$_qty_deffer = $qty - $rows_exist["qty"];
$_rs = $db_global->getQtyFromProductById($id);
if (!empty($_rs)) {
$_qtydata = array('qty_onhand' => $_rs['qty_onhand'] + $_qty_deffer, 'qty_available' => $_rs['qty_available'] + $_qty_deffer);
$where = $db->quoteInto("pro_id=?", $id);
$this->update($_qtydata, $where);
}
}
}
}
$db->commit();
return true;
} catch (Exception $e) {
$db->rollBack();
Application_Form_FrmMessage::messageError("UPDATE_FAIL", $e->getMessage());
}
}
public function updatePurchaseOrder($data)
{
try {
$db = $this->getAdapter();
$db->beginTransaction();
$db_global = new Application_Model_DbTable_DbGlobal();
$session_user = new Zend_Session_Namespace('auth');
$userName = $session_user->user_name;
$GetUserId = $session_user->user_id;
//for update order by id\
$id_order_update = $data['id'];
print_r($id_order_update);
//$recieved_id = $data["recieve_id"];
//update info of order in tb_purchase order
// Select all qty in tb_product and tb_purchase_order_item for compare product exist or not for update qty to old qty
$db->getProfiler()->setEnabled(true);
$info_purchase_order = array("vendor_id" => $data['v_name'], "LocationId" => $data["LocationId"], "order" => $data['txt_order'], "date_order" => $data['order_date'], "status" => $data["status"], "remark" => $data['remark'], "user_mod" => $GetUserId, "timestamp" => new Zend_Date(), "paid" => $data['paid'], "all_total" => $data['totalAmoun'], "payment_method" => $data["payment_name"], "currency_id" => $data["currency"], "balance" => $data['remain']);
$db_global->updateRecord($info_purchase_order, $id_order_update, "order_id", "tb_purchase_order");
Zend_Debug::dump($db->getProfiler()->getLastQueryProfile()->getQuery());
Zend_Debug::dump($db->getProfiler()->getLastQueryProfile()->getQueryParams());
$db->getProfiler()->setEnabled(false);
// end update info of order in tb_purchase order
// Delete old purchase order item before insert new purchase order item in old order_id
$sql = "DELETE FROM tb_purchase_order_item WHERE order_id IN ({$id_order_update})";
$db_global->deleteRecords($sql);
unset($sql);
$sql_history = "DELETE FROM tb_purchase_order_history WHERE `order` IN ({$id_order_update})";
$db_global->deleteRecords($sql_history);
unset($sql_history);
/// update
$ids = explode(',', $data['identity']);
foreach ($ids as $i) {
// Insert New purchase order item in old order_id
$data_item[$i] = array('order_id' => $id_order_update, 'pro_id' => $data['item_id_' . $i], 'qty_order' => $data['qty' . $i], 'price' => $data['price' . $i], 'sub_total' => $data['total' . $i], 'total_befor' => $data['total' . $i], 'remark' => $data['remark_' . $i]);
//print_r($data_item); echo "<br />";echo "<br />";
$db->insert("tb_purchase_order_item", $data_item[$i]);
unset($data_item[$i]);
$data_history[$i] = array('order' => $id_order_update, 'pro_id' => $data['item_id_' . $i], 'type' => 1, 'customer_id' => $data['v_name'], 'status' => $data["status"], 'order_total' => $data['total' . $i], 'qty' => $data['qty' . $i], 'unit_price' => $data['price' . $i], 'sub_total' => $data['total' . $i], 'date' => $data["old_history_date"], 'last_update_date' => new Zend_Date());
//print_r($data_history);exit();
$db->insert("tb_purchase_order_history", $data_history[$i]);
unset($data_history[$i]);
}
$db->commit();
} catch (Exception $e) {
$db->rollBack();
$e->getMessage();
}
}
public function getPurchaseHistory($order_id, $item_name)
{
$db = $this->getAdapter();
$sql = " SELECT history_id FROM tb_purchase_order_history \n\t\t\t\t\tWHERE `order` = {$order_id} AND pro_id ={$item_name} ";
$rows = $db->fetchAll($sql);
$db_global = new Application_Model_DbTable_DbGlobal();
$update = array("status" => 6);
if (!empty($rows)) {
foreach ($rows as $row) {
$db_global->updateRecord($update, $row["history_id"], "history_id", "tb_purchase_order_history");
}
}
}
public function receivedOrderCompleteAction()
{
$data = $this->getRequest()->getPost();
$db = new Application_Model_DbTable_DbGlobal();
//if($post){
$data_staust = array('status' => 5);
$getorder = new purchase_Model_DbTable_DbPurchaseAdvance();
$rows = $getorder->getStatusOrder($data['purchase_order']);
//for update status in purchase order history
if ($rows) {
foreach ($rows as $order) {
$db->updateRecord($data_staust, $order["history_id"], "history_id", "tb_order_history");
//must update status too
}
//$result=$getorder->getPurchaseOrderExist($post);
}
//get fully received
$fully_receive = new purchase_Model_DbTable_DbAdvance();
$get_result = $fully_receive->receivedCompleted($data);
$suc = array("test" => 1);
echo Zend_Json::encode($suc);
exit;
//}
}
public function updateTransferStockTransaction($post)
{
$db = $this->getAdapter();
$session_user = new Zend_Session_Namespace('auth');
$userName = $session_user->user_name;
$GetUserId = $session_user->user_id;
$db_global = new Application_Model_DbTable_DbGlobal();
if ($post['from_location'] !== $post['to_location']) {
$id_update = $post["transfer_id"];
$data_transfer = array('transfer_date' => $post['transfer_date'], 'from_location' => $post['from_location'], 'to_location' => $post['to_location'], 'user_id' => $GetUserId, 'mod_date' => new Zend_Date(), 'remark' => $post['remark_transfer']);
//$transfer_id = $db_global->addRecord($data_transfer, "tb_stocktransfer");
$db_global->updateRecord($data_transfer, $id_update, "transfer_id", "tb_stocktransfer");
unset($data_transfer);
$sql_item = "SELECT st.transfer_id, ti.pro_id, SUM( ti.qty ) AS qty\n\t\t\t\t\t\t\tFROM tb_stocktransfer AS st, tb_transfer_item AS ti, tb_product AS p\n\t\t\t\t\t\t\tWHERE ti.transfer_id = st.transfer_id\n\t\t\t\t\t\t\tAND p.pro_id = ti.pro_id\n\t\t\t\t\t\t\tAND st.transfer_id = {$id_update}\n\t\t\t\t\t\t\tGROUP BY ti.pro_id ";
$rows_transfer = $db_global->getGlobalDb($sql_item);
if ($rows_transfer) {
foreach ($rows_transfer as $row_qty) {
//for from location id
$rows = $db_global->porductLocationExist($row_qty['pro_id'], $post['old_from_location']);
if ($rows) {
//update poduct location from
$data_qty_location = array('qty' => $rows['qty'] + $row_qty['qty'], 'last_usermod' => $GetUserId, 'last_mod_date' => new Zend_Date());
$db_global->updateRecord($data_qty_location, $rows['ProLocationID'], "ProLocationID", "tb_prolocation");
unset($data_qty_location);
unset($rows);
}
//for to location
$row = $db_global->porductLocationExist($row_qty['pro_id'], $post['old_to_location']);
if ($row) {
//update poduct location from
$data_qty_location = array('qty' => $row['qty'] - $row_qty['qty'], 'last_usermod' => $GetUserId, 'last_mod_date' => new Zend_Date());
$db_global->updateRecord($data_qty_location, $row['ProLocationID'], "ProLocationID", "tb_prolocation");
unset($data_qty_location);
unset($row);
}
}
}
unset($rows_transfer);
$sql = "DELETE FROM tb_transfer_item WHERE transfer_id IN ({$id_update})";
$db_global->deleteRecords($sql);
$identity = explode(',', $post['identity']);
foreach ($identity as $i) {
$data_item = array('transfer_id' => $id_update, 'pro_id' => $post['item_id_' . $i], 'qty' => $post['qty_id_' . $i], 'remark_transfer' => $post['remark_' . $i]);
$db->insert("tb_transfer_item", $data_item);
unset($data_item);
$rows = $db_global->porductLocationExist($post['item_id_' . $i], $post['from_location']);
if ($rows) {
//update poduct location from
$data_qty_location = array('qty' => $rows['qty'] - $post['qty_id_' . $i]);
$db_global->updateRecord($data_qty_location, $rows['ProLocationID'], "ProLocationID", "tb_prolocation");
//add move history
$data_history = array('transaction_type' => 2, 'pro_id' => $post['item_id_' . $i], 'date' => new Zend_Date(), 'location_id' => $post['from_location'], 'Remark' => $post['remark_' . $i], 'qty_edit' => $post['qty_id_' . $i], 'qty_before' => $rows['qty'], 'qty_after' => $rows['qty'] - $post['qty_id_' . $i], 'user_mod' => $GetUserId);
$db->insert("tb_move_history", $data_history);
unset($data_qty_location);
unset($rows);
//update product location to
$rows_gets_qty = $db_global->porductLocationExist($post['item_id_' . $i], $post['to_location']);
if ($rows_gets_qty) {
$data_qty_location = array('qty' => $rows_gets_qty['qty'] + $post['qty_id_' . $i]);
$db_global->updateRecord($data_qty_location, $rows_gets_qty['ProLocationID'], "ProLocationID", "tb_prolocation");
//add move history
$data_history = array('transaction_type' => 2, 'pro_id' => $post['item_id_' . $i], 'date' => new Zend_Date(), 'location_id' => $post['to_location'], 'Remark' => $post['remark_' . $i], 'qty_edit' => $post['qty_id_' . $i], 'qty_before' => $rows_gets_qty['qty'], 'qty_after' => $rows_gets_qty['qty'] + $post['qty_id_' . $i], 'user_mod' => $GetUserId);
$db->insert("tb_move_history", $data_history);
unset($rows_gets_qty);
} else {
$add_pro_location = array('pro_id' => $post['item_id_' . $i], 'LocationId' => $post['to_location'], 'qty' => $post['qty_id_' . $i], 'last_usermod' => $GetUserId, 'last_mod_date' => new Zend_Date());
$db->insert("tb_prolocation", $add_pro_location);
//if receive not have
$data_history = array('transaction_type' => 2, 'pro_id' => $post['item_id_' . $i], 'date' => new Zend_Date(), 'location_id' => $post['to_location'], 'Remark' => $post['remark_' . $i], 'qty_edit' => $post['qty_id_' . $i], 'qty_before' => 0, 'qty_after' => $post['qty_id_' . $i], 'user_mod' => $GetUserId);
$db->insert("tb_move_history", $data_history);
unset($data_history);
unset($add_pro_location);
}
} else {
//if from doesn't exist
//add qty in location if from doesn't exist
$add_pro_location = array('pro_id' => $post['item_id_' . $i], 'LocationId' => $post['from_location'], 'qty' => -$post['qty_id_' . $i], 'last_usermod' => $GetUserId, 'last_mod_date' => new Zend_Date());
$db->insert("tb_prolocation", $add_pro_location);
unset($add_pro_location);
//echeck for get product location
$data_history = array('transaction_type' => 1, 'pro_id' => $post['item_id_' . $i], 'date' => new Zend_Date(), 'location_id' => $post['from_location'], 'Remark' => $post['remark_' . $i], 'qty_edit' => $post['qty_id_' . $i], 'qty_after' => -$post['qty_id_' . $i], 'user_mod' => $GetUserId);
$db->insert("tb_move_history", $data_history);
unset($data_history);
//for get stock
$rows_gets_qty = $db_global->porductLocationExist($post['item_id_' . $i], $post['to_location']);
if ($rows_gets_qty) {
$data_qty_location = array('qty' => $rows_gets_qty['qty'] + $post['qty_id_' . $i]);
$db_global->updateRecord($data_qty_location, $rows_gets_qty['ProLocationID'], "ProLocationID", "tb_prolocation");
//add move history
$data_history = array('transaction_type' => 2, 'pro_id' => $post['item_id_' . $i], 'date' => new Zend_Date(), 'location_id' => $post['to_location'], 'Remark' => $post['remark_' . $i], 'qty_edit' => $post['qty_id_' . $i], 'qty_before' => $rows_gets_qty['qty'], 'qty_after' => $rows_gets_qty['qty'] + $post['qty_id_' . $i], 'user_mod' => $GetUserId);
$db->insert("tb_move_history", $data_history);
unset($data_history);
} else {
//if doesn't exist from and to
$add_pro_location = array('pro_id' => $post['item_id_' . $i], 'LocationId' => $post['to_location'], 'qty' => $post['qty_id_' . $i], 'last_usermod' => $GetUserId, 'last_mod_date' => new Zend_Date());
$db->insert("tb_prolocation", $add_pro_location);
//if doesn't exist from and to
$data_history = array('transaction_type' => 1, 'pro_id' => $post['item_id_' . $i], 'date' => new Zend_Date(), 'location_id' => $post['to_location'], 'Remark' => $post['remark_' . $i], 'qty_edit' => $post['qty_id_' . $i], 'qty_after' => $post['qty_id_' . $i], 'user_mod' => $GetUserId);
$db->insert("tb_move_history", $data_history);
unset($add_pro_location);
unset($data_history);
}
}
}
//forforeach
}
//for if
}
public function updateVendorCancellOrder($data)
{
try {
$db = $this->getAdapter();
$db->beginTransaction();
$db_global = new Application_Model_DbTable_DbGlobal();
$session_user = new Zend_Session_Namespace('auth');
$userName = $session_user->user_name;
$GetUserId = $session_user->user_id;
//for update order by id\
$id_order_update = $data['id'];
//update info of order in tb_purchase order
$info_purchase_order = array("vendor_id" => $data['v_name'], "LocationId" => $data["LocationId"], "order" => $data['txt_order'], "date_order" => $data['order_date'], "status" => $data["status"], "remark" => $data['remark'], "user_mod" => $GetUserId, "timestamp" => new Zend_Date(), "paid" => $data['paid'], "all_total" => $data['totalAmoun'], "balance" => $data['remain']);
$db_global->updateRecord($info_purchase_order, $id_order_update, "order_id", "tb_purchase_order");
// end update info of order in tb_purchase order
// Insert recieved order
if ($data["status"] == 5 or $data["status"] == 4) {
$recieved_order = array("recieve_type" => 1, "order_id" => $id_order_update, "order_no" => $data["txt_order"], "vendor_id" => $data['v_name'], "location_id" => $data["LocationId"], "order_date" => new Zend_Date(), "date_recieve" => new Zend_Date(), "status" => $data['status'], "paid" => $data['paid'], "all_total" => $data['totalAmoun'], "balance" => $data['remain'], "user_recieve" => $GetUserId);
$recieved_orders = $db_global->addRecord($recieved_order, "tb_recieve_order");
unset($recieved_orders);
}
// Delete old purchase order item before insert new purchase order item in old order_id
$sql = "DELETE FROM tb_purchase_order_item WHERE order_id IN ({$id_order_update})";
$db_global->deleteRecords($sql);
unset($sql);
$sql_history = "DELETE FROM tb_purchase_order_history WHERE `order` IN ({$id_order_update})";
$db_global->deleteRecords($sql_history);
unset($sql_history);
/// update
$ids = explode(',', $data['identity']);
//add order in tb_inventory must update code again 9/8/13
//print_r($ids);exit();
foreach ($ids as $i) {
// Insert recieved order item
if ($data["status"] == 5 or $data["status"] == 4) {
$recieved_order_item[$i] = array("recieve_id" => $recieved_orders, "pro_id" => $data['item_id_' . $i], "order_id" => $id_order_update, "qty_order" => $data['qty' . $i], "qty_recieve" => $data['qty' . $i], "price" => $data['price' . $i], "total_before" => $data['total' . $i], "sub_total" => $data['total' . $i]);
$db->insert("tb_recieve_order_item", $recieved_order_item[$i]);
unset($recieved_order_item[$i]);
}
// Insert New purchase order item in old order_id
$data_item[$i] = array('order_id' => $id_order_update, 'pro_id' => $data['item_id_' . $i], 'qty_order' => $data['qty' . $i], 'price' => $data['price' . $i], 'sub_total' => $data['total' . $i], 'total_befor' => $data['total' . $i], 'remark' => $data['remark_' . $i]);
//print_r($data_item); echo "<br />";echo "<br />";
$db->insert("tb_purchase_order_item", $data_item[$i]);
unset($data_item[$i]);
$data_history[$i] = array('order' => $id_order_update, 'pro_id' => $data['item_id_' . $i], 'type' => 1, 'customer_id' => $data['v_name'], 'status' => $data["status"], 'order_total' => $data['total' . $i], 'qty' => $data['qty' . $i], 'unit_price' => $data['price' . $i], 'sub_total' => $data['total' . $i], 'date' => $data["old_history_date"], 'last_update_date' => new Zend_Date());
//print_r($data_history);exit();
$db->insert("tb_purchase_order_history", $data_history[$i]);
unset($data_history[$i]);
$locationid = $data['LocationId'];
$itemId = $data['item_id_' . $i];
$qtyrecord = $data['qty' . $i];
//qty on 1 record
// Update stock in tb_product
$rows = $db_global->productLocationInventory($itemId, $locationid);
//to check product location
//print_r($rows); exit();
if ($rows) {
$getrecord_id = $rows["ProLocationID"];
if ($data["status"] == 5 or $data["status"] == 4) {
$itemOnHand = array('qty_onhand' => $rows["qty_onhand"] + $qtyrecord, 'qty_available' => $rows["qty_available"] + $qtyrecord, 'last_mod_date' => new Zend_date());
} else {
$itemOnHand = array('qty_onorder' => $rows["qty_onorder"] + $qtyrecord, 'last_mod_date' => new Zend_date());
}
$db_global->updateRecord($itemOnHand, $itemId, "pro_id", "tb_product");
unset($itemOnHand);
//End update total stock
// Update product Location
if ($data["status"] == 5 or $data["status"] == 4) {
$updatedata = array('qty' => $rows['qty'] + $qtyrecord, 'last_mod_date' => new Zend_date());
} else {
$updatedata = array('qty_onorder' => $rows['qty_onorder'] + $qtyrecord, 'last_mod_date' => new Zend_date());
}
$db_global->updateRecord($updatedata, $getrecord_id, "ProLocationID", "tb_prolocation");
unset($updatedata);
//End update stock product location //update stock record
} else {
//insert stock ;
$rows_pro_exit = $db_global->productLocation($itemId, $locationid);
// check product location exist
// if product exist Update qty in tb_prolocation
if ($rows_pro_exit) {
if ($data["status"] == 5 or $data["status"] == 4) {
$updatedata = array('qty' => $rows['qty'] + $qtyrecord, 'last_mod_date' => new Zend_date());
} else {
$updatedata = array('qty_onorder' => $rows['qty_onorder'] + $qtyrecord, 'last_mod_date' => new Zend_date());
}
$itemid = $db_global->updateRecord($updatedata, $rows_pro_exit['ProLocationID'], "ProLocationID", "tb_prolocation");
unset($updatedata);
// End if product exist Update qty in tb_prolocation
} else {
// If product not exist insert New product in tb_prolocation
$insertdata = array('pro_id' => $itemId, 'LocationId' => $locationid, 'last_usermod' => $GetUserId, 'qty' => $qtyrecord, 'last_mod_date' => new Zend_Date());
//update stock product location
$db->insert("tb_prolocation", $insertdata);
unset($insertdata);
}
// End If product not exist insert New product in tb_prolocation
$rowitem = $db_global->InventoryExist($itemId);
//to check product exist
// If productt exist update product in tb_product
if ($rowitem) {
if ($data["status"] == 5 or $data["status"] == 4) {
$itemOnHand = array('qty_onhand' => $rows["qty_onhand"] + $qtyrecord, 'qty_available' => $rows["qty_available"] + $qtyrecord, 'last_mod_date' => new Zend_date());
} else {
$itemOnHand = array('qty_onorder' => $rows["qty_onrder"] + $qtyrecord, 'last_mod_date' => new Zend_date());
}
//update total stock
$itemid = $db_global->updateRecord($itemOnHand, $itemId, "pro_id", "tb_product");
unset($itemOnHand);
// If productt exist update product in tb_product
} else {
// If product not exist insert new product in tb_product
$dataInventory = array('pro_id' => $itemId, 'qty_onhand' => $qtyrecord, 'qty_available' => $qtyrecord, 'last_mod_date' => new Zend_date());
$db->insert("tb_product", $dataInventory);
unset($dataInventory);
//update stock product location
}
}
}
$db->commit();
} catch (Exception $e) {
$db->rollBack();
$e->getMessage();
//echo $theCauseOfErrorOnlyDoNotRedirectToError;
exit;
}
}
public function RecievedPurchaseOrder($data)
{
try {
$db = $this->getAdapter();
$db->beginTransaction();
$db_global = new Application_Model_DbTable_DbGlobal();
$session_user = new Zend_Session_Namespace('auth');
$userName = $session_user->user_name;
$GetUserId = $session_user->user_id;
$_order_no = $data["order_no"];
$_order_id = $data["order_id"];
$ids = explode(',', $data['identity']);
//print_r($row);//exit();
if ($data['invoice_no'] == "") {
$date = new Zend_Date();
$recieved_num = "RO" . $date->get('hh-mm-ss');
} else {
$recieved_num = $data['invoice_no'];
}
$sql_itm = "SELECT\n\t\t\t\t(SELECT p.pro_id FROM tb_product AS p WHERE p.pro_id = po.`pro_id`) AS pro_id\n\t\t\t\t\t\n\t\t\t\t,(SELECT p.qty_onorder FROM tb_product AS p WHERE p.pro_id = po.`pro_id`) AS qty_onorder\n\t\t\t\t\n\t\t\t\t,(SELECT p.qty_onhand \tFROM tb_product AS p WHERE p.pro_id = po.`pro_id`) AS qty_onhand\n\t\t\t\t\n\t\t\t\t,(SELECT p.qty_available \tFROM tb_product AS p WHERE p.pro_id = po.`pro_id`) AS qty_available\n\t\t\t\t\t\n\t\t\t\t, SUM(po.`qty_order`) AS qty_order FROM\n\t\t\t\t\n\t\t\t\ttb_purchase_order_item AS po WHERE po.order_id = {$_order_no} GROUP BY po.pro_id";
$result = $db_global->getGlobalDb($sql_itm);
if ($result) {
foreach ($result as $row_pro) {
$row_get = $db_global->porductLocationExist($row_pro["pro_id"], $data["LocationId"]);
//print_r($row_get);
if ($row_get) {
$update_prolo_stock = array("qty_onorder" => $row_get["qty_onorder"] - $row_pro["qty_order"], "last_usermod" => $GetUserId, "last_mod_date" => new Zend_Date());
$update_data = $db_global->updateRecord($update_prolo_stock, $row_get["ProLocationID"], "ProLocationID", "tb_prolocation");
unset($update_prolo_stock);
}
$update_product_stock = array("qty_onorder" => $row_pro["qty_onorder"] - $row_pro["qty_order"], "last_mod_date" => new Zend_Date());
$sqls = $db_global->updateRecord($update_product_stock, $row_pro["pro_id"], "pro_id", "tb_product");
unset($update_product_stock);
}
}
unset($result);
try {
$info_purchase_order = array("vendor_id" => $data['v_name'], "LocationId" => $data["LocationId"], "status" => 5, "remark" => $data['remark'], "user_mod" => $GetUserId, "timestamp" => new Zend_Date(), "paid" => $data['paid'], "all_total" => $data['totalAmoun'], "balance" => $data['remain']);
$db_global->updateRecord($info_purchase_order, $_order_no, "order_id", "tb_purchase_order");
unset($info_purchase_order);
} catch (Exception $e) {
echo $e->getMessage();
}
$sql_recieve = new purchase_Model_DbTable_DbPurchaseOrder();
$result_recieve = $sql_recieve->recieved_info($_order_no);
$recieved_id = $result_recieve["recieve_id"];
if ($result_recieve) {
$data_recieved_order = array("recieve_type" => 1, "vendor_id" => $data['v_name'], "location_id" => $data["LocationId"], "date_recieve" => new Zend_Date(), "status" => 5, "is_active" => 1, "paid" => $data['paid'], "all_total" => $data['totalAmoun'], "balance" => $data['remain'], "user_recieve" => $GetUserId);
$recieved_order = $db_global->updateRecord($data_recieved_order, $result_recieve["recieve_id"], "recieve_id", "tb_recieve_order");
unset($data_recieved_order);
$sqls = "DELETE FROM tb_recieve_order_item WHERE recieve_id IN ({$recieved_id})";
$db_global->deleteRecords($sqls);
unset($sqls);
foreach ($ids as $i) {
$recieved_item[$i] = array("recieve_id" => $recieved_id, "pro_id" => $data['item_id_' . $i], "order_id" => $_order_no, "qty_order" => $data['qty' . $i], "qty_recieve" => $data['qty' . $i], "price" => $data['price' . $i], "total_before" => $data['total' . $i], "sub_total" => $data['total' . $i]);
$db->insert("tb_recieve_order_item", $recieved_item[$i]);
unset($recieved_item[$i]);
}
} else {
$recieve_order = array("recieve_no" => $recieved_num, "order_id" => $_order_no, "order_no" => $data['order_num'], "vendor_id" => $data["v_name"], "recieve_type" => 1, "location_id" => $data["LocationId"], "order_date" => $data["order_date"], "date_recieve" => new Zend_Date(), "status" => 5, "is_active" => 1, "paid" => $data["paid"], "all_total" => $data["remain"], "user_recieve" => $GetUserId);
$this->_name = "tb_recieve_order";
$recieved_order = $this->insert($recieve_order);
unset($recieve_order);
foreach ($ids as $i) {
$recieved_item[$i] = array("recieve_id" => $recieved_order, "pro_id" => $data['item_id_' . $i], "order_id" => $_order_no, "qty_order" => $data['qty' . $i], "qty_recieve" => $data['qty' . $i], "price" => $data['price' . $i], "total_before" => $data['total' . $i], "sub_total" => $data['total' . $i]);
$db->insert("tb_recieve_order_item", $recieved_item[$i]);
unset($recieved_item[$i]);
}
}
$sql = "DELETE FROM tb_purchase_order_item WHERE order_id IN ({$_order_no})";
$db_global->deleteRecords($sql);
unset($sql);
$sql_history = "DELETE FROM tb_purchase_order_history WHERE `order` IN ({$_order_no})";
$db_global->deleteRecords($sql_history);
unset($sql_history);
foreach ($ids as $i) {
// Insert New purchase order item in old order_id
$data_item[$i] = array('order_id' => $_order_no, 'pro_id' => $data['item_id_' . $i], 'qty_order' => $data['qty' . $i], 'price' => $data['price' . $i], 'sub_total' => $data['total' . $i], 'total_befor' => $data['total' . $i], 'remark' => $data['remark_' . $i]);
//print_r($data_item); echo "<br />";echo "<br />";
$db->insert("tb_purchase_order_item", $data_item[$i]);
unset($data_item[$i]);
$data_history[$i] = array('order' => $_order_no, 'pro_id' => $data['item_id_' . $i], 'type' => 1, 'customer_id' => $data['v_name'], 'status' => 5, 'order_total' => $data['total' . $i], 'qty' => $data['qty' . $i], 'unit_price' => $data['price' . $i], 'sub_total' => $data['total' . $i], 'date' => $data["order_date"], 'last_update_date' => new Zend_Date());
//print_r($data_history);exit();
$db->insert("tb_purchase_order_history", $data_history[$i]);
unset($data_history[$i]);
$locationid = $data['LocationId'];
$itemId = $data['item_id_' . $i];
$qtyrecord = $data['qty' . $i];
//qty on 1 record
// Update stock in tb_product
$rows = $db_global->productLocationInventory($itemId, $locationid);
//to check product location
if ($rows) {
$getrecord_id = $rows["ProLocationID"];
$itemOnHand = array('qty_onhand' => $rows["qty_onhand"] + $qtyrecord, 'qty_available' => $rows["qty_available"] + $qtyrecord, 'last_mod_date' => new Zend_date());
$db_global->updateRecord($itemOnHand, $itemId, "pro_id", "tb_product");
unset($itemOnHand);
$updatedata = array('qty' => $rows['qty'] + $qtyrecord, 'last_mod_date' => new Zend_date());
$db_global->updateRecord($updatedata, $getrecord_id, "ProLocationID", "tb_prolocation");
} else {
//insert stock ;
$rows_pro_exit = $db_global->productLocation($itemId, $locationid);
// check product location exist
if ($rows_pro_exit) {
$updatedata = array('qty' => $rows['qty'] + $qtyrecord, 'last_mod_date' => new Zend_date());
$itemid = $db_global->updateRecord($updatedata, $rows_pro_exit['ProLocationID'], "ProLocationID", "tb_prolocation");
unset($updatedata);
} else {
// If product not exist insert New product in tb_prolocation
$insertdata = array('pro_id' => $itemId, 'LocationId' => $locationid, 'last_usermod' => $GetUserId, 'qty' => $qtyrecord, 'last_mod_date' => new Zend_Date());
//update stock product location
$db->insert("tb_prolocation", $insertdata);
unset($insertdata);
}
// End If product not exist insert New product in tb_prolocation
$rowitem = $db_global->InventoryExist($itemId);
//to check product exist
// If productt exist update product in tb_product
if ($rowitem) {
$itemOnHand = array('qty_onhand' => $rows["qty_onhand"] + $qtyrecord, 'qty_available' => $rows["qty_available"] + $qtyrecord, 'last_mod_date' => new Zend_date());
$itemid = $db_global->updateRecord($itemOnHand, $itemId, "pro_id", "tb_product");
unset($itemOnHand);
} else {
// If product not exist insert new product in tb_product
$dataInventory = array('pro_id' => $itemId, 'qty_onhand' => $qtyrecord, 'qty_available' => $qtyrecord, 'last_mod_date' => new Zend_date());
$db->insert("tb_product", $dataInventory);
unset($dataInventory);
//update stock product location
}
}
}
$db->commit();
} catch (Exception $e) {
echo $e->getMessage();
exit;
$db->rollBack();
}
}
public function updatCat($data, $id)
{
try {
$db_global = new Application_Model_DbTable_DbGlobal();
$db = $this->getAdapter();
$db->beginTransaction();
$older = $data['id_code'];
$sql = "SELECT p.`order` FROM tb_purchase_order AS p WHERE p.`order`= '{$older}'";
$session_user = new Zend_Session_Namespace('auth');
$userName = $session_user->user_name;
$GetUserId = $session_user->user_id;
$info_purchase_order = array("vendor_id" => $data['vendor'], "date_in" => $data['date'], "status" => $data['status'], "user_mod" => $GetUserId, "timestamp" => new Zend_Date(), "paid" => $data['paid'], "discount" => $data['discount'], "all_total" => $data['totalAmoun'], "balance" => $data['remain']);
$this->_name = "tb_purchase_order";
$where = $db->quoteInto("order_id", $id);
$this->update($info_purchase_order, $where);
$row_old_order = $this->getSalesOderID($id);
if ($row_old_order) {
foreach ($row_old_order as $rs) {
$pro_exist = $db_global->InventoryExist($rs["pro_id"]);
//foreach ($pro_exist as $rs_pro){
//print_r($pro_exist["qty_onorder"]);exit();
$itemOnOrder = array('qty_onorder' => $pro_exist["qty_onorder"] - ($rs['qty_unit'] * $pro_exist["qty_perunit"] + $rs['qty']), 'qty_onhand' => $pro_exist["qty_onhand"] - ($rs['qty_unit'] * $pro_exist["qty_perunit"] + $rs['qty']), 'qty_available' => $pro_exist["qty_available"] - ($rs['qty_unit'] * $pro_exist["qty_perunit"] + $rs['qty']));
//update total stock
$db_global->updateRecord($itemOnOrder, $rs["pro_id"], "pro_id", "tb_product");
//}
}
}
$sql = "DELETE FROM tb_purchase_order_item WHERE order_id={$id}";
$db->query($sql);
$ids = explode(',', $data['identity']);
foreach ($ids as $i) {
$itemId = $data['item_id' . $i];
$qtyrecord = $data['unit_qty' . $i];
//qty on 1 record
$data_history[$i] = array('pro_id' => $data['item_id' . $i], 'type' => 1, 'order' => $id, 'customer_id' => $data['vendor'], 'date' => new Zend_Date(), 'status' => $data['status'], 'qty_unit' => $data['unit_qty' . $i], 'qty_per_unit' => $data['unit_price' . $i], 'qty' => $data['qty' . $i], 'price' => $data['price_per_qty' . $i], "discount" => $data['discount'], 'total' => $data['total_price' . $i]);
$order_history = $db_global->addRecord($data_history[$i], "tb_purchase_order_history");
unset($data_history[$i]);
$data_item[$i] = array('order_id' => $id, 'pro_id' => $data['item_id' . $i], 'qty_unit' => $data['unit_qty' . $i], 'qty_per_unit' => $data['unit_price' . $i], 'qty' => $data['qty' . $i], 'price' => $data['price_per_qty' . $i], 'total_befor' => $data['total_price' . $i], "discount" => $data['discount'], 'sub_total' => $data['total_price' . $i]);
$recieved_order = $db->insert("tb_purchase_order_item", $data_item[$i]);
$data_item_history[$i] = array('order_id' => $id, 'pro_id' => $data['item_id' . $i], 'qty_unit' => $data['unit_qty' . $i], 'qty_per_unit' => $data['unit_price' . $i], 'qty' => $data['qty' . $i], 'price' => $data['price_per_qty' . $i], 'total_befor' => $data['total_price' . $i], 'sub_total' => $data['total_price' . $i]);
$recieved_order_history = $db->insert("tb_purchase_order_item_history", $data_item[$i]);
$rows = $db_global->InventoryExist($itemId);
//print_r($rows);exit();
if ($rows) {
//$qty_onhand = $rowitem["QuantityOnHand"]+$data['qty'.$i];
echo $rows["qty_perunit"];
$itemOnOrder = array('qty_onorder' => $rows["qty_onorder"] + $data['unit_qty' . $i] * $rows["qty_perunit"] + $data['qty' . $i], 'qty_onhand' => $rows["qty_onhand"] + $data['unit_qty' . $i] * $rows["qty_perunit"] + $data['qty' . $i], 'qty_available' => $rows["qty_available"] + $data['unit_qty' . $i] * $rows["qty_perunit"] + $data['qty' . $i], 'pro_id' => $itemId);
//update total stock
$db_global->updateRecord($itemOnOrder, $itemId, "pro_id", "tb_product");
} else {
$dataInventory = array('pro_id' => $itemId, 'qty_onorder' => $rows["qty_onorder"] + $data['unit_qty' . $i] * $rows["qty_perunit"] + $data['qty' . $i], 'qty_onhand' => $rows["qty_onhand"] + $data['unit_qty' . $i] * $rows["qty_perunit"] + $data['qty' . $i], 'qty_available' => $rows["qty_available"] + $data['unit_qty' . $i] * $rows["qty_perunit"] + $data['qty' . $i], 'last_mod_date' => new Zend_date());
$db->insert("tb_product", $dataInventory);
//add move hostory
}
}
// exit();
$db->commit();
} catch (Exception $e) {
$db->rollBack();
$e->getMessage();
exit;
}
}
public function addTransferStock($post)
{
$db = $this->getAdapter();
$session_user = new Zend_Session_Namespace('auth');
$userName = $session_user->user_name;
$GetUserId = $session_user->user_id;
$db_global = new Application_Model_DbTable_DbGlobal();
$identity = explode(',', $post['identity']);
foreach ($identity as $i) {
if ($post['from_location_id_' . $i] !== $post['to_location_id_' . $i]) {
// $data_transfer=array(
// 'pro_id' => $post['item_id_'.$i],
// 'FromLocationId'=> $post['from_location_id_'.$i],
// 'ToLocationId' => $post['to_location_id_'.$i],
// 'qty' => $post['qty_id_'.$i],
// 'user_id' => $GetUserId,
// 'date_transfer' => new Zend_Date(),
// 'remark' => $post['remark']
// );
// $db->insert("tb_stocktransfer", $data_transfer);
// unset($data_transfer);
$rows = $db_global->porductLocationExist($post['item_id_' . $i], $post['from_location_id_' . $i]);
if ($rows) {
//update poduct location from
$data_qty_location = array('qty' => $rows['qty'] - $post['qty_id_' . $i]);
$db_global->updateRecord($data_qty_location, $rows['ProLocationID'], "ProLocationID", "tb_prolocation");
//add move history
$data_history = array('transaction_type' => 2, 'pro_id' => $post['item_id_' . $i], 'date' => new Zend_Date(), 'location_id' => $post['from_location_id_' . $i], 'qty_edit' => $post['qty_id_' . $i], 'qty_before' => $rows['qty'], 'qty_after' => $rows['qty'] - $post['qty_id_' . $i], 'user_mod' => $GetUserId);
$db->insert("tb_move_history", $data_history);
unset($data_qty_location);
unset($rows);
//update product location to
$rows_gets_qty = $db_global->porductLocationExist($post['item_id_' . $i], $post['to_location_id_' . $i]);
if ($rows_gets_qty) {
$data_qty_location = array('qty' => $rows_gets_qty['qty'] + $post['qty_id_' . $i]);
$itemid = $db_global->updateRecord($data_qty_location, $rows_gets_qty['ProLocationID'], "ProLocationID", "tb_prolocation");
//add move history
$data_history = array('transaction_type' => 2, 'pro_id' => $post['item_id_' . $i], 'date' => new Zend_Date(), 'location_id' => $post['to_location_id_' . $i], 'qty_edit' => $post['qty_id_' . $i], 'qty_before' => $rows_gets_qty['qty'], 'qty_after' => $rows_gets_qty['qty'] + $post['qty_id_' . $i], 'user_mod' => $GetUserId);
$db->insert("tb_move_history", $data_history);
} else {
$add_pro_location = array('pro_id' => $post['item_id_' . $i], 'LocationId' => $post['to_location_id_' . $i], 'qty' => $post['qty_id_' . $i], 'last_usermod' => $GetUserId, 'last_mod_date' => new Zend_Date());
$db->insert("tb_prolocation", $add_pro_location);
//if receive not have
$data_history = array('transaction_type' => 2, 'pro_id' => $post['item_id_' . $i], 'date' => new Zend_Date(), 'location_id' => $post['to_location_id_' . $i], 'qty_edit' => $post['qty_id_' . $i], 'qty_before' => 0, 'qty_after' => $post['qty_id_' . $i], 'user_mod' => $GetUserId);
$db->insert("tb_move_history", $data_history);
}
} else {
//if from doesn't exist
//add qty in location if from doesn't exist
$add_pro_location = array('pro_id' => $post['item_id_' . $i], 'LocationId' => $post['from_location_id_' . $i], 'qty' => -$post['qty_id_' . $i], 'last_usermod' => $GetUserId, 'last_mod_date' => new Zend_Date());
$db->insert("tb_prolocation", $add_pro_location);
unset($add_pro_location);
//echeck for get product location
$data_history = array('transaction_type' => 1, 'pro_id' => $post['item_id_' . $i], 'date' => new Zend_Date(), 'location_id' => $post['from_location_id_' . $i], 'qty_edit' => $post['qty_id_' . $i], 'qty_after' => -$post['qty_id_' . $i], 'user_mod' => $GetUserId);
$db->insert("tb_move_history", $data_history);
unset($data_history);
//for get stock
$rows_gets_qty = $db_global->porductLocationExist($post['item_id_' . $i], $post['to_location_id_' . $i]);
if ($rows_gets_qty) {
$data_qty_location = array('qty' => $rows_gets_qty['qty'] + $post['qty_id_' . $i]);
$db_global->updateRecord($data_qty_location, $rows_gets_qty['ProLocationID'], "ProLocationID", "tb_prolocation");
//add move history
$data_history = array('transaction_type' => 2, 'pro_id' => $post['item_id_' . $i], 'date' => new Zend_Date(), 'location_id' => $post['to_location_id_' . $i], 'qty_edit' => $post['qty_id_' . $i], 'qty_before' => $rows_gets_qty['qty'], 'qty_after' => $rows_gets_qty['qty'] + $post['qty_id_' . $i], 'user_mod' => $GetUserId);
$db->insert("tb_move_history", $data_history);
} else {
//if doesn't exist from and to
$add_pro_location = array('pro_id' => $post['item_id_' . $i], 'LocationId' => $post['to_location_id_' . $i], 'qty' => $post['qty_id_' . $i], 'last_usermod' => $GetUserId, 'last_mod_date' => new Zend_Date());
$db->insert("tb_prolocation", $add_pro_location);
//if doesn't exist from and to
$data_history = array('transaction_type' => 1, 'pro_id' => $post['item_id_' . $i], 'date' => new Zend_Date(), 'location_id' => $post['to_location_id_' . $i], 'qty_edit' => $post['qty_id_' . $i], 'qty_after' => $post['qty_id_' . $i], 'user_mod' => $GetUserId);
$db->insert("tb_move_history", $data_history);
}
}
}
}
}
