public Task <ItemsInventoryReportResultVM> getItemsInventoryBalanceResult(ItemsInventoryReportSearchVM searchObject) { return(Task.Run(() => { try { var itemsInventory = new ItemsInventoryReportResultVM(); string Options = "", Select = "", ToSelect = ""; if (searchObject.StoreId != 0) { Options += " AND COM_STORE_ID = " + searchObject.StoreId; } if (searchObject.ShowZeroBalancesOnly) { Options += " AND QTY = 0 "; } else if (searchObject.ShowZeroBalances && searchObject.ShowNegativeBalancesOnly) { Options += " AND QTY <= 0 "; } else if (searchObject.ShowNegativeBalancesOnly) { Options += " AND QTY < 0 "; } else if (!searchObject.ShowZeroBalances) { Options += " AND QTY <> 0 "; } if (searchObject.GroupId != null) { Options += " AND GROUP_ID = " + searchObject.GroupId; } if (searchObject.CostCenterId != 0) { Options += " AND COST_CENTER_ID = " + searchObject.CostCenterId; } if (searchObject.ShowExpired) { Select += " , EXPIRED_DATE "; ToSelect += " , EXPIRED_DATE "; } if (searchObject.ExpireDate != null) { Options += " AND EXPIRED_DATE < " + searchObject.ExpireDate.Value; } if (searchObject.ShowStoreDetails) { Select += ",COM_STORE_ID "; ToSelect += ",COM_STORE_ID"; } //SqlParameter companyBranches_Param = new SqlParameter("@CompanyBranchID", companyBranches); SqlParameter Options_Param = new SqlParameter("@OPTIONS", Options); SqlParameter Select_Param = new SqlParameter("@SELECT", Select); SqlParameter Store_Param = new SqlParameter("@COM_STORE_ID", searchObject.StoreId); SqlParameter Sort_Param = new SqlParameter("@SORT", " order by ITEM_CODE "); SqlParameter STARTDATE_Param = new SqlParameter("@START_DATE", searchObject.dateFrom); SqlParameter ENDDATE_Param = new SqlParameter("@END_DATE", searchObject.dateTo); using (SqlConnection conn = new SqlConnection(context.Database.Connection.ConnectionString)) { using (var cmd = new SqlCommand()) { cmd.CommandText = "[GET_ITEM_BALANCE]"; cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(Options_Param); cmd.Parameters.Add(Select_Param); cmd.Parameters.Add(Sort_Param); cmd.Parameters.Add(STARTDATE_Param); cmd.Parameters.Add(ENDDATE_Param); cmd.Parameters.Add(Store_Param); conn.Open(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); adapter.Fill(ds); var prices = ds.Tables[1].ConvertDataTable <ItemsInventoryReportPricesVM>(); itemsInventory.ItemsBalance = (from b in ds.Tables[0].ConvertDataTable <ItemsInventoryReportBalanceResultVM>() join p in prices on b.ITEM_ID equals p.ITEM_ID select new ItemsInventoryReportBalanceResultVM { ITEM_CODE = b.ITEM_CODE, ITEM_AR_NAME = b.ITEM_AR_NAME, ITEM_EN_NAME = b.ITEM_EN_NAME, GROUP_ID = b.GROUP_ID, QTY = b.QTY, WHOLE_PRICEQTY = p.WHOLE_PRICEQTY, HALF_WHOLE_PRICEQTY = p.HALF_WHOLE_PRICEQTY, EMP_PRICEQTY = p.EMP_PRICEQTY, EXPORT_PRICEQTY = p.EXPORT_PRICEQTY, RETAIL_PRICEQTY = p.RETAIL_PRICEQTY, CONSUMER_PRICEQTY = p.CONSUMER_PRICEQTY, LAST_BUY_PRICEQTY = p.LAST_BUY_PRICEQTY }).ToList(); var totalPrices = new ItemsInventoryReportTotalPricesVM(); totalPrices.WHOLE_PRICE = (double)prices.Where(p => p.WHOLE_PRICEQTY != null).Sum(p => p.WHOLE_PRICEQTY); totalPrices.HALF_WHOLE_PRICE = (double)prices.Where(p => p.HALF_WHOLE_PRICEQTY != null).Sum(p => p.HALF_WHOLE_PRICEQTY); totalPrices.EMP_PRICE = (double)prices.Where(p => p.EMP_PRICEQTY != null).Sum(p => p.EMP_PRICEQTY); totalPrices.CONSUMER_PRICE = (double)prices.Where(p => p.CONSUMER_PRICEQTY != null).Sum(p => p.CONSUMER_PRICEQTY); totalPrices.EXPORT_PRICE = (double)prices.Where(p => p.EXPORT_PRICEQTY != null).Sum(p => p.EXPORT_PRICEQTY); totalPrices.LAST_BUY_PRICE = (double)prices.Where(p => p.LAST_BUY_PRICEQTY != null).Sum(p => p.LAST_BUY_PRICEQTY); totalPrices.RETAIL_PRICE = (double)prices.Where(p => p.RETAIL_PRICEQTY != null).Sum(p => p.RETAIL_PRICEQTY); itemsInventory.TotalPrices = totalPrices; conn.Close(); } } return itemsInventory; } catch (Exception e) { return null; } })); }
public Task <ItemsInventoryReportResultVM> getItemsInventoryReportResult(ItemsInventoryReportSearchVM searchObject) { return(Task.Run(() => { try { var itemsInventory = new ItemsInventoryReportResultVM(); string Options = "", Select = "", ToSelect = "", SortBy = " order by "; if (searchObject.StoreId != 0) { Options += " AND (BILL_MASTER.COM_STORE_ID = " + searchObject.StoreId + " OR BILL_MASTER.TO_COM_STORE_ID = " + searchObject.StoreId + ") "; } if (searchObject.ShowZeroBalancesOnly) { Options += " AND QTY = 0 "; } else if (searchObject.ShowZeroBalances && searchObject.ShowNegativeBalancesOnly) { Options += " AND QTY <= 0 "; } else if (searchObject.ShowNegativeBalancesOnly) { Options += " AND QTY < 0 "; } else if (!searchObject.ShowZeroBalances) { Options += " AND QTY <> 0 "; } if (searchObject.GroupId != null) { Options += " AND ITEMS.GROUP_ID = " + searchObject.GroupId; } if (searchObject.CostCenterId != 0) { Options += " AND BILL_MASTER.COST_CENTER_ID = " + searchObject.CostCenterId; } if (searchObject.ShowExpired) { Select += " , #BILL_DETAILS.EXPIRED_DATE "; ToSelect += " , #BILL_DETAILS.EXPIRED_DATE "; } if (searchObject.ShowStoreDetails) { Select += ",BILL_MASTER.COM_STORE_ID "; ToSelect += ",BILL_MASTER.COM_STORE_ID,BILL_MASTER.TO_COM_STORE_ID"; } if (searchObject.ExpireDate != null) { Options += " AND #BILL_DETAILS.EXPIRED_DATE < " + searchObject.ExpireDate.Value; } if (searchObject.SortBy == 1) { SortBy += "ITEM_CODE"; } else { SortBy += "ITEM_AR_NAME"; } //SqlParameter companyBranches_Param = new SqlParameter("@CompanyBranchID", companyBranches); SqlParameter Options_Param = new SqlParameter("@OPTIONS", Options); SqlParameter Select_Param = new SqlParameter("@SELECT", Select); SqlParameter ToSelect_Param = new SqlParameter("@TO_SELECT", ToSelect); SqlParameter StoresDetails_Param = new SqlParameter("@STORES_DETAILS", searchObject.ShowStoreDetails); SqlParameter ExpiredView_Param = new SqlParameter("@EXPIRED_VIEW", searchObject.ShowExpired); SqlParameter Sort_Param = new SqlParameter("@SORT", SortBy); SqlParameter Store_Param = new SqlParameter("@COM_STORE_ID", searchObject.StoreId); SqlParameter STARTDATE_Param = new SqlParameter("@START_DATE", searchObject.dateFrom); SqlParameter ENDDATE_Param = new SqlParameter("@END_DATE", searchObject.dateTo); //SqlParameter COSTCENTER_ID_Param = new SqlParameter("@COSTCENTER_ID", DBNull.Value); SqlParameter Group_Param; if (searchObject.GroupId == null) { Group_Param = new SqlParameter("@GROUP_ID", DBNull.Value); } else { Group_Param = new SqlParameter("@GROUP_ID", searchObject.GroupId); } using (SqlConnection conn = new SqlConnection(context.Database.Connection.ConnectionString)) { using (var cmd = new SqlCommand()) { cmd.CommandText = "[RPT_ITEMS_INVENTORY]"; cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(Options_Param); cmd.Parameters.Add(Select_Param); cmd.Parameters.Add(ToSelect_Param); cmd.Parameters.Add(StoresDetails_Param); cmd.Parameters.Add(ExpiredView_Param); cmd.Parameters.Add(Sort_Param); cmd.Parameters.Add(STARTDATE_Param); cmd.Parameters.Add(ENDDATE_Param); cmd.Parameters.Add(Group_Param); conn.Open(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); adapter.Fill(ds); itemsInventory.InputsOnGroups = ds.Tables[0].ConvertDataTable <ItemsInventoryReportGroupsResultVM>(); itemsInventory.OutputOnGroups = ds.Tables[1].ConvertDataTable <ItemsInventoryReportGroupsResultVM>(); itemsInventory.TransferOnGroups = ds.Tables[2].ConvertDataTable <ItemsInventoryReportGroupsResultVM>(); itemsInventory.Inputs = ds.Tables[3].ConvertDataTable <ItemsInventoryReportGroupsResultVM>(); itemsInventory.Outputs = ds.Tables[4].ConvertDataTable <ItemsInventoryReportGroupsResultVM>(); itemsInventory.Transfer = ds.Tables[5].ConvertDataTable <ItemsInventoryReportGroupsResultVM>(); itemsInventory.EmptyItems = ds.Tables[6].ConvertDataTable <ItemsInventoryReportGroupsResultVM>(); itemsInventory.EmptyItemsOnGroups = ds.Tables[7].ConvertDataTable <ItemsInventoryReportGroupsResultVM>(); itemsInventory.ItemsBalanceOnGroup = ds.Tables[8].ConvertDataTable <ItemsInventoryReportGroupsResultVM>(); conn.Close(); } } return itemsInventory; } catch (Exception e) { return null; } })); }