db_inventory = $this->load->database("inventory", true);
$this->db_inventory_log = $this->load->database('inventory_log', true);
$this->load->library('ImageManipulator');
}
function index()
{
echo "Api: Training Playground";
echo "
";
$cek = $this->db_inventory->query("select database() as current_db")->result();
// echo $this->db->last_query();
print_r($cek);
}
// select fn_numbering('Item') buat panggil generate code
function search()
{
try {
//# cek token valid
if (! $this->isLogin) {
$this->sys_error("Invalid Token");
exit;
}
$prm = $this->sys_input;
$sql_data = "";
$sql_filter = "";
$search = "";
$search_dropdown_filter_group = "";
$where = " ItemIsActive = 'Y' ";
$filter_group = "";
$join_filter_group = "";
if (isset($prm['search_dropdown_filter_group'])) {
$search_dropdown_filter_group = trim($prm["search_dropdown_filter_group"]);
if ($search_dropdown_filter_group != "") {
$search_dropdown_filter_group = $prm['search_dropdown_filter_group'];
// $where .= " ";
$sql_get_data_by_name = "SELECT ItemGroupID
FROM itemgroup
WHERE ItemGroupIsActive = 'Y'
AND ItemGroupName = '$search_dropdown_filter_group'";
$qry_filter_group = $this->db_inventory->query($sql_get_data_by_name);
// print($this->db_inventory->last_query());
if ($qry_filter_group) {
$rows_filter_group = $qry_filter_group->row_array();
} else {
$this->sys_error_db("itemgroup select by name");
exit;
}
$search_dropdown_filter_group = $rows_filter_group['ItemGroupID'];
// $where .= " AND ( ItemGroupName = '$search_dropdown_filter_group' )";
// $where .= " AND ( ItemItemGroupID = '$search_dropdown_filter_group' )";
$join_filter_group .= " AND ItemItemGroupID = '$search_dropdown_filter_group' ";
}
}
if (isset($prm['search'])) {
$search = trim($prm["search"]);
if ($search != "") {
$search = $prm['search'];
$where .= " AND ( ItemName like '%$search%')";
}
// else{
// $search = '%%';
// $where .= " AND ( ItemName like $search)";
// }
}
// $where .= ")";
$all = $prm['all'];
$limit = '';
if($all == 'N'){
$limit = ' LIMIT 10';
}
// sort
$sortBy = $prm['sortBy'];
$sortStatus = $prm['sortStatus'];
if($sortBy){
$q_sort = "ORDER BY ".$sortBy." ".$sortStatus;
}
$number_offset = 0;
$number_limit = 10;
// $number_limit = 2;
if($prm['current_page'] > 0) {
$number_offset = ($prm['current_page'] - 1) * $number_limit;
}
$sql_filter .= "SELECT COUNT(*) as total from (
SELECT ItemID
FROM item
JOIN itemgroup
ON ItemItemGroupID = ItemGroupID
AND ItemGroupIsActive = 'Y'
$join_filter_group
LEFT JOIN itemcategory
ON ItemItemCategoryID = ItemCategoryID
AND ItemCategoryIsActive = 'Y'
LEFT JOIN itemmanufacture
ON ItemItemManufactureID = ItemManufactureID
AND ItemManufactureIsActive = 'Y'
LEFT JOIN itembrand
ON ItemItemBrandID = ItemBrandID
AND ItemBrandIsActive = 'Y'
join itemunitmap
ON ItemID = ItemUnitMapItemID
AND ItemUnitMapIsActive = 'Y'
join itemunit
ON ItemUnitMapItemUnitID = ItemUnitID
AND ItemUnitIsActive = 'Y'
WHERE $where
group by ItemID
) x";
$qry_filter = $this->db_inventory->query($sql_filter);
// print($this->db_inventory->last_query());
$tot_count = 0;
$tot_page = 0;
if ($qry_filter) {
// $tot_count = count($qry_filter->result_array()[0]["total"]);
$tot_count = $qry_filter->row()->total;
$tot_page = ceil($tot_count/$number_limit);
} else {
$this->db->trans_rollback();
$this->sys_error_db("item count", $this->db);
exit;
}
$sql_data .= "SELECT
distinct ItemID, ItemSKU, ItemName, ItemItemGroupID, ItemItemCategoryID, ItemUnitMapItemUnitID,
ItemItemManufactureID, ItemItemBrandID,
ItemName as name, ItemID as id,
ItemGroupID, ItemGroupName, ItemGroupCode,
ItemItemCategoryID, ItemUnitMapItemUnitID,
ItemItemManufactureID, ItemItemBrandID,
ItemGroupID, ItemGroupName,
ItemCategoryID, ItemCategoryName,
ItemManufactureID, ItemManufactureName,
ItemBrandID, ItemBrandName,
ItemUnitID,ItemUnitName, ItemImage,ItemImageThumb, ItemBarcode,
GROUP_CONCAT(ItemUnitName) as item_unit_name_group_concat,
'' as satuan
FROM item
JOIN itemgroup
ON ItemItemGroupID = ItemGroupID
AND ItemGroupIsActive = 'Y'
$join_filter_group
LEFT JOIN itemcategory
ON ItemItemCategoryID = ItemCategoryID
AND ItemCategoryIsActive = 'Y'
LEFT JOIN itemmanufacture
ON ItemItemManufactureID = ItemManufactureID
AND ItemManufactureIsActive = 'Y'
LEFT JOIN itembrand
ON ItemItemBrandID = ItemBrandID
AND ItemBrandIsActive = 'Y'
join itemunitmap
ON ItemID = ItemUnitMapItemID
AND ItemUnitMapIsActive = 'Y'
join itemunit
ON ItemUnitMapItemUnitID = ItemUnitID
AND ItemUnitIsActive = 'Y'
WHERE $where
group by ItemID
$q_sort
limit ? offset ?";
$qry_data = $this->db_inventory->query($sql_data,
[
$number_limit,
$number_offset
]);
// var_dump($this->db_inventory->last_query());
if ($qry_data) {
$rows = $qry_data->result_array();
if(count($rows) > 0){
foreach($rows as $key => $value){
$sql = "SELECT ItemUnitMapID as id,
ItemUnitMapItemUnitID,
if(ItemUnitMapIsPurchase='Y','purchase','base') as detail_tipe_itemunit,
ItemUnitName as item_unit_name,
ItemUnitID as item_unit_id,
ItemUnitID,
ItemUnitName,
ItemUnitMapMin as item_unit_min
from itemunitmap
JOIN itemunit
ON ItemUnitMapItemUnitID = ItemUnitID
AND ItemUnitIsActive = 'Y'
Where
ItemUnitMapIsActive = 'Y'
AND ItemUnitMapItemID = ?
";
$qry = $this->db_inventory->query($sql, array($value['id']));
if($qry){
$companyaddress = $qry->result_array();
$rows[$key]['satuan'] = $companyaddress;
}
}
}else{
$rows[0]['satuan'] = [];
}
// var_dump($this->db_inventory->last_query());
} else {
$this->sys_error_db("itemunit map error");
exit;
}
$result = array(
"total" => $tot_page,
// "total_filter"=> count($rows),
"total_filter" => $tot_count,
"records" => $rows,
// "sql" => $sql_data,
// "sql_filter" => $sql_filter
);
$this->sys_ok($result);
} catch(Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
function add()
{
try {
//# cek token valid
if (! $this->isLogin) {
$this->sys_error("Invalid Token");
exit;
}
//begin transaction
$this->db->trans_begin();
//# ambil parameter input
$prm = $this->sys_input;
// ItemItemGroupID, ItemItemUnitID, ItemName
// mandatory
$item_unit_id = 0;
$item_group_id = $prm['item_group_id'];
// $item_unit_id = $prm['item_unit_id'];
$item_name = $prm['item_name'];
if(isset($prm['item_unit_id'])){
if(trim($prm['item_unit_id']) != ""){
$item_unit_id = (int) $prm['item_unit_id'];
}
}
// convert
$item_sku = "";
// tidak
$item_category_id = 0;
$item_manufacture_id = 0;
$item_brand_id = 0;
$item_image_name = "";
$item_barcode = "";
$item_min = 0;
$item_max = 0;
$userid = $this->sys_user['M_UserID'];
// $userid = 1;
$item_name_search = "";
$item_name = "";
if (isset($prm['item_name'])) {
$item_name_search = trim($prm["item_name"]);
$item_name = trim($prm['item_name']);
if ($item_name_search != "") {
$item_name_search = $prm['item_name'];
}
}
// cek yg tidak mandatory
if(isset($prm['item_category_id'])){
if(trim($prm['item_category_id']) != ""){
$item_category_id = (int) $prm['item_category_id'];
}
}
if(isset($prm['item_manufacture_id'])){
if(trim($prm['item_manufacture_id']) != ""){
$item_manufacture_id = (int) $prm['item_manufacture_id'];
}
}
if(isset($prm['item_brand_id'])){
if(trim($prm['item_brand_id']) != ""){
$item_brand_id = (int) $prm['item_brand_id'];
}
}
if(isset($prm['item_image_name'])){
if(trim($prm['item_image_name']) != ""){
$item_image_name = trim($prm['item_image_name']);
}
}
if(isset($prm['item_barcode'])){
if(trim($prm['item_barcode']) != ""){
$item_barcode = trim($prm['item_barcode']);
}
}
if(isset($prm['item_min'])){
if(trim($prm['item_min']) != ""){
$item_min = (int) ($prm['item_min']);
}
}
if(isset($prm['item_max'])){
if(trim($prm['item_max']) != ""){
$item_max = (int) ($prm['item_max']);
}
}
// echo json_encode(
// array(
// 'item_group_id' => $item_group_id,
// 'item_name' => $item_name,
// 'item_unit_id' => $item_unit_id,
// 'item_category_id' => $item_category_id,
// 'item_manufacture_id' => $item_manufacture_id,
// 'item_brand_id' => $item_brand_id,
// 'item_image_name' => $item_image_name,
// 'item_barcode' => $item_barcode,
// 'item_min' => $item_min,
// 'item_max' => $item_max
// )
// );
$sql_count = "SELECT COUNT(*) as exist
FROM item
LEFT JOIN itemgroup
ON ItemItemGroupID = ItemGroupID
LEFT JOIN itemcategory
ON ItemItemCategoryID = ItemCategoryID
LEFT JOIN itemmanufacture
ON ItemItemManufactureID = ItemManufactureID
LEFT JOIN itembrand
ON ItemItemBrandID = ItemBrandID
WHERE ItemIsActive = 'Y'
AND ItemName = '$item_name_search'";
$query_count = $this->db_inventory->query($sql_count);
$last_query_count = $this->db_inventory->last_query();
if(!$query_count){
$this->db->trans_rollback();
$this->sys_error_db("item search & count by name");
exit;
}else{
// echo $last_query_count;
$this->db_inventory->trans_start();
$this->db_inventory->trans_strict(FALSE);
// $item_code_generate = "Item";
$get_count = $query_count->row_array();
if($get_count['exist'] == 0)
{
// call fungsi untuk generate code
$sql_get_item_code = "SELECT ItemGroupCode as code
FROM itemgroup
WHERE ItemGroupID = ? ";
$query_get_code_by_id = $this->db_inventory->query($sql_get_item_code,[
$item_group_id
]);
$get_item_group_code_by_id = $query_get_code_by_id->row_array();
$sql_generate_code = "select fn_numbering_item(?) as code";
$query_generate_code = $this->db_inventory->query($sql_generate_code,
[
$get_item_group_code_by_id['code']
]);
if(!$query_generate_code){
$this->db->trans_rollback();
$this->sys_error_db("item call sp");
exit;
}
// $get_item_group_code_by_id = $query_get_code_by_id->row_array();
$get_item_code = $query_generate_code->row_array();
$item_sku = $get_item_code['code'];
// ItemItemGroupID, ItemItemUnitID, ItemName
// query insert
$last_id = 0;
$sql_insert = "INSERT INTO item
(
ItemItemGroupID,
ItemItemCategoryID,
ItemItemManufactureID,
ItemItemBrandID,
ItemImage,
ItemSKU,
ItemName,
ItemBarcode,
ItemCreated,
ItemLastUpdated,
ItemUserID
)
VALUES (
?,
?,
?,
?,
?,
?,
?,
?,
now(),
now(),
?
)";
$query_insert = $this->db_inventory->query($sql_insert,
[
$item_group_id,
$item_category_id,
$item_manufacture_id,
$item_brand_id,
$item_image_name,
$item_sku,
$item_name,
$item_barcode,
$userid
]);
// echo $this->db_inventory->last_query();
if (!$query_insert) {
$this->db->trans_rollback();
$this->sys_error_db("item insert");
exit;
}
$last_id = $this->db_inventory->insert_id();
// insert batch item unit map start
if(count($prm['satuan']) > 0) {
foreach($prm['satuan'] as $key => $value) {
$ItemUnitMapItemUnitID = trim($value['item_unit_id']);
// $ItemUnitMapIsPurchase = trim($value['detail_tipe_itemunit']);
$itemunitpurchase = "";
$itemunitbase = "";
$item_unit_min = trim($value['item_unit_min']);
if($value['detail_tipe_itemunit'] == "purchase"){
$itemunitpurchase = 'Y';
$itemunitbase = 'N';
}else{
if($value['detail_tipe_itemunit'] == "base"){
$itemunitpurchase = 'N';
$itemunitbase = 'Y';
}
}
// insert itemunitmap
$sql = "INSERT INTO itemunitmap(
ItemUnitMapItemID,
ItemUnitMapItemUnitID,
ItemUnitMapIsPurchase,
ItemUnitMapMin,
ItemUnitMapIsBase,
ItemUnitMapIsActive,
ItemUnitMapUserID,
ItemUnitMapCreated,
ItemUnitMapLastUpdated
)
VALUES(
?,?,?,?,?,?,?,NOW(),NOW()
)";
$qry = $this->db_inventory->query($sql,
array(
$last_id,
$ItemUnitMapItemUnitID,
$itemunitpurchase,
$item_unit_min,
$itemunitbase,
'Y',
$userid
));
// echo $this->db_inventory->last_query();
if(!$qry){
$this->db->trans_rollback();
$this->sys_error_db("add item unit map error", $this->db_inventory->last_query());
exit;
}
}
}
// insert batch item unit map end
$this->db_inventory->trans_complete();
// insert log
$id = $last_id;
$sql_json_after = "SELECT item.*, '' as satuan
FROM item
WHERE ItemIsActive = 'Y'
AND ItemID = ?";
$qry = $this->db_inventory->query($sql_json_after,
[
$id
]);
if (!$qry) {
$this->db->trans_rollback();
$this->sys_error_db("item select json");
exit;
}
// $data_by_id = $qry->row();
$row_after = $qry->row_array();
$sql = "SELECT *
FROM `itemunitmap`
WHERE
ItemUnitMapItemID = ? AND
ItemUnitMapIsActive = 'Y'";
$qry = $this->db_inventory->query($sql,
array($id)
);
if($qry){
$row_after['satuan'] = $qry->result_array();
}else{
$this->sys_error_db("select itemunitmap error", $this->db_inventory->last_query());
exit;
}
// $json_after_log = json_encode($data_by_id);
$data_log_after = $row_after;
// print_r($json_after_log);
$sql_insert_log = "INSERT INTO item_log(
ItemLogItemID,
ItemLogStatus,
ItemLogJSONBefore,
ItemLogJSONAfter,
ItemLogUserID,
ItemLogCreated
) VALUES (
?,
'ADD',
null,
?,
?,
now()
)";
$qry_insert_log = $this->db_inventory_log->query($sql_insert_log,
[
$id,
json_encode($data_log_after),
$userid
]);
if (!$qry_insert_log) {
$this->db->trans_rollback();
$this->sys_error_db("item insert log");
exit;
}
// sukses
if($prm['image']){
$upload_image = $this->upload($prm['image'],
$item_sku,
$userid,
'ADD');
if(!$upload_image){
$this->sys_error_db("gagal simpan image");
exit;
}
}
$this->db->trans_commit();
$result = array(
"total" => 1,
"records" => array("xid" => 0)
);
$this->sys_ok($result);
}
else
{
$errors = array();
if($get_count['exist'] != 0){
array_push($errors,array(
'field'=>'name',
'msg'=>'Nama sudah ada'
));
}
$result = array (
"total" => -1,
"errors" => $errors,
"records" => 0);
$this->sys_ok($result);
}
}
} catch(Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
function edit_lama()
{
try {
//# cek token valid
if (! $this->isLogin) {
$this->sys_error("Invalid Token");
exit;
}
//begin transaction
$this->db->trans_begin();
//# ambil parameter input
$prm = $this->sys_input;
// ItemItemGroupID, ItemItemUnitID, ItemName
// mandatory
$item_group_id = $prm['item_group_id'];
// $item_unit_id = $prm['item_unit_id'];
if(isset($prm['item_unit_id'])){
if(trim($prm['item_unit_id']) != ""){
$item_unit_id = (int) $prm['item_unit_id'];
}
}
$item_name = $prm['item_name'];
$id = $prm['item_id'];
// convert
$item_sku = "";
// tidak
$item_category_id = 0;
$item_manufacture_id = 0;
$item_brand_id = 0;
$item_image_name = "";
$item_barcode = "";
$item_min = 0;
$item_max = 0;
$userid = $this->sys_user['M_UserID'];
// $userid = 1;
$flag = 0;
$item_name_search = "";
$item_name = "";
if (isset($prm['item_name'])) {
$item_name_search = trim($prm["item_name"]);
$item_name = trim($prm['item_name']);
if ($item_name_search != "") {
$item_name_search = $prm['item_name'];
}
}
// cek yg tidak mandatory
if(isset($prm['item_category_id'])){
if(trim($prm['item_category_id']) != ""){
$item_category_id = (int) $prm['item_category_id'];
}
}
if(isset($prm['item_manufacture_id'])){
if(trim($prm['item_manufacture_id']) != ""){
$item_manufacture_id = (int) $prm['item_manufacture_id'];
}
}
if(isset($prm['item_brand_id'])){
if(trim($prm['item_brand_id']) != ""){
$item_brand_id = (int) $prm['item_brand_id'];
}
}
if(isset($prm['item_image_name'])){
if(trim($prm['item_image_name']) != ""){
$item_image_name = trim($prm['item_image_name']);
}
}
if(isset($prm['item_barcode'])){
if(trim($prm['item_barcode']) != ""){
$item_barcode = trim($prm['item_barcode']);
}
}
if(isset($prm['item_min'])){
if(trim($prm['item_min']) != ""){
$item_min = (int) ($prm['item_min']);
}
}
if(isset($prm['item_max'])){
if(trim($prm['item_max']) != ""){
$item_max = (int) ($prm['item_max']);
}
}
// echo json_encode(
// array(
// 'item_group_id' => $item_group_id,
// 'item_name' => $item_name,
// 'item_unit_id' => $item_unit_id,
// 'item_category_id' => $item_category_id,
// 'item_manufacture_id' => $item_manufacture_id,
// 'item_brand_id' => $item_brand_id,
// 'item_image_name' => $item_image_name,
// 'item_barcode' => $item_barcode,
// 'item_min' => $item_min,
// 'item_max' => $item_max
// )
// );
$sql_count = "SELECT COUNT(*) as exist
FROM item
LEFT JOIN itemgroup
ON ItemItemGroupID = ItemGroupID
LEFT JOIN itemcategory
ON ItemItemCategoryID = ItemCategoryID
LEFT JOIN itemmanufacture
ON ItemItemManufactureID = ItemManufactureID
LEFT JOIN itembrand
ON ItemItemBrandID = ItemBrandID
WHERE ItemIsActive = 'Y'
AND ItemName = '$item_name_search'";
$query_count = $this->db_inventory->query($sql_count);
$last_query_count = $this->db_inventory->last_query();
if(!$query_count){
$this->db->trans_rollback();
$this->sys_error_db("item search & count by name");
exit;
}else{
// echo $last_query_count;
$get_count = $query_count->row_array();
// if($get_count['exist'] == 0)
// {
// call fungsi untuk generate code
// $item_sku_lama = "";
// $sql_item = "SELECT ItemSKU, ItemGroupID
// WHERE ItemIsActive = 'Y'
// AND ";
$item_code_generate = "Item";
$sql_generate_code = "select fn_numbering(?) as code";
$query_generate_code = $this->db_inventory->query($sql_generate_code,
[
$item_code_generate
]);
// echo $this->db->last_query();
if(!$query_generate_code){
$this->db->trans_rollback();
$this->sys_error_db("item call sp");
exit;
}
$sql_get_item_code = "SELECT ItemGroupCode
FROM itemgroup
WHERE ItemGroupID = ? ";
$query_get_code_by_id = $this->db_inventory->query($sql_get_item_code,[
$item_group_id
]);
if (!$query_get_code_by_id) {
$this->db->trans_rollback();
$this->sys_error_db("itemgroup select");
exit;
}
$get_item_group_code_by_id = $query_get_code_by_id->row_array();
$get_item_code = $query_generate_code->row_array();
$item_sku_new = $get_item_group_code_by_id['ItemGroupCode']."".$get_item_code['code'];
$sql_get_item_by_id = "SELECT ItemItemGroupID, ItemImage, ItemSKU,
ItemImageThumb
FROM item
where ItemIsActive = 'Y'
AND ItemID = ?";
$query_item_by_id = $this->db_inventory->query($sql_get_item_by_id, [$id]);
if (!$query_item_by_id) {
$this->db->trans_rollback();
$this->sys_error_db("item by id select");
exit;
}
$get_item_by_id = $query_item_by_id->row_array();
if($prm['item_group_id'] == $get_item_by_id['ItemItemGroupID']){
$item_sku = $prm['item_sku'];
}
else{
$item_sku = $item_sku_new;
}
// ItemItemGroupID, ItemItemUnitID, ItemName
// ganti nama file dan insert log gambar lama
$gambar_lama = $get_item_by_id['ItemImage'];
$gambar_thumb = $get_item_by_id['ItemImageThumb'];
if($prm['image']){
// echo "masuk";
$home_dir = "/home/one/project/one";
$target_dir = $home_dir . $gambar_lama;
$resource = "/home/one/project/one/one-media/one-inventory/item/";
if (file_exists($target_dir)) {
// rename image and thumbnail
$location_image = $home_dir.$gambar_lama;
$location_thumb = $home_dir.$gambar_thumb;
$rename_image = $resource.$get_item_by_id['ItemSKU']."_".date('d-m-Y H:i:s').".jpg";
$rename_thumb = $resource.$get_item_by_id['ItemSKU']."_thumb"."_".date('d-m-Y H:i:s').".jpg";
$image_log = "/one-media/one-inventory/item/".$get_item_by_id['ItemSKU']."_".date('d-m-Y H:i:s').".jpg";
$image_thumbnail_log = "/one-media/one-inventory/item/".$get_item_by_id['ItemSKU']."_thumb"."_".date('d-m-Y H:i:s').".jpg";
if(rename($location_image, $rename_image)){
if(rename($location_thumb, $rename_thumb)){
// print('sukses');
// $flag ++;
// json before
$sql_json_before = "SELECT *
FROM item
WHERE ItemIsActive = 'Y'
AND ItemID = ?";
$qry_json_before = $this->db_inventory->query(
$sql_json_before,
[
$id
]);
if (!$qry_json_before) {
$this->db->trans_rollback();
$this->sys_error_db("item select json before");
exit;
}
$data_before_by_id = $qry_json_before->row();
$json_before_log = json_encode($data_before_by_id);
// image log
if($prm['image']){
// update image log
$code = "PHOTO.PATIENT.EDIT";
$save_log = $this->db_inventory_log->set('ItemImageCode', $code)
->set('ItemImageSKU', $get_item_by_id['ItemSKU'])
->set('ItemImageDate',date('Y-m-d H:i:s'))
->set('ItemImageCreated',date('Y-m-d H:i:s'))
->set('ItemImageUserID',$userid)
->set('ItemImageUrl', $image_log)
->insert('itemimage_log');
if(!$save_log){
return false;
}
// update
// print($json_before_log);
$upload_image = $this->upload_edit($prm['image'],
$item_sku,
$userid);
if(!$upload_image){
$this->sys_error_db("gagal simpan image");
exit;
}
// query update with new image
$sql_update_new_image = "UPDATE item
SET
ItemItemGroupID = ?,
ItemItemCategoryID = ?,
ItemItemUnitID = ?,
ItemItemManufactureID = ?,
ItemItemBrandID = ?,
ItemSKU = ?,
ItemName = ?,
ItemBarcode = ?,
ItemMin = ?,
ItemMax = ?,
ItemLastUpdated = now(),
ItemUserID = ?
WHERE ItemID = ?";
$query_update_new = $this->db_inventory->query($sql_update_new_image,
[
$item_group_id,
$item_category_id,
$item_unit_id,
$item_manufacture_id,
$item_brand_id,
$item_sku,
$item_name,
$item_barcode,
$item_min,
$item_max,
$userid,
$id
]);
// echo $this->db_inventory->last_query();
if (!$query_update_new) {
$this->db->trans_rollback();
$this->sys_error_db("item update data baru");
exit;
}
// json after
$sql_json_after = "SELECT *
FROM item
WHERE ItemIsActive = 'Y'
AND ItemID = ?";
$qry_json_after = $this->db_inventory->query($sql_json_after,
[
$id
]);
if (!$qry_json_after) {
$this->db->trans_rollback();
$this->sys_error_db("item select json after");
exit;
}
$data_after_by_id = $qry_json_after->row();
$json_after_log = json_encode($data_after_by_id);
$sql_insert_log = "INSERT INTO item_log(
ItemLogItemID,
ItemLogStatus,
ItemLogJSONBefore,
ItemLogJSONAfter,
ItemLogUserID,
ItemLogCreated
) VALUES (
?,
'EDIT',
?,
?,
?,
now()
)";
$qry_insert_log = $this->db_inventory_log->query($sql_insert_log,
[
$id,
$json_before_log,
$json_after_log,
$userid
]);
if (!$qry_insert_log) {
$this->db->trans_rollback();
$this->sys_error_db("item edit log");
exit;
}
}
// // sukses
$this->db->trans_commit();
$result = array(
"total" => 1,
"records" => array("xid" => 0)
);
$this->sys_ok($result);
}
} else {
$this->sys_error_db("image and thumbnail rename failed");
exit;
}
}
}
// else
// {
// $errors = array();
// if($get_count['exist'] != 0){
// array_push($errors,array(
// 'field'=>'name',
// 'msg'=>'Nama sudah ada'
// ));
// }
// if($flag == 0){
// array_push($errors,array(
// 'field'=>'name',
// 'msg'=>'Nama sudah ada'
// ));
// }
// $result = array (
// "total" => -1,
// "errors" => $errors,
// "records" => 0);
// $this->sys_ok($result);
// }
}
} catch(Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
function edit()
{
try {
//# cek token valid
if (! $this->isLogin) {
$this->sys_error("Invalid Token");
exit;
}
//begin transaction
$this->db->trans_begin();
//# ambil parameter input
$prm = $this->sys_input;
// ItemItemGroupID, ItemItemUnitID, ItemName
// echo $prm;
// mandatory
$item_group_id = $prm['item_group_id'];
// $item_unit_id = $prm['item_unit_id'];
if(isset($prm['item_unit_id'])){
if(trim($prm['item_unit_id']) != ""){
$item_unit_id = (int) $prm['item_unit_id'];
}
}
$item_name = $prm['item_name'];
$id = $prm['item_id'];
// convert
$item_sku = "";
// tidak
$item_category_id = 0;
$item_manufacture_id = 0;
$item_brand_id = 0;
$item_image_name = "";
$item_barcode = "";
$item_min = 0;
$item_max = 0;
$userid = $this->sys_user['M_UserID'];
// $userid = 1;
$flag = 0;
$item_name_search = "";
$item_name = "";
if (isset($prm['item_name'])) {
$item_name_search = trim($prm["item_name"]);
$item_name = trim($prm['item_name']);
if ($item_name_search != "") {
$item_name_search = $prm['item_name'];
}
}
// cek yg tidak mandatory
if(isset($prm['item_category_id'])){
if(trim($prm['item_category_id']) != ""){
$item_category_id = (int) $prm['item_category_id'];
}
}
if(isset($prm['item_manufacture_id'])){
if(trim($prm['item_manufacture_id']) != ""){
$item_manufacture_id = (int) $prm['item_manufacture_id'];
}
}
if(isset($prm['item_brand_id'])){
if(trim($prm['item_brand_id']) != ""){
$item_brand_id = (int) $prm['item_brand_id'];
}
}
if(isset($prm['item_image_name'])){
if(trim($prm['item_image_name']) != ""){
$item_image_name = trim($prm['item_image_name']);
}
}
if(isset($prm['item_barcode'])){
if(trim($prm['item_barcode']) != ""){
$item_barcode = trim($prm['item_barcode']);
}
}
if(isset($prm['item_min'])){
if(trim($prm['item_min']) != ""){
$item_min = (int) ($prm['item_min']);
}
}
if(isset($prm['item_max'])){
if(trim($prm['item_max']) != ""){
$item_max = (int) ($prm['item_max']);
}
}
// echo json_encode(
// array(
// 'item_group_id' => $item_group_id,
// 'item_name' => $item_name,
// 'item_unit_id' => $item_unit_id,
// 'item_category_id' => $item_category_id,
// 'item_manufacture_id' => $item_manufacture_id,
// 'item_brand_id' => $item_brand_id,
// 'item_image_name' => $item_image_name,
// 'item_barcode' => $item_barcode,
// 'item_min' => $item_min,
// 'item_max' => $item_max
// )
// );
$sql_count = "SELECT COUNT(*) as exist
FROM item
LEFT JOIN itemgroup
ON ItemItemGroupID = ItemGroupID
LEFT JOIN itemcategory
ON ItemItemCategoryID = ItemCategoryID
LEFT JOIN itemmanufacture
ON ItemItemManufactureID = ItemManufactureID
LEFT JOIN itembrand
ON ItemItemBrandID = ItemBrandID
WHERE ItemIsActive = 'Y'
AND ItemName = '$item_name_search'";
$query_count = $this->db_inventory->query($sql_count);
$last_query_count = $this->db_inventory->last_query();
if(!$query_count){
// echo "1";
$this->db->trans_rollback();
$this->sys_error_db("item search & count by name");
exit;
}else{
// echo "1";
// echo $last_query_count;
$get_count = $query_count->row_array();
// if($get_count['exist'] == 0)
// {
// call fungsi untuk generate code
// $item_sku_lama = "";
// $sql_item = "SELECT ItemSKU, ItemGroupID
// WHERE ItemIsActive = 'Y'
// AND ";
$sql_get_item_code = "SELECT ItemGroupCode as code
FROM itemgroup
WHERE ItemGroupID = ? ";
$query_get_code_by_id = $this->db_inventory->query($sql_get_item_code,[
$item_group_id
]);
$get_item_group_code_by_id = $query_get_code_by_id->row_array();
$sql_generate_code = "select fn_numbering_item(?) as code";
$query_generate_code = $this->db_inventory->query($sql_generate_code,
[
$get_item_group_code_by_id['code']
]);
if(!$query_generate_code){
$this->db->trans_rollback();
$this->sys_error_db("item call sp");
exit;
}
$sql_get_item_code = "SELECT ItemGroupCode
FROM itemgroup
WHERE ItemGroupID = ? ";
$query_get_code_by_id = $this->db_inventory->query($sql_get_item_code,[
$item_group_id
]);
if (!$query_get_code_by_id) {
$this->db->trans_rollback();
$this->sys_error_db("itemgroup select");
exit;
}
// $get_item_group_code_by_id = $query_get_code_by_id->row_array();
$get_item_code = $query_generate_code->row_array();
$item_sku_new = $get_item_code['code'];
// $item_sku_new = $get_item_group_code_by_id['ItemGroupCode']."".$get_item_code['code'];
$sql_get_item_by_id = "SELECT ItemItemGroupID, ItemImage, ItemSKU,
ItemImageThumb
FROM item
where ItemIsActive = 'Y'
AND ItemID = ?";
$query_item_by_id = $this->db_inventory->query($sql_get_item_by_id, [$id]);
if (!$query_item_by_id) {
$this->db->trans_rollback();
$this->sys_error_db("item by id select");
exit;
}
$get_item_by_id = $query_item_by_id->row_array();
if($prm['item_group_id'] == $get_item_by_id['ItemItemGroupID']){
$item_sku = $prm['item_sku'];
// echo "1";
// echo "1";
}
else{
$item_sku = $item_sku_new;
// echo "2";
}
// echo "2";
// ItemItemGroupID, ItemItemUnitID, ItemName
// ganti nama file dan insert log gambar lama
$gambar_lama = $get_item_by_id['ItemImage'];
$gambar_thumb = $get_item_by_id['ItemImageThumb'];
if($prm['image']){
// echo "masuk";
$home_dir = "/home/one/project/one";
$target_dir = $home_dir . $gambar_lama;
$resource = "/home/one/project/one/one-media/one-inventory/item/";
if (file_exists($target_dir)) {
// rename image and thumbnail
$location_image = $home_dir.$gambar_lama;
$location_thumb = $home_dir.$gambar_thumb;
$rename_image = $resource.$get_item_by_id['ItemSKU']."_".date('d-m-Y H:i:s').".jpg";
$rename_thumb = $resource.$get_item_by_id['ItemSKU']."_thumb"."_".date('d-m-Y H:i:s').".jpg";
$image_log = "/one-media/one-inventory/item/".$get_item_by_id['ItemSKU']."_".date('d-m-Y H:i:s').".jpg";
$image_thumbnail_log = "/one-media/one-inventory/item/".$get_item_by_id['ItemSKU']."_thumb"."_".date('d-m-Y H:i:s').".jpg";
if(rename($location_image, $rename_image)){
if(rename($location_thumb, $rename_thumb)){
// print('sukses');
// $flag ++;
// json before
$sql_json_before = "SELECT item.*, '' as satuan
FROM item
WHERE ItemIsActive = 'Y'
AND ItemID = ?";
$qry_json_before = $this->db_inventory->query(
$sql_json_before,
[
$id
]);
if (!$qry_json_before) {
$this->db_inventory->trans_rollback();
$this->sys_error_db("item select json before");
exit;
}
$row_before = $qry_json_before->row_array();
$sql = "SELECT *
FROM `itemunitmap`
WHERE
ItemUnitMapItemID = ? AND
ItemUnitMapIsActive = 'Y'";
$qry = $this->db_inventory->query($sql, array($id));
if($qry){
$row_before['satuan'] = $qry->result_array();
}else{
$this->db_inventory->trans_rollback();
$this->sys_error_db("select itemunitmap error", $this->db_inventory->last_query());
exit;
}
$data_log_before = $row_before;
$json_before_log = json_encode($data_log_before);
$this->db_inventory->trans_start();
$this->db_inventory->trans_strict(FALSE);
// image log
if($prm['image']){
// update image log
$code = "PHOTO.PATIENT.EDIT";
$save_log = $this->db_inventory_log->set('ItemImageCode', $code)
->set('ItemImageSKU', $get_item_by_id['ItemSKU'])
->set('ItemImageDate',date('Y-m-d H:i:s'))
->set('ItemImageCreated',date('Y-m-d H:i:s'))
->set('ItemImageUserID',$userid)
->set('ItemImageUrl', $image_log)
->insert('itemimage_log');
if(!$save_log){
return false;
}
// update
// print($json_before_log);
$upload_image = $this->upload_edit($prm['image'],
$item_sku,
$userid);
if(!$upload_image){
$this->db_inventory->trans_rollback();
$this->sys_error_db("gagal simpan image");
exit;
}
// query update with new image
$sql_update_new_image = "UPDATE item
SET
ItemItemGroupID = ?,
ItemItemCategoryID = ?,
ItemItemManufactureID = ?,
ItemItemBrandID = ?,
ItemSKU = ?,
ItemName = ?,
ItemBarcode = ?,
ItemLastUpdated = now(),
ItemUserID = ?
WHERE ItemID = ?";
$query_update_new = $this->db_inventory->query($sql_update_new_image,
[
$item_group_id,
$item_category_id,
$item_manufacture_id,
$item_brand_id,
$item_sku,
$item_name,
$item_barcode,
$userid,
$id
]);
// echo $this->db_inventory->last_query();
if (!$query_update_new) {
$this->db_inventory->trans_rollback();
$this->sys_error_db("item update data baru");
exit;
}
// update itemunitmap
$sql = "UPDATE itemunitmap
SET ItemUnitMapIsActive = 'N'
WHERE ItemUnitMapItemID = ?";
$qry = $this->db_inventory->query($sql, array(
$id
));
if(!$qry){
$this->db_inventory->trans_rollback();
$this->sys_error_db("update N satuan error", $this->db_inventory->last_query());
exit;
}
if(count($prm['satuan']) > 0)
{
foreach($prm['satuan'] as $key => $value)
{
$ItemUnitMapItemUnitID = trim($value['item_unit_id']);
// $ItemUnitMapIsPurchase = trim($value['detail_tipe_itemunit']);
$itemunitpurchase = "";
$itemunitbase = "";
$item_unit_min = trim($value['item_unit_min']);
if($value['detail_tipe_itemunit'] == "purchase"){
$itemunitpurchase = 'Y';
$itemunitbase = 'N';
}else{
if($value['detail_tipe_itemunit'] == "base"){
$itemunitpurchase = 'N';
$itemunitbase = 'Y';
}
}
if(intval($value['id']) > 0){
// update
$sql = "UPDATE itemunitmap SET
ItemUnitMapItemUnitID = ?,
ItemUnitMapIsPurchase = ?,
ItemUnitMapIsBase = ?,
ItemUnitMapIsActive = 'Y',
ItemUnitMapUserID = ?,
ItemUnitMapMin = ?,
ItemUnitMapLastUpdated = NOW()
WHERE ItemUnitMapID = ?
";
$qry = $this->db_inventory->query($sql,
array(
$ItemUnitMapItemUnitID,
$itemunitpurchase,
$itemunitbase,
$userid,
$item_unit_min,
$value['id']
));
// echo $this->db_inventory->last_query();
if(!$qry){
$this->db->trans_rollback();
$this->sys_error_db("add item unit map error", $this->db_inventory->last_query());
exit;
}
}
else
{
$ItemUnitMapItemUnitID = trim($value['item_unit_id']);
// $ItemUnitMapIsPurchase = trim($value['detail_tipe_itemunit']);
$itemunitpurchase = "";
$itemunitbase = "";
$item_unit_min = trim($value['item_unit_min']);
if($value['detail_tipe_itemunit'] == "purchase"){
$itemunitpurchase = 'Y';
$itemunitbase = 'N';
}else{
if($value['detail_tipe_itemunit'] == "base"){
$itemunitpurchase = 'N';
$itemunitbase = 'Y';
}
}
// insert itemunitmap
$sql = "INSERT INTO itemunitmap(
ItemUnitMapItemID,
ItemUnitMapItemUnitID,
ItemUnitMapIsPurchase,
ItemUnitMapIsBase,
ItemUnitMapMin,
ItemUnitMapIsActive,
ItemUnitMapUserID,
ItemUnitMapCreated,
ItemUnitMapLastUpdated
)
VALUES(
?,?,?,?,?,?,?,NOW(),NOW()
)";
$qry = $this->db_inventory->query($sql,
array(
$prm['item_id'],
$ItemUnitMapItemUnitID,
$itemunitpurchase,
$itemunitbase,
$item_unit_min,
'Y',
$userid
));
// echo $this->db_inventory->last_query();
if(!$qry){
$this->db->trans_rollback();
$this->sys_error_db("add item unit map error", $this->db_inventory->last_query());
exit;
}
}
}
}
$this->db_inventory->trans_complete();
// json after
$sql_json_after = "SELECT item.*, '' as satuan
FROM item
WHERE ItemIsActive = 'Y'
AND ItemID = ?";
$qry_json_after = $this->db_inventory->query($sql_json_after,
[
$id
]);
if (!$qry_json_after) {
$this->db_inventory->trans_rollback();
$this->sys_error_db("item select json after");
exit;
}
$row_after = $qry_json_after->row_array();
$sql = "SELECT *
FROM `itemunitmap`
WHERE
ItemUnitMapItemID = ? AND
ItemUnitMapIsActive = 'Y'";
$qry = $this->db_inventory->query($sql, array($id));
if($qry){
$row_after['satuan'] = $qry->result_array();
}else{
$this->db_inventory->trans_rollback();
$this->sys_error_db("select itemunitmap error", $this->db_inventory->last_query());
exit;
}
$data_log_after = $row_after;
// $data_after_by_id = $qry_json_after->row();
$json_after_log = json_encode($data_log_after);
$sql_insert_log = "INSERT INTO item_log(
ItemLogItemID,
ItemLogStatus,
ItemLogJSONBefore,
ItemLogJSONAfter,
ItemLogUserID,
ItemLogCreated
) VALUES (
?,
'EDIT',
?,
?,
?,
now()
)";
$qry_insert_log = $this->db_inventory_log->query($sql_insert_log,
[
$id,
$json_before_log,
$json_after_log,
$userid
]);
if (!$qry_insert_log) {
$this->db_inventory_log->trans_rollback();
$this->sys_error_db("item edit log");
exit;
}
}
// // sukses
$this->db->trans_commit();
$result = array(
"total" => 1,
"records" => array("xid" => 0)
);
$this->sys_ok($result);
}
} else {
$this->db_inventory->trans_rollback();
$this->sys_error_db("image and thumbnail rename failed");
exit;
}
}
}
// tanpa gambar
else
{
// tanpa gambar
// echo "1";
// json before
$sql_json_before = "SELECT item.*, '' as satuan
FROM item
WHERE ItemIsActive = 'Y'
AND ItemID = ?";
$qry_json_before = $this->db_inventory->query(
$sql_json_before,
[
$id
]);
if (!$qry_json_before) {
$this->db->trans_rollback();
$this->sys_error_db("item select json before");
exit;
}
$row_before = $qry_json_before->row_array();
$sql = "SELECT *
FROM `itemunitmap`
WHERE
ItemUnitMapItemID = ? AND
ItemUnitMapIsActive = 'Y'";
$qry = $this->db_inventory->query($sql, array($id));
if($qry){
$row_before['satuan'] = $qry->result_array();
}else{
$this->db_inventory->trans_rollback();
$this->sys_error_db("select itemunitmap error", $this->db_inventory->last_query());
exit;
}
$data_log_before = $row_before;
// $data_before_by_id = $qry_json_before->row();
$json_before_log = json_encode($data_log_before);
$this->db_inventory->trans_start();
$this->db_inventory->trans_strict(FALSE);
// query update with new image
$sql_update_new_image = "UPDATE item
SET
ItemItemGroupID = ?,
ItemItemCategoryID = ?,
ItemItemManufactureID = ?,
ItemItemBrandID = ?,
ItemSKU = ?,
ItemName = ?,
ItemBarcode = ?,
ItemLastUpdated = now(),
ItemUserID = ?
WHERE ItemID = ?";
$query_update_new = $this->db_inventory->query($sql_update_new_image,
[
$item_group_id,
$item_category_id,
$item_manufacture_id,
$item_brand_id,
$item_sku,
$item_name,
$item_barcode,
$userid,
$id
]);
// echo $this->db_inventory->last_query();
if (!$query_update_new) {
$this->db->trans_rollback();
$this->sys_error_db("item update data baru");
exit;
}
// update itemunitmap
$sql = "UPDATE itemunitmap
SET ItemUnitMapIsActive = 'N'
WHERE ItemUnitMapItemID = ?";
$qry = $this->db_inventory->query($sql, array(
$id
));
if(!$qry){
$this->db->trans_rollback();
$this->sys_error_db("update N satuan error", $this->db_inventory->last_query());
exit;
}
if(count($prm['satuan']) > 0)
{
foreach($prm['satuan'] as $key => $value)
{
$ItemUnitMapItemUnitID = trim($value['item_unit_id']);
// $ItemUnitMapIsPurchase = trim($value['detail_tipe_itemunit']);
$itemunitpurchase = "";
$itemunitbase = "";
$item_unit_min = trim($value['item_unit_min']);
if($value['detail_tipe_itemunit'] == "purchase"){
$itemunitpurchase = 'Y';
$itemunitbase = 'N';
}else{
if($value['detail_tipe_itemunit'] == "base"){
$itemunitpurchase = 'N';
$itemunitbase = 'Y';
}
}
if(intval($value['id']) > 0){
// update
$sql = "UPDATE itemunitmap SET
ItemUnitMapItemUnitID = ?,
ItemUnitMapIsPurchase = ?,
ItemUnitMapIsBase = ?,
ItemUnitMapIsActive = 'Y',
ItemUnitMapUserID = ?,
ItemUnitMapMin = ?,
ItemUnitMapLastUpdated = NOW()
WHERE ItemUnitMapID = ?
";
$qry = $this->db_inventory->query($sql,
array(
$ItemUnitMapItemUnitID,
$itemunitpurchase,
$itemunitbase,
$userid,
$item_unit_min,
$value['id']
));
// echo $this->db_inventory->last_query();
if(!$qry){
$this->db_inventory->trans_rollback();
$this->sys_error_db("add item unit map error", $this->db_inventory->last_query());
exit;
}
}
else
{
$ItemUnitMapItemUnitID = trim($value['item_unit_id']);
// $ItemUnitMapIsPurchase = trim($value['detail_tipe_itemunit']);
$itemunitpurchase = "";
$itemunitbase = "";
$item_unit_min = trim($value['item_unit_min']);
if($value['detail_tipe_itemunit'] == "purchase"){
$itemunitpurchase = 'Y';
$itemunitbase = 'N';
}else{
if($value['detail_tipe_itemunit'] == "base"){
$itemunitpurchase = 'N';
$itemunitbase = 'Y';
}
}
// insert itemunitmap
$sql = "INSERT INTO itemunitmap(
ItemUnitMapItemID,
ItemUnitMapItemUnitID,
ItemUnitMapIsPurchase,
ItemUnitMapIsBase,
ItemUnitMapMin,
ItemUnitMapIsActive,
ItemUnitMapUserID,
ItemUnitMapCreated,
ItemUnitMapLastUpdated
)
VALUES(
?,?,?,?,?,?,?,NOW(),NOW()
)";
$qry = $this->db_inventory->query($sql,
array(
$prm['item_id'],
$ItemUnitMapItemUnitID,
$itemunitpurchase,
$itemunitbase,
$item_unit_min,
'Y',
$userid
));
// echo $this->db_inventory->last_query();
if(!$qry){
$this->db_inventory->trans_rollback();
$this->sys_error_db("add item unit map error", $this->db_inventory->last_query());
exit;
}
}
}
}
$this->db_inventory->trans_complete();
// json after
$sql_json_after = "SELECT item.*, '' as satuan
FROM item
WHERE ItemIsActive = 'Y'
AND ItemID = ?";
$qry_json_after = $this->db_inventory->query($sql_json_after,
[
$id
]);
if (!$qry_json_after) {
$this->db->trans_rollback();
$this->sys_error_db("item select json after");
exit;
}
$row_after = $qry_json_after->row_array();
$sql = "SELECT *
FROM `itemunitmap`
WHERE
ItemUnitMapItemID = ? AND
ItemUnitMapIsActive = 'Y'";
$qry = $this->db_inventory->query($sql, array($id));
if($qry){
$row_after['satuan'] = $qry->result_array();
}else{
$this->db->trans_rollback();
$this->sys_error_db("select itemunitmap error", $this->db_inventory->last_query());
exit;
}
$data_log_after = $row_after;
// $data_after_by_id = $qry_json_after->row();
$json_after_log = json_encode($data_log_after);
$sql_insert_log = "INSERT INTO item_log(
ItemLogItemID,
ItemLogStatus,
ItemLogJSONBefore,
ItemLogJSONAfter,
ItemLogUserID,
ItemLogCreated
) VALUES (
?,
'EDIT',
?,
?,
?,
now()
)";
$qry_insert_log = $this->db_inventory_log->query($sql_insert_log,
[
$id,
$json_before_log,
$json_after_log,
$userid
]);
if (!$qry_insert_log) {
$this->db_inventory_log->trans_rollback();
$this->sys_error_db("item edit log");
exit;
}
// // sukses
$this->db->trans_commit();
$result = array(
"total" => 1,
"records" => array("xid" => 0)
);
$this->sys_ok($result);
}
// else
// {
// $errors = array();
// if($get_count['exist'] != 0){
// array_push($errors,array(
// 'field'=>'name',
// 'msg'=>'Nama sudah ada'
// ));
// }
// if($flag == 0){
// array_push($errors,array(
// 'field'=>'name',
// 'msg'=>'Nama sudah ada'
// ));
// }
// $result = array (
// "total" => -1,
// "errors" => $errors,
// "records" => 0);
// $this->sys_ok($result);
// }
}
} catch(Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
function delete()
{
try {
//# cek token valid
if (! $this->isLogin) {
$this->sys_error("Invalid Token");
exit;
}
//begin transaction
$this->db->trans_begin();
//# ambil parameter input
$prm = $this->sys_input;
$id = "";
if (isset($prm['id'])) {
$id = intval($prm["id"]);
}
if ($id == "" || !$id) {
$error = array(
"message" => "id is mandatory",
);
$this->sys_error_db($error);
exit;
}
$userid = $this->sys_user['M_UserID'];
// $userid = 1;
$this->db_inventory->trans_start();
$this->db_inventory->trans_strict(FALSE);
$sql_delete = "UPDATE item
SET ItemIsActive = 'N',
ItemLastUpdated = now(),
ItemUserID = ?
WHERE ItemID = ?";
$query_delete = $this->db_inventory->query($sql_delete,[
$userid, $id
]);
if(!$query_delete){
$this->db->trans_rollback();
$this->sys_error_db("item delete");
exit;
}else{
$sql = "UPDATE itemunitmap
SET ItemUnitMapIsActive = 'N',
ItemUnitMapLastUpdated = now(),
ItemUnitMapUserID = ?
WHERE
ItemUnitMapItemID = ?
";
$qry = $this->db_inventory->query($sql,
[
$userid,$id
]);
if(!$qry){
$this->db_inventory->trans_rollback();
$this->sys_error_db("delete itemunitmap error", $this->db_inventory->last_query());
exit;
}
}
$this->db_inventory->trans_complete();
// var_dump($this->db->affected_rows());
// print_r($insert_id);
$sql = "SELECT item.*, '' as satuan
FROM item
WHERE ItemID = ? ";
$qry = $this->db_inventory->query($sql, array($id));
//echo $this->db_inventory->last_query();
if(!$qry){
$this->sys_error_db("select item error", $this->db_inventory->last_query());
exit;
}
$row_after = $qry->row_array();
$sql = "SELECT *
FROM `itemunitmap`
WHERE
ItemUnitMapItemID = ? AND
ItemUnitMapIsActive = 'N'";
$qry = $this->db_inventory->query($sql, array($id));
if($qry){
$row_after['satuan'] = $qry->result_array();
}else{
$this->db_inventory->trans_rollback();
$this->sys_error_db("select company address error", $this->db_inventory->last_query());
exit;
}
$data_log_after = $row_after;
// print_r($json_after_log);
$sql_insert_log = "INSERT INTO item_log(
ItemLogItemID,
ItemLogStatus,
ItemLogJSONBefore,
ItemLogJSONAfter,
ItemLogUserID,
ItemLogCreated
) VALUES (
?,
'DELETE',
null,
?,
?,
now()
)";
$qry_insert_log = $this->db_inventory_log->query($sql_insert_log,
[
$id,
json_encode($data_log_after),
$userid
]);
if (!$qry_insert_log) {
$this->db->trans_rollback();
$this->sys_error_db("item delete log");
exit;
}
// sukses
$this->db->trans_commit();
$result = array ("total" => 1, "records" => array("xid" => 0));
$this->sys_ok($result);
} catch(Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
public function upload($data_image,$sku,$userid, $status)
{
$inp = $this->sys_input;
$home_dir = "/home/one/project/one/";
$target_dir = $home_dir . "one-media/one-inventory/item/";
if (!file_exists($target_dir)) {
mkdir($target_dir, 0755, true);
}
$target_path = $target_dir . $sku . ".jpg";
$this->base64_to_jpeg($data_image, $target_path);
// CROP Image
$im = new ImageManipulator($target_path);
$w = $im->getWidth();
$h = $im->getHeight();
$mw = ceil(3 * $h / 4);
if ($w <= $mw)
{
$x1 = 0;
$y1 = 0;
$x2 = $w;
$y2 = $h;
}
else
{
$x1 = floor(($w - $mw) / 2);
$y1 = 0;
$x2 = ceil($w - (($w - $mw) / 2));
$y2 = $h;
}
$im->crop($x1, $y1, $x2, $y2); // takes care of out of boundary conditions automatically
$im->save($target_path);
$x = $this->generateThumbnail($target_path, 75, 100);
// Save to DB
$save_image = $this->db_inventory
->set("ItemImage", "/" . str_replace($home_dir, "", $target_path))
->set("ItemImageThumb", "/" . str_replace($home_dir, "", $x))
->where('ItemSKU', $sku)
->update('item');
if(!$save_image){
return false;
}
// LOGGING
// $code = $y ? "PHOTO.PATIENT.EDIT" : "PHOTO.PATIENT.ADD";
($status == 'ADD') ? $code = "PHOTO.PATIENT.ADD" : $code = "PHOTO.PATIENT.EDIT";
$inventory_log = $this->load->database('inventory_log', true);
$save_log = $inventory_log->set('ItemImageCode', $code)
->set('ItemImageSKU', $sku)
->set('ItemImageDate',date('Y-m-d H:i:s'))
->set('ItemImageCreated',date('Y-m-d H:i:s'))
->set('ItemImageUserID',$userid)
->set('ItemImageUrl', $y ? $y : "/" . str_replace($home_dir, "", $target_path))
->insert('itemimage_log');
if(!$save_log){
return false;
}
return true;
}
public function upload_edit($data_image, $sku, $userid)
{
$inp = $this->sys_input;
$home_dir = "/home/one/project/one/";
$target_dir = $home_dir . "one-media/one-inventory/item/";
if (!file_exists($target_dir)) {
mkdir($target_dir, 0755, true);
}
$target_path = $target_dir . $sku . ".jpg";
$this->base64_to_jpeg($data_image, $target_path);
// CROP Image
$im = new ImageManipulator($target_path);
$w = $im->getWidth();
$h = $im->getHeight();
$mw = ceil(3 * $h / 4);
if ($w <= $mw)
{
$x1 = 0;
$y1 = 0;
$x2 = $w;
$y2 = $h;
}
else
{
$x1 = floor(($w - $mw) / 2);
$y1 = 0;
$x2 = ceil($w - (($w - $mw) / 2));
$y2 = $h;
}
$im->crop($x1, $y1, $x2, $y2); // takes care of out of boundary conditions automatically
$im->save($target_path);
$x = $this->generateThumbnail($target_path, 75, 100);
// ($status == 'ADD') ? $code = "PHOTO.PATIENT.ADD" : $code = "PHOTO.PATIENT.EDIT";
// $inventory_log = $this->load->database('inventory_log', true);
// $save_log = $inventory_log->set('ItemImageCode', $code)
// ->set('ItemImageSKU', $sku)
// ->set('ItemImageDate',date('Y-m-d H:i:s'))
// ->set('ItemImageCreated',date('Y-m-d H:i:s'))
// ->set('ItemImageUserID',$userid)
// ->set('ItemImageUrl', $image_log)
// ->insert('itemimage_log');
// if(!$save_log){
// return false;
// }
return true;
}
function base64_to_jpeg($base64_string, $output_file)
{
// open the output file for writing
//echo $base64_string;
$ifp = fopen( $output_file, 'wb' );
// split the string on commas
// $data[ 0 ] == "data:image/png;base64"
// $data[ 1 ] ==
$data = explode( ',', $base64_string );
//echo $data[ 1 ];
// we could add validation here with ensuring count( $data ) > 1
fwrite( $ifp, base64_decode( $data[ 1 ] ) );
// clean up the file resource
fclose( $ifp );
//echo $output_file;
return $output_file;
}
function generateThumbnail($img, $width, $height, $quality = 90)
{
if (is_file($img)) {
$imagick = new Imagick(realpath($img));
$imagick->setImageFormat('jpeg');
$imagick->setImageCompression(Imagick::COMPRESSION_JPEG);
$imagick->setImageCompressionQuality($quality);
$imagick->thumbnailImage($width, $height, false, false);
$filename_no_ext = reset(explode('.', $img));
if (file_put_contents($filename_no_ext . '_thumb' . '.jpg', $imagick) === false) {
throw new Exception("Could not put contents.");
}
return $filename_no_ext . '_thumb' . '.jpg';
}
else {
throw new Exception("No valid image provided with {$img}.");
}
}
// itemgroup
function searchitemgroup()
{
try {
//# cek token valid
if (! $this->isLogin) {
$this->sys_error("Invalid Token");
exit;
}
//begin transaction
$this->db->trans_begin();
//# ambil parameter input
$prm = $this->sys_input;
// $id = $prm['id'];
$userid = $this->sys_user['M_UserID'];
$search = "";
// $userid = 1;
$number_limit = 10;
if (isset($prm['search'])) {
$search = trim($prm["search"]);
if ($search != "") {
$search = '%' . $prm['search'] . '%';
}else{
$search = '%%';
}
}
$sql_search = "select distinct ItemGroupID, ItemGroupName, ItemGroupCode
from itemgroup
WHERE ItemGroupIsActive = 'Y'
AND (
ItemGroupName like ?
OR ItemGroupCode like ?
)
limit ?
";
$query_search = $this->db_inventory->query($sql_search,[
$search, $search, $number_limit
]);
if(!$query_search){
$this->db->trans_rollback();
$this->sys_error_db("itemgrup select");
exit;
}
$rows = $query_search->result_array();
$sql_filter = "SELECT COUNT(distinct ItemGroupID, ItemGroupName, ItemGroupCode) as total
FROM itemgroup
WHERE ItemGroupIsActive = 'Y'
AND (
ItemGroupName like ?
OR ItemGroupCode like ?
)
limit ?";
$tot_count = 0;
$qry_filter = $this->db_inventory->query($sql_filter, [$search, $search, $number_limit]);
if ($qry_filter) {
$tot_count = $qry_filter->result_array()[0]["total"];
}
else {
$this->sys_error_db("itemgroup count");
exit;
}
// sukses
$this->db->trans_commit();
// $result = array ("total" => 1, "records" => array("xid" => 0));
$result = array(
"total"=> $tot_count,
"total_display"=>sizeof($rows),
"records" => $rows
);
$this->sys_ok($result);
} catch(Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
// itemcategory
function searchitemcategory()
{
try {
//# cek token valid
if (! $this->isLogin) {
$this->sys_error("Invalid Token");
exit;
}
//begin transaction
$this->db->trans_begin();
//# ambil parameter input
$prm = $this->sys_input;
// $id = $prm['id'];
$userid = $this->sys_user['M_UserID'];
$search = "";
// $userid = 1;
$number_limit = 10;
$sql_search = '';
if (isset($prm['search'])) {
$search = trim($prm["search"]);
if ($search != "") {
$search = '%' . $prm['search'] . '%';
}else{
$search = '%%';
}
}
// if (isset($prm['search'])) {
// $search = trim($prm["search"]);
// if ($search != "") {
// $search = trim($prm['search']);
// }else{
// $search = '';
// }
// }
$sql_search = "select distinct ItemCategoryID, ItemCategoryName, ItemCategoryCode
from itemcategory
WHERE ItemCategoryIsActive = 'Y'
AND (
ItemCategoryName like ?
OR ItemCategoryCode like ?
)
limit ?
";
$query_search = $this->db_inventory->query($sql_search,[
$search, $search, $number_limit
]);
if(!$query_search){
$this->db->trans_rollback();
$this->sys_error_db("itemcategory select");
exit;
}
$rows = $query_search->result_array();
$sql_filter = "SELECT COUNT(distinct ItemCategoryID, ItemCategoryName, ItemCategoryCode) as total
FROM itemcategory
WHERE ItemCategoryIsActive = 'Y'
AND (
ItemCategoryName = ?
OR ItemCategoryCode = ?
)
limit ?";
$tot_count = 0;
$qry_filter = $this->db_inventory->query($sql_filter, [$search, $search, $number_limit]);
if ($qry_filter) {
$tot_count = $qry_filter->result_array()[0]["total"];
}
else {
$this->sys_error_db("itemcategory count");
exit;
}
// sukses
$this->db->trans_commit();
// $result = array ("total" => 1, "records" => array("xid" => 0));
$result = array(
"total"=> $tot_count,
"total_display"=>sizeof($rows),
"records" => $rows,
);
$this->sys_ok($result);
} catch(Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
// itemunit
function searchitemunit()
{
try {
//# cek token valid
if (! $this->isLogin) {
$this->sys_error("Invalid Token");
exit;
}
//begin transaction
$this->db->trans_begin();
//# ambil parameter input
$prm = $this->sys_input;
// $id = $prm['id'];
$userid = $this->sys_user['M_UserID'];
$search = "";
// $userid = 1;
$number_limit = 10;
if (isset($prm['search'])) {
$search = trim($prm["search"]);
if ($search != "") {
$search = '%' . $prm['search'] . '%';
}else{
$search = '%%';
}
}
$sql_search = "select distinct ItemUnitID, ItemUnitName, ItemUnitCode
from itemunit
WHERE ItemUnitIsActive = 'Y'
AND (
ItemUnitName like ?
OR ItemUnitCode like ?
)
limit ?
";
$query_search = $this->db_inventory->query($sql_search,[
$search, $search, $number_limit
]);
if(!$query_search){
$this->db->trans_rollback();
$this->sys_error_db("itemunit select");
exit;
}
$rows = $query_search->result_array();
$sql_filter = "SELECT COUNT(distinct ItemUnitID, ItemUnitName, ItemUnitCode) as total
FROM itemunit
WHERE ItemUnitIsActive = 'Y'
AND (
ItemUnitName like ?
OR ItemUnitCode like ?
)
limit ?";
$tot_count = 0;
$qry_filter = $this->db_inventory->query($sql_filter, [$search, $search, $number_limit]);
if ($qry_filter) {
$tot_count = $qry_filter->result_array()[0]["total"];
}
else {
$this->sys_error_db("itemunit count");
exit;
}
// sukses
$this->db->trans_commit();
// $result = array ("total" => 1, "records" => array("xid" => 0));
$result = array(
"total"=> $tot_count,
"total_display"=>sizeof($rows),
"records" => $rows
);
$this->sys_ok($result);
} catch(Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
// itemmanufacture
function searchitemmanufacture()
{
try {
//# cek token valid
if (! $this->isLogin) {
$this->sys_error("Invalid Token");
exit;
}
//begin transaction
$this->db->trans_begin();
//# ambil parameter input
$prm = $this->sys_input;
// $id = $prm['id'];
$userid = $this->sys_user['M_UserID'];
$search = "";
// $userid = 1;
if (isset($prm['search'])) {
$search = trim($prm["search"]);
if ($search != "") {
$search = '%' . $prm['search'] . '%';
}else{
$search = '%%';
}
}
$number_limit = 10;
$sql_search = "select distinct ItemManufactureID, ItemManufactureName, ItemManufactureCode
from itemmanufacture
WHERE ItemManufactureIsActive = 'Y'
AND (
ItemManufactureName like ?
OR ItemManufactureCode like ?
)
limit ?
";
$query_search = $this->db_inventory->query($sql_search,[
$search, $search, $number_limit
]);
if(!$query_search){
$this->db->trans_rollback();
$this->sys_error_db("itemmanufacture select");
exit;
}
$rows = $query_search->result_array();
$sql_filter = "SELECT COUNT(distinct ItemManufactureID, ItemManufactureName, ItemManufactureCode) as total
FROM itemmanufacture
WHERE ItemManufactureIsActive = 'Y'
AND (
ItemManufactureName like ?
OR ItemManufactureCode like ?
)
limit ?";
$tot_count = 0;
$qry_filter = $this->db_inventory->query($sql_filter, [$search, $search, $number_limit]);
if ($qry_filter) {
$tot_count = $qry_filter->result_array()[0]["total"];
}
else {
$this->sys_error_db("itemmanufacture count");
exit;
}
// sukses
$this->db->trans_commit();
// $result = array ("total" => 1, "records" => array("xid" => 0));
$result = array(
"total"=> $tot_count,
"total_display"=>sizeof($rows),
"records" => $rows
);
$this->sys_ok($result);
} catch(Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
// itembrand
function searchitembrand()
{
try {
//# cek token valid
if (! $this->isLogin) {
$this->sys_error("Invalid Token");
exit;
}
//begin transaction
$this->db->trans_begin();
//# ambil parameter input
$prm = $this->sys_input;
// $id = $prm['id'];
$userid = $this->sys_user['M_UserID'];
$search = "";
// $userid = 1;
if (isset($prm['search'])) {
$search = trim($prm["search"]);
if ($search != "") {
$search = '%' . $prm['search'] . '%';
}else{
$search = '%%';
}
}
$number_limit = 10;
$sql_search = "select distinct ItemBrandID, ItemBrandName, ItemBrandCode
from itembrand
WHERE ItemBrandIsActive = 'Y'
AND (
ItemBrandName like ?
OR ItemBrandCode like ?
)
limit ?
";
$query_search = $this->db_inventory->query($sql_search,[
$search, $search, $number_limit
]);
if(!$query_search){
$this->db->trans_rollback();
$this->sys_error_db("itembrand select");
exit;
}
$rows = $query_search->result_array();
$sql_filter = "SELECT COUNT(distinct ItemBrandID, ItemBrandName, ItemBrandCode) as total
from itembrand
WHERE ItemBrandIsActive = 'Y'
AND (
ItemBrandName like ?
OR ItemBrandCode like ?
)
limit ?";
$tot_count = 0;
$qry_filter = $this->db_inventory->query($sql_filter, [$search, $search, $number_limit]);
if ($qry_filter) {
$tot_count = $qry_filter->result_array()[0]["total"];
}
else {
$this->sys_error_db("itemmanufacture count");
exit;
}
// sukses
$this->db->trans_commit();
// $result = array ("total" => 1, "records" => array("xid" => 0));
$result = array(
"total"=> $tot_count,
"total_display"=>sizeof($rows),
"records" => $rows
);
$this->sys_ok($result);
} catch(Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
// itemgroup filter
function searchitemgroupfilter()
{
try {
//# cek token valid
if (! $this->isLogin) {
$this->sys_error("Invalid Token");
exit;
}
//begin transaction
$this->db->trans_begin();
//# ambil parameter input
$prm = $this->sys_input;
// $id = $prm['id'];
$userid = $this->sys_user['M_UserID'];
$search = "";
// $userid = 1;
$number_limit = 10;
if (isset($prm['search'])) {
$search = trim($prm["search"]);
if ($search != "") {
$search = '%' . $prm['search'] . '%';
}else{
$search = '%%';
}
}
$sql_search = "select distinct ItemGroupID, ItemGroupName, ItemGroupCode
from itemgroup
WHERE ItemGroupIsActive = 'Y'
AND (
ItemGroupName like ?
OR ItemGroupCode like ?
)
limit ?
";
$query_search = $this->db_inventory->query($sql_search,[
$search, $search, $number_limit
]);
if(!$query_search){
$this->db->trans_rollback();
$this->sys_error_db("itemgrup select");
exit;
}
$rows = $query_search->result_array();
$sql_filter = "SELECT COUNT(distinct ItemGroupID, ItemGroupName, ItemGroupCode) as total
FROM itemgroup
WHERE ItemGroupIsActive = 'Y'
AND (
ItemGroupName like ?
OR ItemGroupCode like ?
)
limit ?";
$tot_count = 0;
$qry_filter = $this->db_inventory->query($sql_filter, [$search, $search, $number_limit]);
if ($qry_filter) {
$tot_count = $qry_filter->result_array()[0]["total"];
}
else {
$this->sys_error_db("itemgroup count");
exit;
}
// sukses
$this->db->trans_commit();
// $result = array ("total" => 1, "records" => array("xid" => 0));
$result = array(
"total"=> $tot_count,
"total_display"=>sizeof($rows),
"records" => $rows
);
$this->sys_ok($result);
} catch(Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
}
?>