public int InsertFoodMenu(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); } int MaxId = commonRepository.GetMaxId("FoodMenu"); con.Open(); SqlTransaction sqltrans = con.BeginTransaction(); var query = "INSERT INTO FoodMenu " + "( Id,FoodCategoryId, FoodMenuName, FoodMenuType,FoodMenuCode, FoodMenuBarCode,PurchasePrice,SalesPrice, Notes, UnitsId,FoodVatTaxId," + " Position, IsActive) " + "Values " + "(" + MaxId + ", @FoodCategoryId, upper(@FoodMenuName),@FoodMenuType, @FoodMenuCode, @FoodMenuBarCode,@PurchasePrice,@SalesPrice, @Notes,@UnitsId,@FoodVatTaxId," + "@Position, @IsActive);" + " SELECT CAST(SCOPE_IDENTITY() as INT);"; result = con.Execute(query, foodMenuModel, sqltrans, 0, System.Data.CommandType.Text); if (result > 0) { sqltrans.Commit(); //CREATE ENTRY INTO INVETORY AS STOCK 0.00 query = " INSERT INTO INVENTORY (STOREID,FOODMENUID,STOCKQTY,USERIDINSERTED,ISDELETED)" + " Select S.ID as StoreId,FM.Id,0,1,0 from foodmenu FM CROSS JOIN STORE S " + " WHERE FM.ID =" + MaxId; result = con.Execute(query, foodMenuModel, sqltrans, 0, System.Data.CommandType.Text); //CREATE ENTRY INTO FOODMENURATE query = " INSERT INTO FOODMENURATE(Id, OutletId, FoodMenuId, SalesPrice, FoodVatTaxId, IsActive) " + " Select(select max(Id) from foodmenurate) + ROW_NUMBER() OVER(ORDER BY fm.id desc) AS Row# " + " , O.Id,FM.Id,FM.SalesPrice,FM.FoodVatTaxId,1 from FoodMenu FM Cross join Outlet O " + " Where FM.Id =" + MaxId; result = con.Execute(query, foodMenuModel, sqltrans, 0, System.Data.CommandType.Text); string output = commonRepository.SyncTableStatus("FoodMenu"); output = commonRepository.SyncTableStatus("INVENTORY"); output = commonRepository.SyncTableStatus("FOODMENURATE"); } else { sqltrans.Rollback(); } } return(result); }
public int InsertOutlet(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); } int MaxId = commonRepository.GetMaxId("Outlet"); con.Open(); SqlTransaction sqltrans = con.BeginTransaction(); 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 = "INSERT INTO Outlet" + "(Id,StoreId, OutletName, OutletAddress1, OutletAddress2, OutletPhone, OutletEmail," + " InvoiceHeader, InvoiceFooter, IsCollectTax, IsPreorPostPayment, IsActive, IsLock)" + "VALUES " + "(" + MaxId + ", @StoreId, @OutletName, @OutletAddress1, @OutletAddress2, @OutletPhone, @OutletEmail, " + "@InvoiceHeader, @InvoiceFooter, @IsCollectTax, @IsPreorPostPayment, @IsActive, @IsLock); " + " SELECT CAST(SCOPE_IDENTITY() as INT);"; result = con.Execute(query, outletModel, sqltrans, 0, System.Data.CommandType.Text); if (result > 0) { sqltrans.Commit(); //CREATE ENTRY INTO FOODMENURATE query = " INSERT INTO FOODMENURATE(Id, OutletId, FoodMenuId, SalesPrice, FoodVatTaxId, IsActive) " + " Select(select max(Id) from foodmenurate) + ROW_NUMBER() OVER(ORDER BY fm.id desc) AS Row# , " + MaxId + ", FM.Id,FM.SalesPrice,FM.FoodVatTaxId,1 FROM FoodMenu FM WHERE isdeleted = 0 "; result = con.Execute(query, outletModel, sqltrans, 0, System.Data.CommandType.Text); string output = commonRepository.SyncTableStatus("Outlet"); output = commonRepository.SyncTableStatus("FOODMENURATE"); } else { sqltrans.Rollback(); } } else { result = -1; } } return(result); }
public int InsertIngredient(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); } int MaxId = commonRepository.GetMaxId("Ingredient"); con.Open(); SqlTransaction sqltrans = con.BeginTransaction(); var query = "INSERT into Ingredient(Id,IngredientName," + "Code, " + "IngredientCategoryId," + "IngredientUnitId," + "PurchasePrice," + "SalesPrice," + "AlterQty," + "TaxId," + "IsActive) " + "VALUES(" + MaxId + ",@IngredientName," + " @Code," + " @CategoryId," + "@UnitId," + "@PurchasePrice," + "@SalesPrice," + "@AlterQty," + "@TaxId," + "@IsActive" + " ); SELECT CAST(SCOPE_IDENTITY() as INT); "; result = con.Execute(query, ingredientModel, sqltrans, 0, System.Data.CommandType.Text); if (result > 0) { sqltrans.Commit(); //CREATE ENTRY INTO INVETORY AS STOCK 0.00 query = " INSERT INTO INVENTORY (STOREID,IngredientId,STOCKQTY,USERIDINSERTED,ISDELETED)" + " Select S.ID as StoreId,FM.Id,0,1,0 from Ingredient FM CROSS JOIN STORE S " + " WHERE FM.ID =" + MaxId; result = con.Execute(query, ingredientModel, sqltrans, 0, System.Data.CommandType.Text); string output = commonRepository.SyncTableStatus("Ingredient"); output = commonRepository.SyncTableStatus("INVENTORY"); } else { sqltrans.Rollback(); } } return(result); }
public int InsertStore(StoreModel storeModel) { int result = 0; using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString)) { CommonRepository commonRepository = new CommonRepository(_ConnectionString); result = commonRepository.GetValidateUnique("Store", "StoreName", storeModel.StoreName, storeModel.Id.ToString()); if (result > 0) { return(-1); } int MaxId = commonRepository.GetMaxId("Store"); con.Open(); SqlTransaction sqltrans = con.BeginTransaction(); var query = "INSERT INTO Store (Id,StoreName, IsMainStore,Notes,IsActive,IsLock) " + "VALUES (" + MaxId + ",@StoreName, @IsMainStore,@Notes,@IsActive,@IsLock);" + " SELECT CAST(SCOPE_IDENTITY() as INT);"; result = con.Execute(query, storeModel, sqltrans, 0, System.Data.CommandType.Text); if (result > 0) { sqltrans.Commit(); //CREATE ENTRY INTO INVENTORY WITH ALL FOODMENU WITH STOCKQTY AS 0.00 query = " INSERT INTO INVENTORY(STOREID, FOODMENUID, STOCKQTY, USERIDINSERTED, ISDELETED) " + " Select S.Id,FM.Id,0,1,0 from FoodMenu FM Cross join STORE S Where S.Id = " + MaxId; result = con.Execute(query, storeModel, sqltrans, 0, System.Data.CommandType.Text); //CREATE ENTRY INTO INVENTORY WITH ALL INGREDIENT WITH STOCKQTY AS 0.00 query = " INSERT INTO INVENTORY(STOREID, INGREDIENTid, STOCKQTY, USERIDINSERTED, ISDELETED) " + " Select S.Id,FM.Id,0,1,0 from INGREDIENT FM Cross join STORE S Where S.Id = " + MaxId; result = con.Execute(query, storeModel, sqltrans, 0, System.Data.CommandType.Text); //CREATE ENTRY INTO INVENTORY WITH ALL ASSETITEM WITH STOCKQTY AS 0.00 query = " INSERT INTO INVENTORY(STOREID, ASSETITEMid, STOCKQTY, USERIDINSERTED, ISDELETED) " + " Select S.Id,FM.Id,0,1,0 from ASSETITEM FM Cross join STORE S Where S.Id = " + MaxId; result = con.Execute(query, storeModel, sqltrans, 0, System.Data.CommandType.Text); string output = commonRepository.SyncTableStatus("Store"); output = commonRepository.SyncTableStatus("INVENTORY"); } else { sqltrans.Rollback(); } } return(result); }
public int UpdateBank(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); } con.Open(); SqlTransaction sqltrans = con.BeginTransaction(); var query = "UPDATE Bank SET BankName =@BankName," + "AccountName = @AccountName, " + "AccountNumber = @AccountNumber," + "Branch =@Branch," + "SignaturePicture =@SignaturePicture " + "WHERE Id = @Id;"; 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 UpdateFoodMenuRateList(List <FoodMenuRate> foodMenuRates) { int result = 0; List <FoodMenuRate> foodMenuRate = new List <FoodMenuRate>(); using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString)) { CommonRepository commonRepository = new CommonRepository(_ConnectionString); con.Open(); SqlTransaction sqltrans = con.BeginTransaction(); foreach (var item in foodMenuRates) { var query = $"Update FoodMenuRate set SalesPrice=@SalesPrice,IsActive=@IsActive,UserIdUpdated=" + LoginInfo.Userid + ", DateUpdated=GetutcDate() where id=@Id"; result = con.Execute(query, item, sqltrans, 0, System.Data.CommandType.Text); } if (result > 0) { sqltrans.Commit(); string output = commonRepository.SyncTableStatus("FoodMenuRate"); } else { sqltrans.Rollback(); } } return(result); }
public int UpdateIngredientCategory(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); } con.Open(); SqlTransaction sqltrans = con.BeginTransaction(); var query = "UPDATE IngredientCategory SET IngredientCategoryName =@IngredientCategoryName," + "RawMaterialId=@RawMaterialId,Notes = @Notes, " + "IsActive = @IsActive " + "WHERE Id = @Id;"; 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 UpdateTax(TaxModel taxModel) { int result = 0; using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString)) { CommonRepository commonRepository = new CommonRepository(_ConnectionString); result = commonRepository.GetValidateUnique("Tax", "TaxName", taxModel.TaxName, taxModel.Id.ToString()); if (result > 0) { return(-1); } con.Open(); SqlTransaction sqltrans = con.BeginTransaction(); var query = "UPDATE Tax SET TaxName =@TaxName," + "TaxPercentage = @TaxPercentage, " + "TaxType = @TaxType, " + "UserIdUpdated = " + LoginInfo.Userid + ",DateUpdated = GetUtcDate() " + "WHERE Id = @Id;"; result = con.Execute(query, taxModel, sqltrans, 0, System.Data.CommandType.Text); if (result > 0) { sqltrans.Commit(); string output = commonRepository.SyncTableStatus("Tax"); } else { sqltrans.Rollback(); } } return(result); }
public int UpdateRolePermissionList(List <WebRolePageModel> webRolePageModels) { int result = 0; using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString)) { CommonRepository commonRepository = new CommonRepository(_ConnectionString); con.Open(); SqlTransaction sqltrans = con.BeginTransaction(); foreach (var item in webRolePageModels) { var query = string.Empty; if (item.Id != 0) { query = $"Update WebRolePages set WebRolesId=@WebRolesId,PagesId=@PagesId,[Add]=@Add,Edit=@Edit,[Delete]=@Delete,[View]=@View,UserIdUpdated=" + LoginInfo.Userid + ", DateUpdated=GetutcDate() where id=@Id"; } else { query = " INSERT INTO WebRolePages (WebRolesId,PagesId,[Add],[Edit],[Delete],[View],[UserIdInserted],[DateInserted],[IsDeleted]) " + "values(@WebRolesId,@PagesId,@Add,@Edit,@Delete,@View," + LoginInfo.Userid + ",GetUtcDate(),0)"; } result = con.Execute(query, item, sqltrans, 0, System.Data.CommandType.Text); } if (result > 0) { sqltrans.Commit(); string output = commonRepository.SyncTableStatus("WebRolePages"); } else { sqltrans.Rollback(); } } return(result); }
public int InsertPaymentMethod(PaymentMethodModel PaymentMethodModel) { int result = 0; using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString)) { CommonRepository commonRepository = new CommonRepository(_ConnectionString); result = commonRepository.GetValidateUnique("PaymentMethod", "PaymentMethodName", PaymentMethodModel.PaymentMethodName, PaymentMethodModel.Id.ToString()); if (result > 0) { return(-1); } int MaxId = commonRepository.GetMaxId("PaymentMethod"); con.Open(); SqlTransaction sqltrans = con.BeginTransaction(); var query = "INSERT INTO PaymentMethod (Id,PaymentMethodName,IsBank,IsIntegration,IsActive)" + "VALUES (" + MaxId + ",@PaymentMethodName, @IsBank,@IsIntegration,@IsActive); SELECT CAST(SCOPE_IDENTITY() as INT);"; result = con.Execute(query, PaymentMethodModel, sqltrans, 0, System.Data.CommandType.Text); if (result > 0) { sqltrans.Commit(); string output = commonRepository.SyncTableStatus("PaymentMethod"); } else { sqltrans.Rollback(); } } return(result); }
public int UpdateRawMaterial(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); } con.Open(); SqlTransaction sqltrans = con.BeginTransaction(); var query = "UPDATE RawMaterial SET RawMaterialName =@RawMaterialName," + "Notes = @Notes, " + "IsActive = @IsActive, " + "UserIdUpdated = " + LoginInfo.Userid + ",DateUpdated = GetUtcDate() " + "WHERE Id = @Id;"; 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 UpdatePaymentMethod(PaymentMethodModel PaymentMethodModel) { int result = 0; using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString)) { CommonRepository commonRepository = new CommonRepository(_ConnectionString); result = commonRepository.GetValidateUnique("PaymentMethod", "PaymentMethodName", PaymentMethodModel.PaymentMethodName, PaymentMethodModel.Id.ToString()); if (result > 0) { return(-1); } con.Open(); SqlTransaction sqltrans = con.BeginTransaction(); var query = "UPDATE PaymentMethod SET PaymentMethodName =@PaymentMethodName, IsBank=@IsBank,IsIntegration=@IsIntegration," + "IsActive = @IsActive " + "WHERE Id = @Id;"; result = con.Execute(query, PaymentMethodModel, sqltrans, 0, System.Data.CommandType.Text); if (result > 0) { sqltrans.Commit(); string output = commonRepository.SyncTableStatus("PaymentMethod"); } else { sqltrans.Rollback(); } } return(result); }
public int UpdateVarient(VarientModel varientModel) { int result = 0; using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString)) { CommonRepository commonRepository = new CommonRepository(_ConnectionString); con.Open(); SqlTransaction sqltrans = con.BeginTransaction(); var query = "UPDATE Varient SET VarientName =@VarientName,FoodMenuId=@FoodMenuId," + "Price = @Price, " + "IsActive = @IsActive " + "WHERE Id = @Id;"; result = con.Execute(query, varientModel, sqltrans, 0, System.Data.CommandType.Text); if (result > 0) { sqltrans.Commit(); string output = commonRepository.SyncTableStatus("Varient"); } else { sqltrans.Rollback(); } } return(result); }
public int InsertTables(TablesModel TablesModel) { int result = 0; using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString)) { TablesModel.Status = (TablesModel.Status == 0) ? null : TablesModel.Status; CommonRepository commonRepository = new CommonRepository(_ConnectionString); int MaxId = commonRepository.GetMaxId("Tables"); con.Open(); SqlTransaction sqltrans = con.BeginTransaction(); var query = "INSERT INTO Tables (Id,TableName,OutletId,PersonCapacity,TableIcon,Status,IsActive)" + "VALUES ( " + MaxId + ",@TableName,@OutletId,@PersonCapacity,@TableIcon,@Status,@IsActive);" + " SELECT CAST(SCOPE_IDENTITY() as INT);"; result = con.Execute(query, TablesModel, sqltrans, 0, System.Data.CommandType.Text); if (result > 0) { sqltrans.Commit(); string output = commonRepository.SyncTableStatus("Tables"); } else { sqltrans.Rollback(); } } return(result); }
public int UpdateTables(TablesModel TablesModel) { int result = 0; using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString)) { CommonRepository commonRepository = new CommonRepository(_ConnectionString); con.Open(); SqlTransaction sqltrans = con.BeginTransaction(); var query = "UPDATE Tables SET TableName=@TableName,OutletId=@OutletId," + "PersonCapacity=@PersonCapacity,TableIcon=@TableIcon,Status=@Status,IsActive=@IsActive " + "WHERE Id = @Id;"; result = con.Execute(query, TablesModel, sqltrans, 0, System.Data.CommandType.Text); if (result > 0) { sqltrans.Commit(); string output = commonRepository.SyncTableStatus("Tables"); } else { sqltrans.Rollback(); } } return(result); }
public int UpdateUser(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); } con.Open(); SqlTransaction sqltrans = con.BeginTransaction(); //LastLogin=@LastLogin,LastLogout=@LastLogout, var query = "UPDATE [User] SET EmployeeId=@EmployeeId,OutletId=@OutletId,Username=@Username,Password=@Password,ThumbToken=@ThumbToken," + "RoleTypeId=@RoleTypeId,IPAdress=@IPAdress,Counter=@Counter,IsActive=@IsActive,WebRoleId=@WebRoleId " + "WHERE Id = @Id;"; 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 InsertTax(TaxModel taxModel) { int result = 0; using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString)) { CommonRepository commonRepository = new CommonRepository(_ConnectionString); result = commonRepository.GetValidateUnique("Tax", "TaxName", taxModel.TaxName, taxModel.Id.ToString()); if (result > 0) { return(-1); } int MaxId = commonRepository.GetMaxId("Tax"); con.Open(); SqlTransaction sqltrans = con.BeginTransaction(); var query = "INSERT INTO Tax (Id,TaxName,TaxPercentage,TaxType," + "UserIdInserted,DateInserted,IsDeleted)" + "VALUES (" + MaxId + ",@TaxName,@TaxPercentage,@TaxType," + +LoginInfo.Userid + ",GetUtcDate(),0); SELECT CAST(SCOPE_IDENTITY() as INT);"; result = con.Execute(query, taxModel, sqltrans, 0, System.Data.CommandType.Text); if (result > 0) { sqltrans.Commit(); string output = commonRepository.SyncTableStatus("Tax"); } else { sqltrans.Rollback(); } return(result); } }
public int UpdateGlobalStatus(GlobalStatusModel GlobalStatusModel) { int result = 0; using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString)) { CommonRepository commonRepository = new CommonRepository(_ConnectionString); result = commonRepository.GetValidateUnique("GlobalStatus", "StatusName", GlobalStatusModel.StatusName, GlobalStatusModel.Id.ToString()); if (result > 0) { return(-1); } con.Open(); SqlTransaction sqltrans = con.BeginTransaction(); var query = "UPDATE GlobalStatus SET ModuleName =@ModuleName," + "StatusName = @StatusName, " + "StatusCode = @StatusCode, " + "UserIdUpdated = " + LoginInfo.Userid + ",DateUpdated = GetUtcDate() " + "WHERE Id = @Id;"; result = con.Execute(query, GlobalStatusModel, sqltrans, 0, System.Data.CommandType.Text); if (result > 0) { sqltrans.Commit(); string output = commonRepository.SyncTableStatus("GlobalStatus"); } else { sqltrans.Rollback(); } } return(result); }
public int UpdateStore(StoreModel storeModel) { int result = 0; using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString)) { CommonRepository commonRepository = new CommonRepository(_ConnectionString); result = commonRepository.GetValidateUnique("Store", "StoreName", storeModel.StoreName, storeModel.Id.ToString()); if (result > 0) { return(-1); } con.Open(); SqlTransaction sqltrans = con.BeginTransaction(); var query = " UPDATE Store SET StoreName =@StoreName," + " Notes=@Notes,IsActive=@IsActive,IsLock=@IsLock " + " WHERE Id = @Id;"; result = con.Execute(query, storeModel, sqltrans, 0, System.Data.CommandType.Text); if (result > 0) { sqltrans.Commit(); string output = commonRepository.SyncTableStatus("Store"); } else { sqltrans.Rollback(); } } return(result); }
public int InsertVarient(VarientModel varientModel) { CommonRepository commonRepository = new CommonRepository(_ConnectionString); int MaxId = commonRepository.GetMaxId("Varient"); int result = 0; using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString)) { con.Open(); SqlTransaction sqltrans = con.BeginTransaction(); var query = "INSERT INTO Varient (Id,VarientName,FoodMenuId," + "Price, " + "IsActive)" + "VALUES (" + MaxId + ",@VarientName,@FoodMenuId," + "@Price," + "@IsActive); SELECT CAST(SCOPE_IDENTITY() as INT);"; result = con.Execute(query, varientModel, sqltrans, 0, System.Data.CommandType.Text); if (result > 0) { sqltrans.Commit(); string output = commonRepository.SyncTableStatus("Varient"); } else { sqltrans.Rollback(); } } 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 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 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 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 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 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); } }