public IdentityWarehouse GetById(int Id)
{
var info = new IdentityWarehouse();
var sqlCmd = @"Warehouse_GetById";
var parameters = new Dictionary <string, object>
{
{ "@Id", Id }
};
try
{
using (var conn = new SqlConnection(_connectionString))
{
using (var reader = MsSqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, sqlCmd, parameters))
{
while (reader.Read())
{
info = ExtractWarehouseData(reader);
}
}
}
}
catch (Exception ex)
{
var strError = "Failed to execute Warehouse_GetById. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(info);
}
public bool Update(IdentityWarehouse identity)
{
//Common syntax
var sqlCmd = @"Warehouse_Update";
//For parameters
var parameters = new Dictionary <string, object>
{
{ "@Id", identity.Id },
//{"@Name", identity.Name},
//{"@Code", identity.Code },
//{"@LastUpdatedBy", identity.LastUpdatedBy},
//{"@Status", identity.Status}
};
try
{
using (var conn = new SqlConnection(_connectionString))
{
MsSqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, sqlCmd, parameters);
}
}
catch (Exception ex)
{
var strError = "Failed to execute Warehouse_Update. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(true);
}
public ActionResult ReflectStockTake(WarehouseActionModel model)
{
var strError = string.Empty;
try
{
var productInfo = new IdentityProduct();
var warehouseInfo = new IdentityWarehouse();
productInfo.Id = model.ProductId;
productInfo.Code = model.ItemCode;
var productStore = GlobalContainer.IocContainer.Resolve <IStoreProduct>();
var productInfoCheck = productStore.GetById(model.ProductId);
if (productInfoCheck == null || (productInfoCheck != null && productInfoCheck.Id <= 0))
{
return(Json(new { success = false, message = ManagerResource.ERROR_PRODUCT_ITEM_NOTFOUND, title = ManagerResource.LB_NOTIFICATION }));
}
else
{
productInfo = productInfoCheck;
}
//if (model.ProductId <= 0)
//{
// if (!string.IsNullOrEmpty(model.ItemCode))
// {
// var productStore = GlobalContainer.IocContainer.Resolve<IStoreProduct>();
// var productInfoCheck = productStore.GetByCode(model.ItemCode);
// if (productInfoCheck == null || (productInfoCheck != null && productInfoCheck.Id <= 0))
// return Json(new { success = false, message = string.Format(ManagerResource.ERROR_PRODUCT_ITEM_CODE_NOTFOUND_FORMAT, model.ItemCode), title = ManagerResource.LB_NOTIFICATION, clientcallback = " ShowMyModalAgain();" });
// else
// productInfo = productInfoCheck;
// }
//}
//productInfo.WarehouseNum = model.WarehouseNum;
warehouseInfo.ProductList.Add(productInfo);
var activityInfo = new IdentityWarehouseActivity();
activityInfo.ActivityType = (int)EnumWarehouseActivityType.ReflectStockTake;
activityInfo.DeviceId = RegisterNewDevice();
activityInfo.StaffId = GetCurrentStaffId();
//Database execute
_mainStore.ReflectStockTake(warehouseInfo, activityInfo);
}
catch (Exception ex)
{
strError = ManagerResource.LB_SYSTEM_BUSY;
logger.Error("Failed to exec ReflectStockTake because: " + ex.ToString());
return(Json(new { success = false, message = strError }));
}
return(Json(new { success = true, message = ManagerResource.LB_GOODS_REFLECT_STOCK_TAKE_SUCCESS, title = ManagerResource.LB_NOTIFICATION, clientcallback = " location.reload()" }));
}
public ActionResult GoodsReceipt(WarehouseActionModel model)
{
var strError = string.Empty;
try
{
var productInfo = new IdentityProduct();
var warehouseInfo = new IdentityWarehouse();
productInfo.Id = model.ProductId;
productInfo.Code = model.ItemCode;
var qty = Utils.ConvertToDouble(model.WarehouseNum);
if (qty <= 0)
{
return(Json(new { success = false, message = ManagerResource.ERROR_QTY_MUST_LARGE_THAN_0, title = ManagerResource.LB_NOTIFICATION, clientcallback = " ShowMyModalAgain();" }));
}
if (model.ProductId <= 0)
{
if (!string.IsNullOrEmpty(model.ItemCode))
{
var productStore = GlobalContainer.IocContainer.Resolve <IStoreProduct>();
var productInfoCheck = productStore.GetByCode(model.ItemCode);
if (productInfoCheck == null || (productInfoCheck != null && productInfoCheck.Id <= 0))
{
return(Json(new { success = false, message = string.Format(ManagerResource.ERROR_PRODUCT_ITEM_CODE_NOTFOUND_FORMAT, model.ItemCode), title = ManagerResource.LB_NOTIFICATION, clientcallback = " ShowMyModalAgain();" }));
}
else
{
productInfo = productInfoCheck;
}
}
}
productInfo.WarehouseNum = qty;
warehouseInfo.ProductList.Add(productInfo);
var activityInfo = new IdentityWarehouseActivity();
activityInfo.ActivityType = (int)EnumWarehouseActivityType.GoodsReceipt;
activityInfo.DeviceId = RegisterNewDevice();
activityInfo.StaffId = GetCurrentStaffId();
//Database execute
_mainStore.GoodsReceipt(warehouseInfo, activityInfo);
}
catch (Exception ex)
{
strError = ManagerResource.LB_SYSTEM_BUSY;
logger.Error("Failed to exec GoodsReceipt because: " + ex.ToString());
return(Json(new { success = false, message = strError }));
}
return(Json(new { success = true, message = ManagerResource.LB_GOODS_RECEIPT_SUCCESS, title = ManagerResource.LB_NOTIFICATION, clientcallback = " location.reload()" }));
}
public ActionResult GetListProductStockOut(ManageWarehouseModel model)
{
int currentPage = 1;
int pageSize = SystemSettings.DefaultPageSize;
if (string.IsNullOrEmpty(model.SearchExec))
{
model.SearchExec = "Y";
if (!ModelState.IsValid)
{
ModelState.Clear();
}
}
currentPage = model.CurrentPage;
if (currentPage == 0)
{
currentPage = 1;
}
if (model.PageSize > 0)
{
pageSize = model.PageSize;
}
if (pageSize <= 0 || pageSize > 100)
{
pageSize = SystemSettings.DefaultPageSize;
}
var filter = new IdentityWarehouse
{
//ProductCode = !string.IsNullOrEmpty(model.Code) ? model.Code.Trim() : null,
//Keyword = !string.IsNullOrEmpty(model.Name) ? model.Name.Trim() : null,
};
try
{
model.Units = CommonHelpers.GetListUnit();
var warehouseStore = GlobalContainer.IocContainer.Resolve <IStoreWarehouse>();
model.SearchResults = warehouseStore.GetProductStockOutByPage(filter, currentPage, SystemSettings.DefaultPageSize);
if (model.SearchResults != null && model.SearchResults.Count > 0)
{
model.TotalCount = model.SearchResults[0].TotalCount;
model.CurrentPage = currentPage;
model.PageSize = pageSize;
}
}
catch (Exception ex)
{
this.AddNotification(NotifSettings.Error_SystemBusy, NotificationType.ERROR);
logger.Error("Failed to get GetProductStockOutByPage because: " + ex.ToString());
}
return(PartialView("Partials/_ProductStockOutList", model));
}
public List <IdentityWarehouse> GetByPage(IdentityWarehouse filter, int currentPage, int pageSize)
{
//Common syntax
var sqlCmd = @"Warehouse_GetByPage";
List <IdentityWarehouse> listData = null;
//For paging
int offset = (currentPage - 1) * pageSize;
//For parameters
var parameters = new Dictionary <string, object>
{
{ "@ProductCode", filter.ProductCode },
{ "@Keyword", filter.Keyword },
{ "@IsStockOut", filter.IsStockOut },
{ "@IsStockTakeQTY", filter.IsStockTakeQTY },
{ "@Offset", offset },
{ "@PageSize", pageSize },
};
try
{
using (var conn = new SqlConnection(_connectionString))
{
using (var reader = MsSqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, sqlCmd, parameters))
{
listData = ParsingListWarehouseFromReader(reader);
if (listData.Count > 0)
{
if (reader.NextResult())
{
while (reader.Read())
{
var productInfo = RpsProduct.ExtractProductData(reader);
foreach (var item in listData)
{
if (productInfo.Id == item.ProductId)
{
item.ProductInfo = productInfo;
}
}
}
}
}
}
}
}
catch (Exception ex)
{
var strError = "Failed to execute Warehouse_GetByPage. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(listData);
}
public ActionResult Index(ManageWarehouseModel model)
{
int currentPage = 1;
int pageSize = SystemSettings.DefaultPageSize;
if (string.IsNullOrEmpty(model.SearchExec))
{
model.SearchExec = "Y";
if (!ModelState.IsValid)
{
ModelState.Clear();
}
}
if (Request["Page"] != null)
{
currentPage = Utils.ConvertToInt32(Request["Page"], 1);
}
var filter = new IdentityWarehouse
{
ProductCode = !string.IsNullOrEmpty(model.Code) ? model.Code.Trim() : null,
Keyword = !string.IsNullOrEmpty(model.Name) ? model.Name.Trim() : null,
IsStockTakeQTY = model.IsConfirmStockTakeQTY == null ? -1 : (int)model.IsConfirmStockTakeQTY,
IsStockOut = model.IsStockOut == null ? -1 : (int)model.IsStockOut
};
try
{
model.Units = CommonHelpers.GetListUnit();
model.SearchResults = _mainStore.GetByPage(filter, currentPage, SystemSettings.DefaultPageSize);
if (model.SearchResults != null && model.SearchResults.Count > 0)
{
model.TotalCount = model.SearchResults[0].TotalCount;
model.CurrentPage = currentPage;
model.PageSize = pageSize;
}
}
catch (Exception ex)
{
this.AddNotification(NotifSettings.Error_SystemBusy, NotificationType.ERROR);
logger.Error("Failed to get data because: " + ex.ToString());
return(View(model));
}
return(View(model));
}
private IdentityWarehouse ExtractWarehouseData(IDataReader reader)
{
var record = new IdentityWarehouse();
//Seperate properties
record.Id = Utils.ConvertToInt32(reader["Id"]);
record.ProductId = Utils.ConvertToInt32(reader["ProductId"]);
record.WarehouseNum = Utils.ConvertToDouble(reader["WarehouseNum"]);
record.StockTakeQTY = Utils.ConvertToDouble(reader["StockTakeQTY"]);
//record.CreatedBy = reader["CreatedBy"].ToString();
//record.CreatedDate = reader["CreatedDate"] == DBNull.Value ? null : (DateTime?)reader["CreatedDate"];
//record.LastUpdated = reader["LastUpdated"] == DBNull.Value ? null : (DateTime?)reader["LastUpdated"];
//record.LastUpdatedBy = reader["LastUpdatedBy"].ToString();
//record.Status = Utils.ConvertToInt32(reader["Status"]);
return(record);
}
public bool Update(IdentityWarehouse identity)
{
return(myRepository.Update(identity));
}
public double ReflectStockTake(IdentityWarehouse identity, IdentityWarehouseActivity activity)
{
return(myRepository.ReflectStockTake(identity, activity));
}
public int GoodsIssue(IdentityWarehouse identity, IdentityWarehouseActivity activity)
{
return(myRepository.GoodsIssue(identity, activity));
}
public List <IdentityWarehouse> GetProductStockOutByPage(IdentityWarehouse filter, int currentPage, int pageSize)
{
return(myRepository.GetProductStockOutByPage(filter, currentPage, pageSize));
}
public double ReflectStockTake(IdentityWarehouse identity, IdentityWarehouseActivity activity)
{
//Common syntax
var sqlReflectCmd = @"Warehouse_ReflectStockTake_Item";
double stockQTY = 0;
//For parameters
var parameters = new Dictionary <string, object>
{
//{"@Name", identity.Name},
//{"@Code", identity.Code },
//{"@CreatedBy", identity.CreatedBy},
//{"@Status", identity.Status}
};
StringBuilder activityInsertCmd = new StringBuilder();
var cmdReflectStockCreate = string.Empty;
StringBuilder reflectDetailCmd = new StringBuilder();
try
{
using (var conn = new SqlConnection(_connectionString))
{
if (identity.ProductList != null && identity.ProductList.Count > 0)
{
foreach (var item in identity.ProductList)
{
var itemParms = new Dictionary <string, object>
{
{ "@ProductId", item.Id }
};
var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlReflectCmd, itemParms);
stockQTY = Utils.ConvertToDouble(returnObj);
if (stockQTY >= 0)
{
cmdReflectStockCreate = string.Format("INSERT INTO tbl_reflectstocktake(CreatedBy) VALUES({0}); SELECT SCOPE_IDENTITY();", activity.StaffId);
var activityCmd = string.Format(@"INSERT INTO tbl_warehouse_activity (ActivityType,ProductId,NumberOfProducts,StaffId,DeviceId,ObjectId)
VALUES ({0},{1},{2},{3},{4},{5}); ", activity.ActivityType, item.Id, stockQTY, activity.StaffId, activity.DeviceId, "##NEWID##");
var productDetailCmd = string.Format(@"INSERT INTO tbl_reflectstocktake_detail (ReflectStockTakeId,ProductId,NumberOfProducts)
VALUES ({0},{1},{2});", "##NEWID##", item.Id, stockQTY);
activityInsertCmd.Append(activityCmd);
reflectDetailCmd.Append(productDetailCmd);
}
}
var reflectStockId = 0;
if (!string.IsNullOrEmpty(cmdReflectStockCreate))
{
var returnObjReflectStock = MsSqlHelper.ExecuteScalar(conn, CommandType.Text, cmdReflectStockCreate, null);
reflectStockId = Utils.ConvertToInt32(returnObjReflectStock);
if (reflectStockId > 0)
{
reflectDetailCmd.Replace("##NEWID##", reflectStockId.ToString());
var reflectStockDetailCmdCmdExec = reflectDetailCmd.ToString();
if (!string.IsNullOrEmpty(reflectStockDetailCmdCmdExec))
{
//Insert details
MsSqlHelper.ExecuteScalar(conn, CommandType.Text, reflectStockDetailCmdCmdExec, null);
}
}
}
activityInsertCmd.Replace("##NEWID##", reflectStockId.ToString());
var activityCmdExec = activityInsertCmd.ToString();
if (!string.IsNullOrEmpty(activityCmdExec))
{
//Insert logs
MsSqlHelper.ExecuteScalar(conn, CommandType.Text, activityCmdExec, null);
}
}
}
}
catch (Exception ex)
{
var strError = "Failed to execute Warehouse_GoodsIssue_Item. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(stockQTY);
}
public int GoodsReceipt(IdentityWarehouse identity, IdentityWarehouseActivity activity)
{
//Common syntax
var sqlGoodsReceiptCmd = @"Warehouse_GoodsReceipt_Item";
var newId = 0;
//For parameters
var parameters = new Dictionary <string, object>
{
//{"@Name", identity.Name},
//{"@Code", identity.Code },
//{"@CreatedBy", identity.CreatedBy},
//{"@Status", identity.Status}
};
StringBuilder activityInsertCmd = new StringBuilder();
var cmdGoodsReceiptCreate = string.Empty;
StringBuilder goodsReceiptDetailCmd = new StringBuilder();
try
{
using (var conn = new SqlConnection(_connectionString))
{
if (identity.ProductList != null && identity.ProductList.Count > 0)
{
foreach (var item in identity.ProductList)
{
var itemParms = new Dictionary <string, object>
{
{ "@ProductId", item.Id },
{ "@WarehouseNum", item.WarehouseNum }
};
var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlGoodsReceiptCmd, itemParms);
newId = Utils.ConvertToInt32(returnObj);
if (newId >= 0)
{
cmdGoodsReceiptCreate = string.Format("INSERT INTO tbl_goods_receipt(LotNo,CreatedBy) VALUES('{0}',{1}); SELECT SCOPE_IDENTITY();", string.Empty, activity.StaffId);
var activityCmd = string.Format(@"INSERT INTO tbl_warehouse_activity (ActivityType,ProductId,NumberOfProducts,StaffId,DeviceId,ObjectId)
VALUES ({0},{1},{2},{3},{4},{5}); ", activity.ActivityType, item.Id, item.WarehouseNum, activity.StaffId, activity.DeviceId, "##NEWID##");
var productDetailCmd = string.Format(@"INSERT INTO tbl_goods_receipt_detail (GoodsReceiptId,ProductId,NumberOfProducts)
VALUES ({0},{1},{2});", "##NEWID##", item.Id, item.WarehouseNum);
activityInsertCmd.Append(activityCmd);
goodsReceiptDetailCmd.Append(productDetailCmd);
}
}
var goodsReceiptId = 0;
if (!string.IsNullOrEmpty(cmdGoodsReceiptCreate))
{
var returnObjGoodsReceipt = MsSqlHelper.ExecuteScalar(conn, CommandType.Text, cmdGoodsReceiptCreate, null);
goodsReceiptId = Utils.ConvertToInt32(returnObjGoodsReceipt);
if (goodsReceiptId > 0)
{
goodsReceiptDetailCmd.Replace("##NEWID##", goodsReceiptId.ToString());
var goodsReceiptDetailCmdCmdExec = goodsReceiptDetailCmd.ToString();
if (!string.IsNullOrEmpty(goodsReceiptDetailCmdCmdExec))
{
//Insert details
MsSqlHelper.ExecuteScalar(conn, CommandType.Text, goodsReceiptDetailCmdCmdExec, null);
}
}
}
activityInsertCmd.Replace("##NEWID##", goodsReceiptId.ToString());
var activityCmdExec = activityInsertCmd.ToString();
if (!string.IsNullOrEmpty(activityCmdExec))
{
//Insert logs
MsSqlHelper.ExecuteScalar(conn, CommandType.Text, activityCmdExec, null);
}
}
}
}
catch (Exception ex)
{
var strError = "Failed to execute Warehouse_GoodsReceipt_Item. Error: " + ex.Message;
throw new CustomSQLException(strError);
}
return(newId);
}
