public IEnumerable <T> FindAll(List <Dictionary <string, object> > keyValueParam)
{
var result = new List <ProductCategory>();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_ALL_PRODUCT_CATEGORY") as SqlCommand;
RoutinesParameterSetter.Set(ref cmd, keyValueParam);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
var category = new ProductCategory();
category.CategoryID = int.Parse(reader[0].ToString());
category.CategoryName = reader[1].ToString();
category.Active = bool.Parse(reader[2].ToString());
result.Add(category);
}
}
return(result as List <T>);
}
public IEnumerable <T> FindAll(List <Dictionary <string, object> > keyValueParam)
{
var result = new List <UserProfile>();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("aspnet_Membership_GetAllUsers") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@ApplicationName", "/");
DBClass.AddSimpleParameter(cmd, "@PageIndex", 0);
DBClass.AddSimpleParameter(cmd, "@PageSize", 25);
DbDataReader reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
UserProfile profile = UserProfile.GetUserProfile(reader[0].ToString());
profile.UserName = reader[0].ToString();
profile.IsLocked = bool.Parse(reader[10].ToString());
result.Add(profile);
}
}
return(result as IEnumerable <T>);
}
public Dictionary <string, Dictionary <string, string> > GetProductWithSpesificPrice(int custStatusType)
{
var result = new Dictionary <string, Dictionary <string, string> >();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_ALL_PRODUCT_WITH_SPESIFIC_PRICE") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@CustomerStatusType", custStatusType);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
var productID = reader[0].ToString();
var dictionary = new Dictionary <string, string>
{
{ reader[1].ToString(), reader[2].ToString() }
};
result.Add(productID, dictionary);
}
}
return(result);
}
public int SaveDeliveredPurchasedItem(PurchasedItem item, string createdBy)
{
int objID = 0;
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_SAVE_NEW_PURCHASE_ORDER_ITEM_IN") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@PurchaseOrderId", item.PurchaseOrderID);
DBClass.AddSimpleParameter(cmd, "@ProductId", item.ProductID);
DBClass.AddSimpleParameter(cmd, "@Qty", item.Qty);
DBClass.AddSimpleParameter(cmd, "@DeliveredDate", item.DeliveredDate);
DBClass.AddSimpleParameter(cmd, "@Note", item.Note);
DBClass.AddSimpleParameter(cmd, "@CreatedBy", createdBy);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
objID = int.Parse(reader[0].ToString());
}
}
return(objID);
}
public SalesInvoice FindbyId(int id)
{
var salesInvoice = new SalesInvoice();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_SALES_BY_ID") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@SalesInvoiceId", id);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
salesInvoice.InvoiceID = int.Parse(reader[0].ToString());
salesInvoice.VoucherCode = reader[1].ToString();
salesInvoice.DepartementID = int.Parse(reader[2].ToString());
salesInvoice.CustomerName = reader[3].ToString();
salesInvoice.Items = (List <Items>)GetSalesItems(salesInvoice.InvoiceID);
salesInvoice.Payments = (List <Payments>)GetSalesPayment(salesInvoice.InvoiceID);
}
}
return(salesInvoice);
}
public IEnumerable <T> FindAll(List <Dictionary <string, object> > keyValueParam)
{
var result = new List <Marketing>();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_ALL_MARKETING") as SqlCommand;
RoutinesParameterSetter.Set(ref cmd, keyValueParam);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
var _marketing = new Marketing();
_marketing.MarketingID = int.Parse(reader[0].ToString());
_marketing.MarketingName = reader[1].ToString();
_marketing.DepartementName = reader[2].ToString();
_marketing.Active = bool.Parse(reader[3].ToString());
result.Add(_marketing);
}
}
return(result as List <T>);
}
public IEnumerable <T> GetAllDepartementStock(List <Dictionary <string, object> > keyValueParam)
{
var result = new List <ProductStock>();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_ALL_DEPARTEMENT_STOCK") as SqlCommand;
RoutinesParameterSetter.Set(ref cmd, keyValueParam);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
var stock = new ProductStock();
stock.StockID = int.Parse(reader[0].ToString());
stock.ProductID = int.Parse(reader[1].ToString());
stock.ProductCode = reader[2].ToString();
stock.ProductName = reader[3].ToString();
result.Add(stock);
}
}
return(result as List <T>);
}
public T FindbyId(int id)
{
var stock = new ProductStock();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_STOCK_BY_ID") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@StockId", id);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
stock.StockID = int.Parse(reader[0].ToString());
stock.ProductID = int.Parse(reader[1].ToString());
stock.DepartementID = int.Parse(reader[2].ToString());
stock.StartQuantity = decimal.Parse(reader[3].ToString());
stock.CurrentQuantity = decimal.Parse(reader[4].ToString());
stock.MinimumQuantity = decimal.Parse(reader[5].ToString());
}
}
return(stock as T);
}
public IEnumerable <T> FindAll(List <Dictionary <string, object> > keyValueParam)
{
var result = new List <ProductUnit>();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_ALL_PRODUCT_UNIT") as SqlCommand;
RoutinesParameterSetter.Set(ref cmd, keyValueParam);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
var productUnit = new ProductUnit();
productUnit.UnitID = int.Parse(reader[0].ToString());
productUnit.UnitName = reader[1].ToString();
productUnit.Abbreviation = reader[2].ToString();
productUnit.Active = bool.Parse(reader[3].ToString());
result.Add(productUnit);
}
}
return(result as List <T>);
}
public RequestedItem GetRequestedItemOutById(int requestOrderInID)
{
var item = new RequestedItem();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_REQUEST_ORDER_ITEM_OUT_BY_ID") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@RequestOrderOutId", requestOrderInID);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
item.LogObject = new StockLogObject
{
DepartementID = int.Parse(reader[0].ToString()),
ProductID = int.Parse(reader[1].ToString()),
RequestVoucher = reader[2].ToString()
};
}
}
return(item);
}
public Supplier FindbyId(int id)
{
var suplier = new Supplier();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_SUPPLIER_BY_ID") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@SupplierId", id);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
suplier.SupplierID = int.Parse(reader[0].ToString());
suplier.SupplierName = reader[1].ToString();
suplier.Address = reader[2].ToString();
suplier.Phone = reader[3].ToString();
suplier.MobilePhone = reader[4].ToString();
suplier.Active = bool.Parse(reader[5].ToString());
}
}
return(suplier);
}
private void DeleteItemAndPresent(int id)
{
using (DBClass = new MSSQLDatabase())
{
using (DbTransaction txn = DBClass.BeginTransaction())
{
try
{
var cmd = DBClass.GetStoredProcedureCommand("APP_DELETE_PROFORMA_SALES_ITEM_AND_PRESENT") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@ProformaId", id);
DBClass.ExecuteNonQuery(cmd, txn);
txn.Commit();
}
catch (Exception)
{
txn.Rollback();
throw;
}
}
}
}
public T FindbyId(int id)
{
var productionPackaging = new ProductionPackaging();
using (DBClass = new MSSQLDatabase())
{
SqlCommand cmd = DBClass.GetStoredProcedureCommand("APP_GET_PRODUCTION_PACKAGING_BY_ID");
cmd.Parameters.AddWithValue("@ProductionPackagingId", id);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
productionPackaging.ProductionPackagingID = int.Parse(reader[0].ToString());
productionPackaging.VoucherCode = reader[1].ToString();
productionPackaging.DepartementID = int.Parse(reader[2].ToString());
productionPackaging.DestinationProduct = int.Parse(reader[3].ToString());
productionPackaging.DestinationQty = decimal.Parse(reader[4].ToString());
productionPackaging.Note = reader[6].ToString();
productionPackaging.ProductionPackagingDate = DateTime.Parse(reader[7].ToString());
}
}
return(productionPackaging as T);
}
public IEnumerable <RequestedItem> GetRequestedItemOut(int RequestOrderId, int productID)
{
var result = new List <RequestedItem>();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_REQUEST_ORDER_ITEM_OUT") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@RequestOrderId", RequestOrderId);
DBClass.AddSimpleParameter(cmd, "@ProductId", productID);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
var item = new RequestedItem();
item.ItemOutID = int.Parse(reader[0].ToString());
item.DeliverQty = decimal.Parse(reader[1].ToString());
item.DeliverDate = DateTime.Parse(reader[2].ToString());
item.Note = reader[3].ToString();
result.Add(item);
}
}
return(result);
}
public IEnumerable <Supplier> FindAll(List <Dictionary <string, object> > keyValueParam)
{
var result = new List <Supplier>();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_ALL_SUPPLIER") as SqlCommand;
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
var suplier = new Supplier();
suplier.SupplierID = int.Parse(reader[0].ToString());
suplier.SupplierName = reader[1].ToString();
suplier.Address = reader[2].ToString();
suplier.Phone = reader[3].ToString();
suplier.MobilePhone = reader[4].ToString();
suplier.Active = bool.Parse(reader[5].ToString());
result.Add(suplier);
}
}
return(result);
}
public T FindbyId(int id)
{
var purchaseOrder = new PurchaseOrder();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_PURCHASE_ORDER_BY_ID") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@PurchaseOrderId", id);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
purchaseOrder.PurchaseOrderID = int.Parse(reader[0].ToString());
purchaseOrder.VoucherCode = reader[1].ToString();
purchaseOrder.PurchaseDate = DateTime.Parse(reader[2].ToString());
purchaseOrder.DepartementID = int.Parse(reader[3].ToString());
purchaseOrder.SupplierID = int.Parse(reader[4].ToString());
purchaseOrder.SupplierName = reader[5].ToString();
purchaseOrder.Note = reader[6].ToString();
}
}
return(purchaseOrder as T);
}
public IEnumerable <T> FindAll(List <Dictionary <string, object> > keyValueParam)
{
var result = new List <Production>();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_ALL_PRODUCTION") as SqlCommand;
RoutinesParameterSetter.Set(ref cmd, keyValueParam);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
var production = new Production();
production.ProductionID = int.Parse(reader[0].ToString());
production.VoucherCode = reader[1].ToString();
production.ProductionDate = DateTime.Parse(reader[2].ToString());
production.DepartementName = reader[3].ToString();
production.Note = reader[4].ToString();
result.Add(production);
}
}
return(result as List <T>);
}
public PurchasedItem GetPurchasedItemInByID(int purchaseOrderInId)
{
var item = new PurchasedItem();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_PURCHASE_ORDER_ITEM_IN_BY_ID") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@PurchaseOrderInId", purchaseOrderInId);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
item.LogObject = new StockLogObject
{
DepartementID = int.Parse(reader[0].ToString()),
ProductID = int.Parse(reader[1].ToString()),
PurchaseVoucher = reader[2].ToString(),
CreatedDate = DateTime.Parse(reader[3].ToString())
};
}
}
return(item);
}
public IEnumerable <T> DashboardCreditPassAlert(List <Dictionary <string, object> > keyValueParam)
{
var result = new List <Customer>();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_ALL_CUSTOMER_LIMITCREDIT_PASS_ALERT") as SqlCommand;
RoutinesParameterSetter.Set(ref cmd, keyValueParam);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
var customer = new Customer();
customer.DepartementName = reader[0].ToString();
customer.FirstName = StringManipulation.UppercaseFirst(reader[1].ToString());
customer.LastName = StringManipulation.UppercaseFirst(reader[2].ToString());
customer.CreditLimit = decimal.Parse(reader[3].ToString());
customer.CreditTotal = decimal.Parse(reader[4].ToString());
result.Add(customer);
}
}
return(result as List <T>);
}
public int SaveRow(T param, string createdBy)
{
int objID = 0;
using (DBClass = new MSSQLDatabase())
{
SqlCommand cmd = DBClass.GetStoredProcedureCommand("APP_SAVE_NEW_JOB_COSTING");
RoutinesParameterSetter.Set(ref cmd, param, CRUDType.Insert);
cmd.Parameters.AddWithValue("@CreatedBy", createdBy);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
objID = int.Parse(reader[0].ToString());
}
var listMaterial = (param as JobCosting).Materials;
foreach (var material in listMaterial)
{
SaveRawMaterial(objID, material);
}
}
return(objID);
}
public IEnumerable <T> DashboardMinimumStockAlert(List <Dictionary <string, object> > keyValueParam)
{
var result = new List <ProductStock>();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_ALL_STOCK_MINIMUM_ALERT") as SqlCommand;
RoutinesParameterSetter.Set(ref cmd, keyValueParam);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
var stock = new ProductStock();
stock.DepartementName = reader[0].ToString();
stock.ProductCode = reader[1].ToString();
stock.ProductName = reader[2].ToString();
stock.MinimumQuantity = decimal.Parse(reader[3].ToString());
stock.CurrentQuantity = decimal.Parse(reader[4].ToString());
result.Add(stock);
}
}
return(result as List <T>);
}
public IEnumerable <T> FindAll(List <Dictionary <string, object> > keyValueParam)
{
var result = new List <Notification>();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_NOTIFICATIONS") as SqlCommand;
RoutinesParameterSetter.Set(ref cmd, keyValueParam);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
var notification = new Notification();
notification.DepartementId = int.Parse(reader[0].ToString());
notification.VoucherCode = reader[1].ToString();
notification.Note = reader[2].ToString();
notification.Url = reader[3].ToString();
notification.CreatedDate = reader[4].ToString();
result.Add(notification);
}
}
return(result as List <T>);
}
public int DeleteSalesCreditPayment(int creditPaymentID)
{
int result = 0;
try
{
using (DBClass = new MSSQLDatabase())
{
using (var txn = (SqlTransaction)DBClass.BeginTransaction())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_DELETE_SALES_CREDIT_PAYMENT") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@SalesCreditPaymentId", creditPaymentID);
DBClass.ExecuteNonQuery(cmd, txn);
txn.Commit();
}
}
}
catch (Exception)
{
result = 1;
}
return(result);
}
public void SaveEDCPayment(CreditPayment payment, string createdBy)
{
using (DBClass = new MSSQLDatabase())
{
using (DbTransaction txn = DBClass.BeginTransaction())
{
try
{
var cmd = DBClass.GetStoredProcedureCommand("APP_EDC_CREDIT_PAYMENT") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@SalesCreditId", payment.SalesCreditID);
DBClass.AddSimpleParameter(cmd, "@CreatedBy", createdBy);
DBClass.AddSimpleParameter(cmd, "@PaymentDate", payment.CreditPaymentDate);
DBClass.ExecuteNonQuery(cmd, txn);
txn.Commit();
}
catch (Exception)
{
txn.Rollback();
throw;
}
}
}
}
public IEnumerable <T> FindAllBankIncome(List <Dictionary <string, object> > keyValueParam)
{
var result = new List <Income>();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_ALL_BANK_INCOME") as SqlCommand;
RoutinesParameterSetter.Set(ref cmd, keyValueParam);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
var income = new Income();
income.IncomeID = int.Parse(reader[0].ToString());
income.VoucherCode = reader[1].ToString();
income.IncomeDate = DateTime.Parse(reader[2].ToString());
income.Description = reader[3].ToString();
income.Nominal = decimal.Parse(reader[4].ToString());
income.Note = reader[5].ToString();
result.Add(income);
}
}
return(result as List <T>);
}
private void SaveResult(int id, ProductionResult result)
{
using (DBClass = new MSSQLDatabase())
{
using (DbTransaction txn = DBClass.BeginTransaction())
{
try
{
var cmd = DBClass.GetStoredProcedureCommand("APP_SAVE_NEW_PRODUCTION_OUTPUT") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@ProductionId", id);
DBClass.AddSimpleParameter(cmd, "@ProductId", result.ProductID);
DBClass.AddSimpleParameter(cmd, "@Qty", result.Qty);
DBClass.ExecuteNonQuery(cmd, txn);
txn.Commit();
}
catch (Exception)
{
txn.Rollback();
throw;
}
}
}
}
private void SaveSalesCredit(int id, decimal grandTotal, DateTime creditDate)
{
using (DBClass = new MSSQLDatabase())
{
using (DbTransaction txn = DBClass.BeginTransaction())
{
try
{
var cmd = DBClass.GetStoredProcedureCommand("APP_SAVE_NEW_SALES_CREDIT") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@SalesId", id);
DBClass.AddSimpleParameter(cmd, "@GrandTotal", grandTotal);
DBClass.AddSimpleParameter(cmd, "@CreditDate", creditDate);
DBClass.ExecuteNonQuery(cmd, txn);
txn.Commit();
}
catch (Exception)
{
txn.Rollback();
throw;
}
}
}
}
public int DeletePurchaseOrderIn(int purchaseOrderInId)
{
int result = 0;
try
{
using (DBClass = new MSSQLDatabase())
{
using (var txn = (SqlTransaction)DBClass.BeginTransaction())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_DELETE_PURCHASE_ORDER_ITEM_IN") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@PurchaseOrderInId", purchaseOrderInId);
DBClass.ExecuteNonQuery(cmd, txn);
txn.Commit();
}
}
}
catch (Exception)
{
result = 1;
}
return(result);
}
public T FindbyId(int id)
{
var bankAccount = new BankAccount();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_BANK_ACCOUNT") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@BankAccountId", id);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
bankAccount.BankAccountID = int.Parse(reader[0].ToString());
bankAccount.DepartementID = int.Parse(reader[1].ToString());
bankAccount.BankID = int.Parse(reader[2].ToString());
bankAccount.AccountNumber = reader[3].ToString();
bankAccount.AccountName = reader[4].ToString();
bankAccount.StartingBalance = decimal.Parse(reader[5].ToString());
bankAccount.CurrentBalance = decimal.Parse(reader[6].ToString());
bankAccount.Active = bool.Parse(reader[7].ToString());
}
}
return(bankAccount as T);
}
public IEnumerable <T> FindAll(List <Dictionary <string, object> > keyValueParam)
{
var result = new List <PurchaseOrder>();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_ALL_PURCHASE_ORDER") as SqlCommand;
RoutinesParameterSetter.Set(ref cmd, keyValueParam);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
var purchaseOrder = new PurchaseOrder();
purchaseOrder.PurchaseOrderID = int.Parse(reader[0].ToString());
purchaseOrder.VoucherCode = reader[1].ToString();
purchaseOrder.PurchaseDate = DateTime.Parse(reader[2].ToString());
purchaseOrder.DepartementName = reader[3].ToString();
purchaseOrder.SupplierName = reader[4].ToString();
purchaseOrder.Note = reader[5].ToString();
result.Add(purchaseOrder);
}
}
return(result as List <T>);
}
