public int UpdateOutlet(OutletModel outletModel)
{
int result = 0, storeCount = 0;
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
result = commonRepository.GetValidateUnique("Outlet", "OutletName", outletModel.OutletName, outletModel.Id.ToString());
if (result > 0)
{
return(-1);
}
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
if (outletModel.OriginalStoreId != outletModel.StoreId)
{
var queryStoreCount = "select count(storeId) from Outlet where IsActive=1 And IsDeleted=0 And storeId=" + outletModel.StoreId;
storeCount = con.QueryFirstOrDefault <int>(queryStoreCount, null, sqltrans);
}
if (storeCount == 0)
{
var query = "UPDATE Outlet SET " +
"StoreId=@StoreId, OutletName=@OutletName, OutletAddress1=@OutletAddress1, OutletAddress2=@OutletAddress2, " +
"OutletPhone=@OutletPhone, OutletEmail=@OutletEmail, InvoiceHeader=@InvoiceHeader, InvoiceFooter=@InvoiceFooter, " +
"IsCollectTax=@IsCollectTax, IsPreorPostPayment=@IsPreorPostPayment, IsActive=@IsActive, IsLock=@IsLock " +
"WHERE Id = @Id;";
result = con.Execute(query, outletModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
sqltrans.Commit();
string output = commonRepository.SyncTableStatus("Outlet");
}
else
{
sqltrans.Rollback();
}
}
else
{
result = -1;
}
}
return(result);
}
public int InsertAssetItem(AssetItemModel assetItemModel)
{
int result = 0;
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
result = commonRepository.GetValidateUnique("AssetItem", "AssetItemName", assetItemModel.AssetItemName, assetItemModel.Id.ToString());
if (result > 0)
{
return(-1);
}
int MaxId = commonRepository.GetMaxId("AssetItem");
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
var query = "INSERT INTO AssetItem " +
"(AssetCategoryId,AssetItemName, ShortName, Code, Brandname, Model, Notes,CostPrice,UnitId,TaxId," +
" UserIdInserted, DateInserted,IsDeleted) " +
"Values " +
"(@AssetCategoryId,@AssetItemName, @ShortName,@Code, @Brandname, @Model,@Notes,@CostPrice,@UnitId,@TaxId," +
LoginInfo.Userid + ",GetUtcDate(),0);" +
" SELECT CAST(SCOPE_IDENTITY() as INT);";
result = con.Execute(query, assetItemModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
sqltrans.Commit();
//CREATE ENTRY INTO INVETORY AS STOCK 0.00
query = " INSERT INTO INVENTORY (STOREID,AssetItemId,STOCKQTY,USERIDINSERTED,ISDELETED)" +
" Select S.ID as StoreId,FM.Id,0,1,0 from AssetItem FM CROSS JOIN STORE S " +
" WHERE FM.ID =" + MaxId;
result = con.Execute(query, assetItemModel, sqltrans, 0, System.Data.CommandType.Text);
string output = commonRepository.SyncTableStatus("AssetItem");
output = commonRepository.SyncTableStatus("Inventory");
}
else
{
sqltrans.Rollback();
}
}
return(result);
}
public int UpdateAssetItem(AssetItemModel assetItemModel)
{
int result = 0;
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
result = commonRepository.GetValidateUnique("AssetItem", "AssetItemName", assetItemModel.AssetItemName, assetItemModel.Id.ToString());
if (result > 0)
{
return(-1);
}
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
var query = "UPDATE AssetItem SET " +
"AssetCategoryId=@AssetCategoryId, " +
"AssetItemName=@AssetItemName, " +
"ShortName=@ShortName, " +
"Code=@Code, " +
"CostPrice=@CostPrice, " +
" Brandname = @Brandname," +
" Model =@Model ," +
"Notes=@Notes," +
"UnitId=@UnitId, " +
"TaxId=@TaxId, " +
"[UserIdUpdated] = " + LoginInfo.Userid + " " +
",[DateUpdated] = GetUtcDate() WHERE Id = @Id;";
result = con.Execute(query, assetItemModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
sqltrans.Commit();
string output = commonRepository.SyncTableStatus("AssetItem");
}
else
{
sqltrans.Rollback();
}
return(result);
}
}
public int InsertEmployee(EmployeeModel employeeModel)
{
int result = 0;
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
result = commonRepository.GetValidateUnique("Employee", "Phone", employeeModel.Phone, employeeModel.Id.ToString());
if (result > 0)
{
return(-1);
}
int MaxId = commonRepository.GetMaxId("Employee");
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
//HireDate,OriginalHireDate,TerminationDate,RehireDate,DOB,
//@HireDate,@OriginalHireDate,@TerminationDate,@RehireDate,@DOB,
var query = "INSERT INTO Employee " +
"(Id,FirstName,MiddleName,LastName,Designation,Email,Phone,AlterPhone,PresentAdress,PermanentAdress,picture" +
//,DegreeName,UniversityName,CGP,PassingYear,CompanyName,WorkingPeriod,Duties,Suoervisor,Signature,State,City,Zip,CitizenShip,TerminationReason,VolunteryTermination,RateType,Rate,PayFrequency,PayFrequencyTxt,HourlyRate2,HourlyRate3,Gender,Country,MaritalStatus,EthnicGroup,SSN,WorkInState,LiveInState,HomeEmail,BusinessEmail,HomePhone,BUsinessPhone,CellPhone,EmergConct,EmergHPhone,EmergWPhone,EmergContctRelation,AltEmContct,AltEmgHPhone,AltEmgWPhone,IsActive) " +
" )VALUES " +
"(" + MaxId + ", @FirstName,@MiddleName,@LastName,@Designation,@Email,@Phone,@AlterPhone,@PresentAdress,@PermanentAdress,@picture)" + //,@DegreeName,@UniversityName,@CGP,@PassingYear,@CompanyName,@WorkingPeriod,@Duties,@Suoervisor,@Signature,@State,@City,@Zip,@CitizenShip,@TerminationReason,@VolunteryTermination,@RateType,@Rate,@PayFrequency,@PayFrequencyTxt,@HourlyRate2,@HourlyRate3,@Gender,@Country,@MaritalStatus,@EthnicGroup,@SSN,@WorkInState,@LiveInState,@HomeEmail,@BusinessEmail,@HomePhone,@BUsinessPhone,@CellPhone,@EmergConct,@EmergHPhone,@EmergWPhone,@EmergContctRelation,@AltEmContct,@AltEmgHPhone,@AltEmgWPhone,@IsActive);" +
" SELECT CAST(SCOPE_IDENTITY() as INT);";
result = con.Execute(query, employeeModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
sqltrans.Commit();
string output = commonRepository.SyncTableStatus("Employee");
}
else
{
sqltrans.Rollback();
}
}
return(result);
}
public int InsertUser(UserModel UserModel)
{
int result = 0;
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
result = commonRepository.GetValidateUnique("[User]", "Username", UserModel.Username, UserModel.Id.ToString());
if (result > 0)
{
return(-1);
}
UserModel.RoleTypeId = (UserModel.RoleTypeId == 0) ? null : UserModel.RoleTypeId;
int MaxId = commonRepository.GetMaxId("[User]");
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
//LastLogin,LastLogout,
var query = "INSERT INTO [User] (Id,EmployeeId,OutletId,Username,Password,ThumbToken,RoleTypeId,IPAdress,Counter,IsActive,WebRoleId) " +
"Values" +
" (" + MaxId + ",@EmployeeId,@OutletId,@Username,@Password,@ThumbToken,@RoleTypeId,@IPAdress,@Counter,@IsActive,@WebRoleId); " +
"SELECT CAST(SCOPE_IDENTITY() as INT);";
result = con.Execute(query, UserModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
sqltrans.Commit();
string output = commonRepository.SyncTableStatus("[User]");
}
else
{
sqltrans.Rollback();
}
}
return(result);
}
public int InsertBank(BankModel bankModel)
{
int result = 0;
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
result = commonRepository.GetValidateUnique("Bank", "BankName", bankModel.BankName, bankModel.Id.ToString());
if (result > 0)
{
return(-1);
}
int MaxId = commonRepository.GetMaxId("Bank");
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
var query = "INSERT INTO Bank (Id,BankName," +
"AccountName,AccountNumber,Branch,SignaturePicture) " +
"VALUES (" + MaxId + ",@BankName," +
"@AccountName,@AccountNumber,@Branch,@SignaturePicture " +
"); SELECT CAST(SCOPE_IDENTITY() as INT);";
result = con.Execute(query, bankModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
sqltrans.Commit();
string output = commonRepository.SyncTableStatus("Bank");
}
else
{
sqltrans.Rollback();
}
}
return(result);
}
public int InsertRawMaterial(RawMaterialModel rawMaterialModel)
{
int result = 0;
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
result = commonRepository.GetValidateUnique("RawMaterial", "RawMaterialName", rawMaterialModel.RawMaterialName, rawMaterialModel.Id.ToString());
if (result > 0)
{
return(-1);
}
int MaxId = commonRepository.GetMaxId("RawMaterial");
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
var query = "INSERT INTO RawMaterial (Id,RawMaterialName," +
"Notes, " +
"IsActive,UserIdInserted,DateInserted,IsDeleted)" +
"VALUES (" + MaxId + ",@RawMaterialName," +
"@Notes," +
"@IsActive," + LoginInfo.Userid + ",GetUtcDate(),0); SELECT CAST(SCOPE_IDENTITY() as INT);";
result = con.Execute(query, rawMaterialModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
sqltrans.Commit();
string output = commonRepository.SyncTableStatus("RawMaterial");
}
else
{
sqltrans.Rollback();
}
return(result);
}
}
public int UpdateRewardSetup(RewardSetupModel rewardSetupModel)
{
int result = 0;
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
result = commonRepository.GetValidateUnique("RewardSetup", "OfferName", rewardSetupModel.OfferName, rewardSetupModel.Id.ToString());
if (result > 0)
{
return(-1);
}
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
var query = "UPDATE RewardSetup SET OfferName =@OfferName," +
"TransactionAmount = @TransactionAmount, " +
"RewardPoint = @RewardPoint, " +
"Notes = @Notes, " +
"IsActive = @IsActive, " +
"UserIdUpdated = " + LoginInfo.Userid +
",DateUpdated = GetUtcDate() " +
"WHERE Id = @Id;";
result = con.Execute(query, rewardSetupModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
sqltrans.Commit();
string output = commonRepository.SyncTableStatus("RewardSetup");
}
else
{
sqltrans.Rollback();
}
}
return(result);
}
public int InsertIngredientCategory(IngredientCategoryModel ingredientCategoryModel)
{
int result = 0;
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
result = commonRepository.GetValidateUnique("IngredientCategory", "IngredientCategoryName", ingredientCategoryModel.IngredientCategoryName, ingredientCategoryModel.Id.ToString());
if (result > 0)
{
return(-1);
}
int MaxId = commonRepository.GetMaxId("IngredientCategory");
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
var query = "INSERT INTO IngredientCategory (Id,IngredientCategoryName," +
"RawMaterialId,Notes, " +
"IsActive)" +
"VALUES (" + MaxId + ",@IngredientCategoryName," +
"@RawMaterialId,@Notes," +
"@IsActive); SELECT CAST(SCOPE_IDENTITY() as INT);";
result = con.Execute(query, ingredientCategoryModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
sqltrans.Commit();
string output = commonRepository.SyncTableStatus("IngredientCategory");
}
else
{
sqltrans.Rollback();
}
return(result);
}
}
public int UpdateIngredient(IngredientModel ingredientModel)
{
int result = 0;
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
result = commonRepository.GetValidateUnique("Ingredient", "IngredientName", ingredientModel.IngredientName, ingredientModel.Id.ToString());
if (result > 0)
{
return(-1);
}
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
var query = "Update Ingredient set IngredientName = @IngredientName," +
"Code = @Code ," +
"IngredientCategoryId = @CategoryId," +
"IngredientUnitId = @UnitId," +
"PurchasePrice = @PurchasePrice," +
"SalesPrice = @SalesPrice," +
"AlterQty = @AlterQty," +
"TaxId = @TaxId," +
"IsActive = @IsActive WHERE Id = @Id ";
result = con.Execute(query, ingredientModel, sqltrans, 0, System.Data.CommandType.Text);;
if (result > 0)
{
sqltrans.Commit();
string output = commonRepository.SyncTableStatus("Ingredient");
}
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 UpdateFoodMenu(FoodMenuModel foodMenuModel)
{
int result = 0;
int detailsResult = 0;
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
result = commonRepository.GetValidateUnique("FoodMenu", "FoodMenuName", foodMenuModel.FoodMenuName, foodMenuModel.Id.ToString());
if (result > 0)
{
return(-1);
}
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
var query = "UPDATE FoodMenu SET " +
"FoodCategoryId=@FoodCategoryId, " +
"FoodMenuName=@FoodMenuName, " +
"FoodMenuType=@FoodMenuType, " +
"FoodMenuCode=@FoodMenuCode, " +
"FoodMenuBarCode=@FoodMenuBarCode," +
"PurchasePrice=@PurchasePrice, " +
" UnitsId = @UnitsId," +
" FoodVatTaxId =@FoodVatTaxId ," +
"ColourCode=@ColourCode," +
"BigThumb=@BigThumb, " +
"MediumThumb=@MediumThumb, " +
"SmallThumb=@SmallThumb, " +
"SalesPrice=@SalesPrice, " +
"Notes=@Notes, " +
"IsVegItem=@IsVegItem, " +
"IsBeverages=@IsBeverages,FoodVat=@FoodVat, " +
"Foodcess=@Foodcess, " +
"OfferIsAvailable=@OfferIsAvailable," +
"Position=@Position, " +
"OutletId=@OutletId, " +
"IsActive=@IsActive " +
",[UserIdUpdated] = " + LoginInfo.Userid + " " +
",[DateUpdated] = GetUtcDate() WHERE Id = @Id;";
result = con.Execute(query, foodMenuModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
int detailResult = 0;
if (foodMenuModel.DeletedId != null)
{
foreach (var item in foodMenuModel.DeletedId)
{
var deleteQuery = $"update FoodMenuIngredient set IsDeleted = 1, UserIdDeleted = " + LoginInfo.Userid + ", DateDeleted = GetutcDate() where id = " + item + ";";
result = con.Execute(deleteQuery, null, sqltrans, 0, System.Data.CommandType.Text);
}
}
//foreach (var item in foodMenuModel.FoodMenuDetails)
//{
// var queryDetails = string.Empty;
// if (item.FoodMenuId > 0)
// {
// queryDetails = "Update [dbo].[FoodMenuIngredient] set " +
// " [FoodMenuId] = " + item.FoodMenuId + "," +
// " [IngredientId] = " + item.IngredientId + "," +
// " [Consumption] = " + item.Consumption + "," +
// " [UserIdUpdated] = " + LoginInfo.Userid + "," +
// " [DateUpdated] = GetUTCDate() " +
// " where id = " + item.FoodMenuId + ";";
// }
// else
// {
// var detailsQuery = "insert into FoodMenuIngredient (FoodMenuId, IngredientId , Consumption,[UserIdUpdated] ) values (" +
// "" + foodMenuModel.Id + "," +
// "" + item.IngredientId + "," +
// "" + item.Consumption + "," +
// "" + LoginInfo.Userid + "); ";
// }
// detailResult = con.Execute(queryDetails, null, sqltrans, 0, System.Data.CommandType.Text);
//}
if (result > 0)
{
sqltrans.Commit();
// query = $"UPDATE FoodMenuRate SET FoodVatTaxId = " + foodMenuModel.FoodVatTaxId + " WHERE FoodmenuId = {foodMenuId};";
// result = con.Execute(query, null, sqltrans, 0, System.Data.CommandType.Text);
string output = commonRepository.SyncTableStatus("FoodMenu");
}
else
{
sqltrans.Rollback();
}
}
return(result);
}
}
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 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 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 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 UpdateEmployee(EmployeeModel employeeModel)
{
int result = 0;
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
result = commonRepository.GetValidateUnique("Employee", "Phone", employeeModel.Phone, employeeModel.Id.ToString());
if (result > 0)
{
return(-1);
}
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
//"HireDate=@HireDate," +
//"OriginalHireDate=@OriginalHireDate," +
//"TerminationDate=@TerminationDate," +
//"RehireDate=@RehireDate," +
// "DOB=@DOB," +
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
var query = "UPDATE Employee SET " +
"FirstName=@FirstName," +
"MiddleName=@MiddleName," +
"LastName=@LastName," +
"Designation=@Designation," +
"Email=@Email," +
"Phone=@Phone," +
"AlterPhone=@AlterPhone," +
"PresentAdress=@PresentAdress," +
"PermanentAdress=@PermanentAdress," +
"picture=@picture," +
// "DegreeName=@DegreeName," +
//"UniversityName=@UniversityName," +
//"CGP=@CGP," +
//"PassingYear=@PassingYear," +
//"CompanyName=@CompanyName," +
//"WorkingPeriod=@WorkingPeriod," +
//"Duties=@Duties," +
//"Suoervisor=@Suoervisor," +
//"Signature=@Signature," +
//"State=@State," +
//"City=@City," +
//"Zip=@Zip," +
//"CitizenShip=@CitizenShip," +
//"TerminationReason=@TerminationReason," +
//"VolunteryTermination=@VolunteryTermination," +
//"RateType=@RateType," +
//"Rate=@Rate," +
//"PayFrequency=@PayFrequency," +
//"PayFrequencyTxt=@PayFrequencyTxt," +
//"HourlyRate2=@HourlyRate2," +
//"HourlyRate3=@HourlyRate3," +
//"Gender=@Gender," +
//" Country=@ Country," +
//"MaritalStatus=@MaritalStatus," +
//"EthnicGroup=@EthnicGroup," +
//"SSN=@SSN," +
//"WorkInState=@WorkInState," +
//"LiveInState=@LiveInState," +
//"HomeEmail=@HomeEmail," +
//"BusinessEmail=@BusinessEmail," +
//"HomePhone=@HomePhone," +
//"BUsinessPhone=@BUsinessPhone," +
//"CellPhone=@CellPhone," +
//"EmergConct=@EmergConct," +
//"EmergHPhone=@EmergHPhone," +
//"EmergWPhone=@EmergWPhone," +
//"EmergContctRelation=@EmergContctRelation," +
//"AltEmContct=@AltEmContct," +
//"AltEmgHPhone=@AltEmgHPhone," +
//"AltEmgWPhone=@AltEmgWPhone," +
"IsActive=@IsActive " +
"WHERE Id = @Id;";
result = con.Execute(query, employeeModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
sqltrans.Commit();
string output = commonRepository.SyncTableStatus("Employee");
}
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 InsertUpdateFoodMenuIngredient(FoodMenuIngredientModel foodMenuIngredientModel)
{
int result = 0, detailResult = 0;
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
int MaxId = commonRepository.GetMaxId("FoodMenuIngredient");
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
int deleteResult = 0;
if (foodMenuIngredientModel.DeletedId != null)
{
foreach (var item in foodMenuIngredientModel.DeletedId)
{
var deleteQuery = $"update FoodMenuIngredient set IsDeleted = 1, UserIdDeleted = " + LoginInfo.Userid + ", DateDeleted = GetutcDate() where id = " + item + ";";
deleteResult = con.Execute(deleteQuery, null, sqltrans, 0, System.Data.CommandType.Text);
}
}
foreach (var item in foodMenuIngredientModel.FoodMenuIngredientDetails)
{
var query = string.Empty;
if (item.Id > 0)
{
query = "update FoodMenuIngredient set " +
"FoodMenuId =" + item.FoodMenuId + "," +
"IngredientId =" + item.IngredientId + "," +
"Consumption = " + item.Consumption + "," +
"UserIdUpdated =" + LoginInfo.Userid + "," +
"DateUpdated=getutcdate(),IsDeleted = 0 Where Id=" + item.Id;
}
else
{
query = "INSERT INTO FoodMenuIngredient" +
" (Id " +
" ,FoodMenuId " +
" ,IngredientId " +
" ,Consumption" +
" ,UserIdInserted" +
" ,DateInserted,IsDeleted) " +
"VALUES " +
"("
+ MaxId.ToString() + "," +
+item.FoodMenuId + "," +
item.IngredientId + "," +
item.Consumption + "," +
LoginInfo.Userid +
",GetUtcDate(),0);";
MaxId = MaxId + 1;
}
detailResult = con.Execute(query, null, sqltrans, 0, System.Data.CommandType.Text);
}
if (detailResult > 0)
{
result = 1;
sqltrans.Commit();
}
else
{
sqltrans.Rollback();
}
}
return(result);
}
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 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);
}
