public int UpdateProductionEntry(ProductionEntryModel productionEntryModel)
{
int result = 0, deleteFoodMenuResult = 0, deleteIngredientResult = 0, foodmenudetails = 0, ingredientdetails = 0;
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
var query = "Update [dbo].[ProductionEntry] set " +
" ProductionDate = @ProductionDate " +
",ProductionCompletionDate = @ProductionCompletionDate " +
",ActualBatchSize = @ActualBatchSize " +
",Status = @Status " +
",VariationNotes = @VariationNotes " +
",Notes = @Notes " +
",StoreId = @StoreId " +
" ,[UserIdUpdated] = " + LoginInfo.Userid + " " +
" ,[DateUpdated] = GetUtcDate() where id= " + productionEntryModel.Id + ";";
result = con.Execute(query, productionEntryModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
if (productionEntryModel.FoodMenuDeletedId != null)
{
foreach (var item in productionEntryModel.FoodMenuDeletedId)
{
var deleteQuery = $"update ProductionEntryFoodmenu set IsDeleted = 1, UserIdDeleted = " + LoginInfo.Userid + ", DateDeleted = GetutcDate() where id = " + item + ";";
deleteFoodMenuResult = con.Execute(deleteQuery, null, sqltrans, 0, System.Data.CommandType.Text);
}
}
if (productionEntryModel.IngredientDeletedId != null)
{
foreach (var item in productionEntryModel.IngredientDeletedId)
{
var deleteQuery = $"update ProductionEntryIngredient set IsDeleted = 1, UserIdDeleted = " + LoginInfo.Userid + ", DateDeleted = GetutcDate() where id = " + item + ";";
deleteIngredientResult = con.Execute(deleteQuery, null, sqltrans, 0, System.Data.CommandType.Text);
}
}
foreach (var item in productionEntryModel.productionEntryFoodMenuModels)
{
var queryDetails = string.Empty;
if (item.PEFoodMenuId > 0)
{
queryDetails = "Update [dbo].[ProductionEntryFoodmenu] set " +
"[FoodMenuId] = "+ item.FoodMenuId +
//",[ExpectedOutput] = " + item.ExpectedOutput +
",[AllocationOutput] = " + item.AllocationOutput +
",[ActualOutput] = " + item.ActualOutput +
" ,[UserIdUpdated] = " + LoginInfo.Userid + "," +
" [DateUpdated] = GetUTCDate() " +
" where id = " + item.PEFoodMenuId + ";";
}
foodmenudetails = con.Execute(queryDetails, null, sqltrans, 0, System.Data.CommandType.Text);
}
foreach (var item in productionEntryModel.productionEntryIngredientModels)
{
var queryDetails = string.Empty;
if (item.PEIngredientId > 0)
{
queryDetails = "Update [dbo].[ProductionEntryIngredient] set " +
"[IngredientId] = "+ item.IngredientId +
//",[IngredientQty] = " + item.IngredientQty +
",[AllocationIngredientQty] = " + item.AllocationIngredientQty +
",[ActualIngredientQty] = " + item.ActualIngredientQty +
" ,[UserIdUpdated] = " + LoginInfo.Userid + "," +
" [DateUpdated] = GetUTCDate() " +
" where id = " + item.PEIngredientId + ";";
}
ingredientdetails = con.Execute(queryDetails, null, sqltrans, 0, System.Data.CommandType.Text);
}
if (foodmenudetails > 0 && ingredientdetails > 0)
{
sqltrans.Commit();
if (productionEntryModel.Status == 2)
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
string sResult = commonRepository.InventoryPush("PE_Ingredient", Convert.ToInt32(productionEntryModel.Id));
}
if (productionEntryModel.Status == 3)
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
string sResult = commonRepository.InventoryPush("PE_Food", productionEntryModel.Id);
}
}
else
{
sqltrans.Rollback();
}
}
else
{
sqltrans.Rollback();
}
}
return(result);
}
public int InsertInventoryTransfer(InventoryTransferModel inventoryTransferModel)
{
int result = 0;
int detailResult = 0;
string foodMenuId = "NULL", ingredientId = "NULL", assetItemId = "NULL";
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
var query = "INSERT INTO [InventoryTransfer] " +
" ( FromStoreId, ToStoreId,ReferenceNumber,InventoryType,EntryDate ,EmployeeId,Notes,UserIdInserted,DateInserted,IsDeleted ) " +
" VALUES " +
" ( @FromStoreId, @ToStoreId,@ReferenceNo,@InventoryType,@Date ,@EmployeeId,@Notes," + LoginInfo.Userid + ",GetUTCDate(),0); " +
" SELECT CAST(Scope_Identity() as int); ";
result = con.ExecuteScalar <int>(query, inventoryTransferModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
foreach (var item in inventoryTransferModel.InventoryTransferDetail)
{
/*
* int consumptionId = 0;
* if (item.ConsumpationStatus.Value.ToString() == "StockIN")
* {
* consumptionId = 1;
* }
* else
* {
* consumptionId = 2;
* }
*/
if (item.IngredientId == 0)
{
ingredientId = "NULL";
}
else
{
ingredientId = item.IngredientId.ToString();
}
if (item.AssetItemId == 0)
{
assetItemId = "NULL";
}
else
{
assetItemId = item.AssetItemId.ToString();
}
if (item.FoodMenuId == 0)
{
foodMenuId = "NULL";
}
else
{
foodMenuId = item.FoodMenuId.ToString();
}
var queryDetails = "INSERT INTO InventoryTransferDetail" +
" (InventoryTransferId,IngredientId,FoodMenuId,AssetItemId,Qty,ConsumptionStatus,CurrentStock,UserIdInserted,DateInserted,IsDeleted) " +
"VALUES " +
"(" + result + "," +
"" + ingredientId + "," +
"" + foodMenuId + "," +
"" + assetItemId + "," +
"" + item.Quantity + "," +
"" + 1 + "," +
"" + item.CurrentStock + "," +
"" + LoginInfo.Userid + ",GetUtcDate(),0); " +
" SELECT CAST(ReferenceNumber as INT) from [InventoryTransfer] where id = " + result + "; ";
detailResult = con.ExecuteScalar <int>(queryDetails, null, sqltrans, 0, System.Data.CommandType.Text);
}
if (detailResult > 0)
{
sqltrans.Commit();
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
string sResult = commonRepository.InventoryPush("IT", result);
}
else
{
sqltrans.Rollback();
}
}
else
{
sqltrans.Rollback();
}
}
return(detailResult);
}
public int InsertProductionEntry(ProductionEntryModel productionEntryModel)
{
int result = 0;
int foodMenuResult = 0, ingredientResult = 0;
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
var refNoQuery = $"SELECT ISNULL(MAX(convert(int,ReferenceNo)),0) + 1 FROM ProductionEntry where foodmenutype=" + productionEntryModel.FoodmenuType + " and isdeleted = 0; ";
var referenceNo = con.ExecuteScalar <string>(refNoQuery, null, sqltrans, 0, System.Data.CommandType.Text);
var query = "INSERT INTO [dbo].[ProductionEntry] " +
" ([ProductionFormulaId] " +
" ,[FoodmenuType] " +
" ,[ReferenceNo] " +
" ,[ProductionDate] " +
" ,[ProductionCompletionDate] " +
" ,[ActualBatchSize] " +
" ,[Status] " +
" ,[VariationNotes] " +
" ,[Notes] " +
" ,[StoreId] " +
" ,[UserIdInserted] " +
" ,[DateInserted] " +
" ,[IsDeleted]) " +
" VALUES " +
" (@ProductionFormulaId " +
" ,@FoodmenuType " +
" ,'" + referenceNo + "' " +
" ,@ProductionDate " +
" ,GetUtcDate() " +
" ,@ActualBatchSize " +
" ,@Status " +
" ,@VariationNotes " +
" ,@Notes " +
" ,@StoreId, " +
"" + LoginInfo.Userid + "," +
" GetUtcDate(), " +
" 0); SELECT CAST(SCOPE_IDENTITY() as int); ";
result = con.ExecuteScalar <int>(query, productionEntryModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
foreach (var foodmenu in productionEntryModel.productionEntryFoodMenuModels)
{
var queryDetails = "INSERT INTO [dbo].[ProductionEntryFoodmenu]" +
" ([ProductionEntryId] " +
" ,[FoodMenuId] " +
" ,[ExpectedOutput] " +
" ,[AllocationOutput] " +
" ,[ActualOutput] " +
" ,[UserIdInserted]" +
" ,[DateInserted]" +
" ,[IsDeleted]) " +
"VALUES " +
"(" + result + "," +
foodmenu.FoodMenuId + "," +
foodmenu.ExpectedOutput + "," +
foodmenu.AllocationOutput + "," +
foodmenu.ActualOutput + "," +
LoginInfo.Userid + ",GetUtcDate(),0);";
foodMenuResult = con.Execute(queryDetails, null, sqltrans, 0, System.Data.CommandType.Text);
}
foreach (var ingredient in productionEntryModel.productionEntryIngredientModels)
{
var queryDetails = "INSERT INTO [dbo].[ProductionEntryIngredient]" +
" ([ProductionEntryId] " +
" ,[IngredientId] " +
" ,[IngredientQty] " +
" , [AllocationIngredientQty] " +
" ,[ActualIngredientQty] " +
" ,[UserIdInserted]" +
" ,[DateInserted]" +
" ,[IsDeleted]) " +
"VALUES " +
"(" + result + "," +
ingredient.IngredientId + "," +
ingredient.IngredientQty + "," +
ingredient.AllocationIngredientQty + "," +
ingredient.ActualIngredientQty + "," +
LoginInfo.Userid + ",GetUtcDate(),0);";
ingredientResult = con.Execute(queryDetails, null, sqltrans, 0, System.Data.CommandType.Text);
}
if (foodMenuResult > 0 && ingredientResult > 0)
{
sqltrans.Commit();
if (productionEntryModel.Status == 2)
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
string sResult = commonRepository.InventoryPush("PE_Ingredient", result);
}
if (productionEntryModel.Status == 3)
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
string sResult = commonRepository.InventoryPush("PE_Food", result);
}
}
else
{
sqltrans.Rollback();
}
}
else
{
sqltrans.Rollback();
}
}
return(result);
}
public int InsertPurchaseGRNFoodMenu(SalesDeliveryModel purchaseModel)//
{
bool taxInclusive = GetCustomerTaxExampt((int)purchaseModel.CustomerId);
int result = 0;
int detailResult = 0;
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
var query = "INSERT INTO [dbo].[SalesDelivery] " +
" ([SalesId] " +
" ,[ReferenceNumber] " +
" ,[InventoryType] " +
" ,[CustomerId] " +
" ,[StoreId] " +
" ,[EmployeeId] " +
" ,[SalesDeliveryDate] " +
" ,[GrossAmount] " +
" ,[TaxAmount] " +
" ,[TotalAmount] " +
" ,[VatableAmount] " +
" ,[NonVatableAmount] " +
" ,[DeliveryNoteNumber] " +
" ,[DeliveryDate] " +
" ,[DriverName] " +
" ,[VehicleNumber] " +
" ,[PaidAmount] " +
" ,[DueAmount] " +
" ,[Notes] " +
" ,[UserIdInserted] " +
" ,[DateInserted] " +
" ,[IsDeleted] ) " +
" VALUES " +
" (@SalesId, " +
" @ReferenceNo, " +
" @InventoryType, " +
" @CustomerId, " +
" @StoreId, " +
" @EmployeeId, " +
" @SalesDeliveryDate, ";
if (taxInclusive == true)
{
query = query + " @GrossAmount, " +
" @TaxAmount, " +
" @TotalAmount, " +
" @VatableAmount, " +
" @NonVatableAmount, ";
}
else
{
query = query + " @TotalAmount, " +
" 0, " +
" @TotalAmount, " +
" 0, " +
" 0, ";
}
query = query + " @DeliveryNoteNumber," +
" @DeliveryDate," +
" @DriverName," +
" @VehicleNumber," +
" @PaidAmount, " +
" @DueAmount, " +
" @Notes," +
"" + LoginInfo.Userid + "," +
" GetUtcDate(), " +
" 0); SELECT CAST(SCOPE_IDENTITY() as int); ";
result = con.ExecuteScalar <int>(query, purchaseModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
foreach (var item in purchaseModel.salesDeliveryDetails)
{
var queryDetails = "INSERT INTO [dbo].[SalesDeliveryDetail]" +
" ([SalesDeliveryId] " +
" ,[FoodMenuId] " +
" ,[IngredientId] " +
" ,[AssetItemId] " +
" ,[SOQty] " +
" ,[DeliveryQty] " +
" ,[UnitPrice] " +
" ,[GrossAmount] " +
" ,[DiscountPercentage] " +
" ,[DiscountAmount] " +
" ,[TaxAmount] " +
" ,[TotalAmount] " +
" ,[VatableAmount] " +
" ,[NonVatableAmount] " +
" ,[UserIdInserted]" +
" ,[DateInserted]" +
" ,[IsDeleted]) " +
"VALUES ( " + result + ",";
if (item.ItemType == 0)
{
queryDetails = queryDetails + "" + item.FoodMenuId + ",NUll,NUll,";
}
else if (item.ItemType == 1)
{
queryDetails = queryDetails + "NULL," + item.FoodMenuId + ",NUll,";
}
else if (item.ItemType == 2)
{
queryDetails = queryDetails + "NUll,NULL," + item.FoodMenuId + ",";
}
queryDetails = queryDetails + "" + item.SOQTY + "," +
item.DeliveryQTY + "," +
item.UnitPrice + ",";
if (taxInclusive == true)
{
queryDetails = queryDetails +
item.GrossAmount + "," +
item.DiscountPercentage + "," +
item.DiscountAmount + "," +
item.TaxAmount + "," +
item.TotalAmount + "," +
item.VatableAmount + "," +
item.NonVatableAmount + ",";
}
else
{
queryDetails = queryDetails +
item.TotalAmount + "," +
item.DiscountPercentage + "," +
item.DiscountAmount + "," +
"0," +
item.TotalAmount + "," +
"0," +
"0,";
}
queryDetails = queryDetails + LoginInfo.Userid + ",GetUTCDate(),0); SELECT CAST(ReferenceNumber as INT) from SalesDelivery where id = " + result + "; ";
detailResult = con.ExecuteScalar <int>(queryDetails, null, sqltrans, 0, System.Data.CommandType.Text);
}
if (detailResult > 0)
{
sqltrans.Commit();
int outResult = 0;
if (purchaseModel.SalesId > 0)
{
outResult = UpdatePurchaseOrderId(purchaseModel.SalesId);
}
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
string sResult = commonRepository.InventoryPush("SalesDelivery", result);
}
else
{
sqltrans.Rollback();
}
}
else
{
sqltrans.Rollback();
}
}
return(detailResult);
}
public int InsertInventoryAlteration(InventoryAlterationModel inventoryAlterationModel)
{
int result = 0;
int foodMenuResult = 0;
string foodMenuId = "NULL", ingredientId = "NULL", assetItemId = "NULL";
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
var query = "INSERT INTO [dbo].[InventoryAlteration] " +
" ([ReferenceNo] " +
" ,[EntryDate] " +
" ,[StoreId] " +
" ,[Notes] " +
" ,[InventoryType] " +
" ,[UserIdInserted] " +
" ,[DateInserted] " +
" ,[IsDeleted]) " +
" VALUES " +
" (@ReferenceNo, " +
" GetUtcDate() " +
" ,@StoreId, " +
" @Notes, " +
" @InventoryType, " +
"" + LoginInfo.Userid + "," +
" GetUtcDate(), " +
" 0); SELECT CAST(SCOPE_IDENTITY() as int); ";
result = con.ExecuteScalar <int>(query, inventoryAlterationModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
foreach (var foodmenu in inventoryAlterationModel.InventoryAlterationDetails)
{
if (foodmenu.IngredientId == 0)
{
ingredientId = "NULL";
}
else
{
ingredientId = foodmenu.IngredientId.ToString();
}
if (foodmenu.AssetItemId == 0)
{
assetItemId = "NULL";
}
else
{
assetItemId = foodmenu.AssetItemId.ToString();
}
if (foodmenu.FoodMenuId == 0)
{
foodMenuId = "NULL";
}
else
{
foodMenuId = foodmenu.FoodMenuId.ToString();
}
var queryDetails = "INSERT INTO [dbo].[InventoryAlterationDetail]" +
" ([InventoryAlterationId] " +
" ,[FoodMenuId] " +
" ,[IngredientId] " +
" ,[AssetItemId] " +
" ,[Qty] " +
" ,[InventoryStockQty] " +
" ,[Amount] " +
" ,[EntryDate] " +
" ,[UserIdInserted]" +
" ,[DateInserted]" +
" ,[IsDeleted]) " +
"VALUES " +
"(" + result + "," +
foodMenuId + "," +
ingredientId + "," +
assetItemId + "," +
foodmenu.Qty + "," +
foodmenu.InventoryStockQty + "," +
foodmenu.Amount + "," +
" GetUtcDate()" + "," +
LoginInfo.Userid + ",GetUtcDate(),0);";
foodMenuResult = con.Execute(queryDetails, null, sqltrans, 0, System.Data.CommandType.Text);
}
if (foodMenuResult > 0)
{
sqltrans.Commit();
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
string sResult = commonRepository.InventoryPush("PhysicalStock", result);
}
else
{
sqltrans.Rollback();
}
}
else
{
sqltrans.Rollback();
}
}
return(result);
}
public int InsertInventoryAdjustment(InventoryAdjustmentModel inventoryAdjustmentModel)
{
int result = 0;
int detailResult = 0;
string foodMenuId = "NULL", ingredientId = "NULL", assetItemId = "NULL";
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
if (LoginInfo.Userid == 0)
{
LoginInfo.Userid = 1;
}
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
var query = "INSERT INTO InventoryAdjustment " +
" ( StoreId, ReferenceNumber,InventoryType,ConsumptionStatus,EntryDate,EmployeeId,Notes,UserIdInserted,DateInserted,IsDeleted ) " +
" VALUES " +
" ( @StoreId, @ReferenceNo,@InventoryType,@ConsumptionStatus, @Date,@EmployeeId,@Notes," + LoginInfo.Userid + ",GetUTCDate(),0); " +
" SELECT CAST(Scope_Identity() as int); ";
result = con.ExecuteScalar <int>(query, inventoryAdjustmentModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
foreach (var item in inventoryAdjustmentModel.InventoryAdjustmentDetail)
{
if (item.AssetItemId == 0)
{
assetItemId = "NULL";
}
else
{
assetItemId = item.AssetItemId.ToString();
}
if (item.IngredientId == 0)
{
ingredientId = "NULL";
}
else
{
ingredientId = item.IngredientId.ToString();
}
if (item.FoodMenuId == 0)
{
foodMenuId = "NULL";
}
else
{
foodMenuId = item.FoodMenuId.ToString();
var FoodmenuPurchaePriceUpdate = "" +
" update foodmenu set PurchasePrice = " + item.Price + " Where id = " + item.FoodMenuId;
}
var queryDetails = "INSERT INTO InventoryAdjustmentDetail" +
" (InventoryAdjustmentId,IngredientId,FoodMenuId,AssetItemId,Qty,Price,Total ,ConsumptionStatus,UserIdInserted,DateInserted,IsDeleted) " +
"VALUES " +
"(" + result + "," +
"" + ingredientId + "," +
"" + foodMenuId + "," +
"" + assetItemId + "," +
"" + item.Quantity + "," +
"" + item.Price + "," +
"" + item.TotalAmount + "," +
"" + inventoryAdjustmentModel.ConsumptionStatus + "," +
"" + LoginInfo.Userid + ",GetUtcDate(),0); " +
" SELECT CAST(ReferenceNumber as INT) from InventoryAdjustment where id = " + result + "; ";
detailResult = con.ExecuteScalar <int>(queryDetails, null, sqltrans, 0, System.Data.CommandType.Text);
}
if (detailResult > 0)
{
sqltrans.Commit();
if (inventoryAdjustmentModel.ConsumptionStatus == 1)
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
string sResult = commonRepository.InventoryPush("IA", result);
}
if (inventoryAdjustmentModel.ConsumptionStatus == 2)
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
string sResult = commonRepository.InventoryPush("IA-Out", result);
}
}
else
{
sqltrans.Rollback();
}
}
else
{
sqltrans.Rollback();
}
}
return(detailResult);
}
public int InsertWaste(WasteModel wasteModel)
{
int result = 0;
int detailResult = 0;
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
var query = "INSERT INTO Waste " +
"(StoreId, " +
"ReferenceNumber, " +
"WasteDateTime, " +
"EmployeeId, " +
"TotalLossAmount, " +
"ReasonForWaste, " +
"WasteStatus," +
"UserIdInserted ," +
"[DateInserted], " +
"CreatedUserId ," +
"[CreatedDatetime], " +
"[IsDeleted]) " +
" VALUES " +
" (@StoreId, " +
"@ReferenceNumber, " +
"@WasteDateTime, " +
"@EmployeeId, " +
"@TotalLossAmount, " +
"@ReasonForWaste, " +
"@WasteStatus, " +
"" + LoginInfo.Userid + "," +
"GetUtcDate(), " +
"" + LoginInfo.Userid + "," +
"GetUtcDate(), " +
"0); SELECT CAST(SCOPE_IDENTITY() as int); ";
result = con.ExecuteScalar <int>(query, wasteModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
var queryDetails = string.Empty;
foreach (var item in wasteModel.WasteDetail)
{
if (item.FoodMenuId == 0)
{
queryDetails = "INSERT INTO WasteIngredient ([WasteId],FoodMenuId,[IngredientId] ,IngredientQty, LossAmount, [UserIdInserted],DateInserted,[IsDeleted]) " +
"VALUES " +
"(" + result + ",NULL," + item.IngredientId + "," + item.Qty + "," + item.LossAmount +
"," + LoginInfo.Userid + "," + "GetUtcDate(),0);" +
" SELECT CAST(ReferenceNumber as INT) from waste where id = " + result + "; ";
}
else
{
queryDetails = "INSERT INTO WasteIngredient ([WasteId],FoodMenuId,[IngredientId] ,IngredientQty, LossAmount, [UserIdInserted],DateInserted,[IsDeleted]) " +
"VALUES " +
"(" + result + "," + item.FoodMenuId + ",NULL," + item.Qty + "," + item.LossAmount +
"," + LoginInfo.Userid + "," + "GetUtcDate(),0);" +
" SELECT CAST(ReferenceNumber as INT) from waste where id = " + result + "; ";
}
detailResult = con.ExecuteScalar <int>(queryDetails, null, sqltrans, 0, System.Data.CommandType.Text);
}
if (detailResult > 0)
{
sqltrans.Commit();
if ((int)wasteModel.WasteStatus == 2)
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
string sResult = commonRepository.InventoryPush("Waste", result);
}
}
else
{
sqltrans.Rollback();
}
}
else
{
sqltrans.Rollback();
}
}
return(detailResult);
}
public int UpdateWaste(WasteModel wasteModel)
{
int result = 0;
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
var query = "Update Waste set " +
"StoreId=@StoreId, ReferenceNumber=@ReferenceNumber, WasteDateTime=@WasteDateTime, EmployeeId=@EmployeeId, " +
" TotalLossAmount=@TotalLossAmount, ReasonForWaste=@ReasonForWaste, WasteStatus=@WasteStatus ";
if ((int)wasteModel.WasteStatus == 2)
{
query += ",ApprovedUserId = " + LoginInfo.Userid + ",ApprovedDateTime=GetUtcDate()";
}
query += " ,UserIdUpdated = " + LoginInfo.Userid + ",DateUpdated = GetUtcDate() where id= " + wasteModel.Id + ";";
result = con.Execute(query, wasteModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
int detailResult = 0;
if (wasteModel.DeletedId != null)
{
foreach (var item in wasteModel.DeletedId)
{
var obj = item.Split('|');
var deleteQuery = string.Empty;
if (Convert.ToInt32(obj[2]) == 0)
{
deleteQuery = $"update WasteIngredient set IsDeleted = 1, UserIdDeleted = " + LoginInfo.Userid + ", DateDeleted = GetutcDate() where wasteid = " + Convert.ToInt32(obj[0]) + " and FoodMenuId = " + Convert.ToInt32(obj[1]) + " ;";
}
else
{
deleteQuery = $"update WasteIngredient set IsDeleted = 1, UserIdDeleted = " + LoginInfo.Userid + ", DateDeleted = GetutcDate() where wasteid = " + Convert.ToInt32(obj[0]) + " and " + " IngredientId=" + Convert.ToInt32(obj[2]) + " and FoodMenuId = 0;";
}
result = con.Execute(deleteQuery, null, sqltrans, 0, System.Data.CommandType.Text);
}
}
foreach (var item in wasteModel.WasteDetail)
{
var queryDetails = string.Empty;
if (item.WasteIngredientId > 0)
{
if (item.FoodMenuId > 0)
{
queryDetails = "Update [dbo].[WasteIngredient] set " +
" [IngredientQty] = " + item.Qty + "," +
" [LossAmount] = " + item.LossAmount + "," +
" UserIdUpdated = " + LoginInfo.Userid + "," +
" DateUpdated = GetUtcDate()" +
" where Id = " + item.WasteIngredientId + ";";
}
else if (item.IngredientId > 0)
{
queryDetails = "Update [dbo].[WasteIngredient] set " +
" [IngredientQty] = " + item.Qty + "," +
" [LossAmount] = " + item.LossAmount + "," +
" UserIdUpdated = " + LoginInfo.Userid + "," +
" DateUpdated = GetUtcDate()" +
" where Id = " + item.WasteIngredientId + ";";
}
}
else
{
if (item.FoodMenuId == 0)
{
queryDetails = "INSERT INTO WasteIngredient ([WasteId],FoodMenuId,[IngredientId] ,IngredientQty, LossAmount, [UserIdInserted],DateInserted,[IsDeleted]) " +
"VALUES " +
"(" + wasteModel.Id + ",NULL," + item.IngredientId + "," + item.Qty + "," + item.LossAmount +
"," + LoginInfo.Userid + "," + "GetUtcDate(),0);" +
" SELECT CAST(ReferenceNumber as INT) from waste where id = " + result + "; ";
}
else
{
queryDetails = "INSERT INTO WasteIngredient ([WasteId],FoodMenuId,[IngredientId] ,IngredientQty, LossAmount, [UserIdInserted],DateInserted,[IsDeleted]) " +
"VALUES " +
"(" + wasteModel.Id + "," + item.FoodMenuId + ",NULL," + item.Qty + "," + item.LossAmount +
"," + LoginInfo.Userid + "," + "GetUtcDate(),0);" +
" SELECT CAST(ReferenceNumber as INT) from waste where id = " + result + "; ";
}
}
detailResult = con.Execute(queryDetails, null, sqltrans, 0, System.Data.CommandType.Text);
}
if (detailResult > 0)
{
sqltrans.Commit();
if ((int)wasteModel.WasteStatus == 2)
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
string sResult = commonRepository.InventoryPush("Waste", wasteModel.Id);
}
}
else
{
sqltrans.Rollback();
}
}
else
{
sqltrans.Rollback();
}
}
return(result);
}
