public int UpdateSupplier(SupplierModel supplierModel)
{
int result = 0;
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
result = commonRepository.GetValidateUnique("Supplier", "SupplierName", supplierModel.SupplierName, supplierModel.Id.ToString());
if (result > 0)
{
return(-1);
}
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
var query = "UPDATE Supplier SET SupplierName=@SupplierName,TaxType=@TaxType," +
"SupplierAddress1=@SupplierAddress1, SupplierAddress2=@SupplierAddress2,SupplierPhone=@SupplierPhone," +
"SupplierEmail =@SupplierEmail,SupplierPicture=@SupplierPicture, IsActive=@IsActive, " +
"Vat=@VATNumber,Pin=@PINNumber, City = @City " +
"WHERE Id = @Id;";
result = con.Execute(query, supplierModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
sqltrans.Commit();
}
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 InsertSupplier(SupplierModel supplierModel)
{
int result = 0;
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
result = commonRepository.GetValidateUnique("Supplier", "SupplierName", supplierModel.SupplierName, supplierModel.Id.ToString());
if (result > 0)
{
return(-1);
}
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
var query = "INSERT INTO Supplier (SupplierName,TaxType,SupplierAddress1, SupplierAddress2,SupplierPhone,SupplierEmail,SupplierPicture,City, IsActive,Vat,Pin)" +
"VALUES " +
"(@SupplierName,@TaxType,@SupplierAddress1, @SupplierAddress2,@SupplierPhone,@SupplierEmail,@SupplierPicture,@City, @IsActive,@VATNumber,@PINNumber);" +
" SELECT CAST(SCOPE_IDENTITY() as INT);";
result = con.Execute(query, supplierModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
sqltrans.Commit();
}
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 InsertCustomer(CustomerModel customerModel)
{
int result = 0;
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
result = commonRepository.GetValidateUnique("Customer", "CustomerPhone", customerModel.CustomerPhone, customerModel.Id.ToString());
if (result > 0)
{
return(-1);
}
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
var query = "INSERT INTO Customer (CustomerTypeId, CustomerNumber,CustomerName,CustomerAddress1, CustomerAddress2,CustomerPhone,CustomerEmail,CustomerImage, FavDeliveryAddress,IsActive)" +
"VALUES " +
"(@CustomerTypeId, @CustomerNumber,@CustomerName,@CustomerAddress1, @CustomerAddress2,@CustomerPhone,@CustomerEmail,@CustomerImage, @FavDeliveryAddress,@IsActive);" +
" SELECT CAST(SCOPE_IDENTITY() as INT);";
result = con.Execute(query, customerModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
sqltrans.Commit();
}
else
{
sqltrans.Rollback();
}
}
return(result);
}
public int UpdateCustomer(CustomerModel customerModel)
{
int result = 0;
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
CommonRepository commonRepository = new CommonRepository(_ConnectionString);
result = commonRepository.GetValidateUnique("Customer", "CustomerPhone", customerModel.CustomerPhone, customerModel.Id.ToString());
if (result > 0)
{
return(-1);
}
con.Open();
SqlTransaction sqltrans = con.BeginTransaction();
var query = "UPDATE Customer SET CustomerTypeId=@CustomerTypeId, CustomerNumber=@CustomerNumber,CustomerName=@CustomerName," +
"CustomerAddress1=@CustomerAddress1, CustomerAddress2=@CustomerAddress2,CustomerPhone=@CustomerPhone," +
"CustomerEmail =@CustomerEmail,CustomerImage=@CustomerImage, FavDeliveryAddress=@FavDeliveryAddress, IsActive=@IsActive " +
"WHERE Id = @Id;";
result = con.Execute(query, customerModel, sqltrans, 0, System.Data.CommandType.Text);
if (result > 0)
{
sqltrans.Commit();
}
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 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 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 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 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 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 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 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 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 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 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 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 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);
}
}
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);
}
