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;
}
}));
}
