public DataSet GetMoneyFlowForExcell(int departementId, DateTime printDate, DateTime dateStart, DateTime dateEnd)
{
DataSet dataSetResult = new DataSet();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_HEADER") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", departementId);
DBClass.AddSimpleParameter(cmd, "@DateStart", dateStart);
DBClass.AddSimpleParameter(cmd, "@DateEnd", dateEnd);
DBClass.AddSimpleParameter(cmd, "@PrintDate", printDate);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
adapter.Fill(table);
dataSetResult.Tables.Add(table);
}
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_ALL_MONEY") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", departementId);
DBClass.AddSimpleParameter(cmd, "@DateStart", dateStart);
DBClass.AddSimpleParameter(cmd, "@DateEnd", dateEnd);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
adapter.Fill(table);
dataSetResult.Tables.Add(table);
}
return(dataSetResult);
}
private void SaveItem(int id, Items item)
{
using (DBClass = new MSSQLDatabase())
{
using (DbTransaction txn = DBClass.BeginTransaction())
{
try
{
var cmd = DBClass.GetStoredProcedureCommand("APP_SAVE_NEW_PROFORMA_ITEM") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@ProformaId", id);
DBClass.AddSimpleParameter(cmd, "@ProductId", item.ProductID);
DBClass.AddSimpleParameter(cmd, "@Qty", item.Qty);
DBClass.AddSimpleParameter(cmd, "@Price", item.Price);
DBClass.AddSimpleParameter(cmd, "@SubTotal", item.SubTotal);
DBClass.ExecuteNonQuery(cmd, txn);
txn.Commit();
}
catch (Exception)
{
txn.Rollback();
throw;
}
}
}
}
private void SavePurchasedItem(int id, PurchasedItem item)
{
using (DBClass = new MSSQLDatabase())
{
using (DbTransaction txn = DBClass.BeginTransaction())
{
try
{
var cmd = DBClass.GetStoredProcedureCommand("APP_SAVE_NEW_PURCHASE_ORDER_ITEM") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@PurchaseOrderId", id);
DBClass.AddSimpleParameter(cmd, "@ProductId", item.ProductID);
DBClass.AddSimpleParameter(cmd, "@Qty", item.Qty);
DBClass.AddSimpleParameter(cmd, "@EstimatedDate", item.EstimatedDate);
DBClass.AddSimpleParameter(cmd, "@DeliveredStatus", 1);
DBClass.ExecuteNonQuery(cmd, txn);
txn.Commit();
}
catch (Exception)
{
txn.Rollback();
throw;
}
}
}
}
public DataSet[] GetSalesByMarketingReportData(int departementId, int marketingId, DateTime printDate, DateTime dateStart, DateTime dateEnd)
{
DataSet[] dataSetArray = new DataSet[2];
DataSet dataSetResult;
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_GET_SALES_BY_HEADER") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", departementId);
DBClass.AddSimpleParameter(cmd, "@DateStart", dateStart);
DBClass.AddSimpleParameter(cmd, "@DateEnd", dateEnd);
DBClass.AddSimpleParameter(cmd, "@PrintDate", printDate);
var adapter = new SqlDataAdapter(cmd);
dataSetResult = new DataSet();
adapter.Fill(dataSetResult, "SalesHeader");
dataSetArray[0] = dataSetResult;
}
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_SALES_BY_OUTLET_MARKETING") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", departementId);
DBClass.AddSimpleParameter(cmd, "@MarketingId", marketingId);
DBClass.AddSimpleParameter(cmd, "@DateStart", dateStart);
DBClass.AddSimpleParameter(cmd, "@DateEnd", dateEnd);
var adapter = new SqlDataAdapter(cmd);
dataSetResult = new DataSet();
adapter.Fill(dataSetResult, "SalesByMarketing");
dataSetArray[1] = dataSetResult;
}
return(dataSetArray);
}
public DataSet[] GetProductStockReportData(int departementId, int productId, DateTime date, DateTime printDate)
{
DataSet[] dataSetArray = new DataSet[2];
DataSet dataSetResult;
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_PRODUCT_STOCK_HEADER") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", departementId);
DBClass.AddSimpleParameter(cmd, "@PrintDate", printDate);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
dataSetResult = new DataSet();
adapter.Fill(dataSetResult, "FlowHeader");
dataSetArray[0] = dataSetResult;
}
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_PRODUCT_STOCK_DATA") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", departementId);
DBClass.AddSimpleParameter(cmd, "@ProductId", productId);
DBClass.AddSimpleParameter(cmd, "@ByPassProduct", productId == 0 ? 1 : 0);
DBClass.AddSimpleParameter(cmd, "@Date", date);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
dataSetResult = new DataSet();
adapter.Fill(dataSetResult, "ProductStock");
dataSetArray[1] = dataSetResult;
}
return(dataSetArray);
}
public DataSet GetCustomerControlForExcell(int departementId, int customerId, string month, string year, DateTime startingDate, DateTime printDate)
{
DataSet dataSetResult = new DataSet();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("KARTU_DOKTER_HEADER_EXCELL") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", departementId);
DBClass.AddSimpleParameter(cmd, "@CustomerId", customerId);
DBClass.AddSimpleParameter(cmd, "@Month", month);
DBClass.AddSimpleParameter(cmd, "@Year", year);
DBClass.AddSimpleParameter(cmd, "@PrintDate", printDate);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
adapter.Fill(table);
dataSetResult.Tables.Add(table);
}
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("KARTUDOKTER") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@startingDate", startingDate);
DBClass.AddSimpleParameter(cmd, "@CustomerId", customerId);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
adapter.Fill(table);
dataSetResult.Tables.Add(table);
}
return(dataSetResult);
}
public DataSet[] GetCustomerControlReportData(int departementId, int customerId, string month, string year, DateTime startingDate, DateTime printDate)
{
DataSet[] dataSetArray = new DataSet[2];
DataSet dataSetResult;
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("KARTU_DOKTER_HEADER") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", departementId);
DBClass.AddSimpleParameter(cmd, "@CustomerId", customerId);
DBClass.AddSimpleParameter(cmd, "@Month", month);
DBClass.AddSimpleParameter(cmd, "@Year", year);
DBClass.AddSimpleParameter(cmd, "@PrintDate", printDate);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
dataSetResult = new DataSet();
adapter.Fill(dataSetResult, "KartuDokterHeader");
dataSetArray[0] = dataSetResult;
}
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("KARTUDOKTER") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@startingDate", startingDate);
DBClass.AddSimpleParameter(cmd, "@CustomerId", customerId);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
dataSetResult = new DataSet();
adapter.Fill(dataSetResult, "KartuDokter");
dataSetArray[1] = dataSetResult;
}
return(dataSetArray);
}
public DataSet[] GetStockAdjustmentReportData(int departementId, DateTime printDate, DateTime dateStart, DateTime dateEnd)
{
DataSet[] dataSetArray = new DataSet[2];
DataSet dataSetResult;
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_ADJUSTMENT_REPORT_HEADER") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", departementId);
DBClass.AddSimpleParameter(cmd, "@DateStart", dateStart);
DBClass.AddSimpleParameter(cmd, "@DateEnd", dateEnd);
DBClass.AddSimpleParameter(cmd, "@PrintDate", printDate);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
dataSetResult = new DataSet();
adapter.Fill(dataSetResult, "AdjustmentHeader");
dataSetArray[0] = dataSetResult;
}
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_STOCK_ADJUSTMENT_DATA") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", departementId);
DBClass.AddSimpleParameter(cmd, "@DateStart", dateStart);
DBClass.AddSimpleParameter(cmd, "@DateEnd", dateEnd);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
dataSetResult = new DataSet();
adapter.Fill(dataSetResult, "AdjustmentList");
dataSetArray[1] = dataSetResult;
}
return(dataSetArray);
}
public DataSet[] GetReportData(DateTime startingDate, DateTime endDate, DateTime printDate)
{
DataSet[] dataSetArray = new DataSet[2];
DataSet dataSetResult;
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_DEPOSIT_SALES_HISTORY_HEADER") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DateStart", startingDate);
DBClass.AddSimpleParameter(cmd, "@DateEnd", endDate);
DBClass.AddSimpleParameter(cmd, "@PrintDate", printDate);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
dataSetResult = new DataSet();
adapter.Fill(dataSetResult, "DepositSalesHistoryHeader");
dataSetArray[0] = dataSetResult;
}
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_DEPOSIT_SALES_HISTORY") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DateStart", startingDate);
DBClass.AddSimpleParameter(cmd, "@DateEnd", endDate);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
dataSetResult = new DataSet();
adapter.Fill(dataSetResult, "DepositSalesHistory");
dataSetArray[1] = dataSetResult;
}
return(dataSetArray);
}
public DataSet GetSalesByMarketingForExcell(int departementId, int marketingId, DateTime printDate, DateTime dateStart, DateTime dateEnd)
{
var dataSetResult = new DataSet();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_GET_SALES_BY_MARKETING_HEADER") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", departementId);
DBClass.AddSimpleParameter(cmd, "@MarketingId", marketingId);
DBClass.AddSimpleParameter(cmd, "@DateStart", dateStart);
DBClass.AddSimpleParameter(cmd, "@DateEnd", dateEnd);
DBClass.AddSimpleParameter(cmd, "@PrintDate", printDate);
var adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
adapter.Fill(table);
dataSetResult.Tables.Add(table);
}
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_SALES_BY_OUTLET_MARKETING_EXCELL") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", departementId);
DBClass.AddSimpleParameter(cmd, "@MarketingId", marketingId);
DBClass.AddSimpleParameter(cmd, "@DateStart", dateStart);
DBClass.AddSimpleParameter(cmd, "@DateEnd", dateEnd);
var adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
adapter.Fill(table);
dataSetResult.Tables.Add(table);
}
return(dataSetResult);
}
public DataSet GetBankFlowExcell(int departementId, int cashBankId, DateTime printDate, DateTime dateStart, DateTime dateEnd)
{
DataSet dataSetResult = new DataSet();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_BANKFLOW_REPORT_HEADER_EXCELL") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", departementId);
DBClass.AddSimpleParameter(cmd, "@BankAccountId", cashBankId);
DBClass.AddSimpleParameter(cmd, "@DateStart", dateStart);
DBClass.AddSimpleParameter(cmd, "@DateEnd", dateEnd);
DBClass.AddSimpleParameter(cmd, "@PrintDate", printDate);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
adapter.Fill(table);
dataSetResult.Tables.Add(table);
}
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_BANKFLOW_REPORT_DATA_EXCELL") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", departementId);
DBClass.AddSimpleParameter(cmd, "@BankAccountId", cashBankId);
DBClass.AddSimpleParameter(cmd, "@DateStart", dateStart);
DBClass.AddSimpleParameter(cmd, "@DateEnd", dateEnd);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
adapter.Fill(table);
dataSetResult.Tables.Add(table);
}
return(dataSetResult);
}
public DataSet[] GetBankFlowReportData(int departementId, int cashBankId, DateTime printDate, DateTime dateStart, DateTime dateEnd)
{
DataSet[] dataSetArray = new DataSet[2];
DataSet dataSetResult;
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_BANKFLOW_REPORT_HEADER") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", departementId);
DBClass.AddSimpleParameter(cmd, "@BankAccountId", cashBankId);
DBClass.AddSimpleParameter(cmd, "@DateStart", dateStart);
DBClass.AddSimpleParameter(cmd, "@DateEnd", dateEnd);
DBClass.AddSimpleParameter(cmd, "@PrintDate", printDate);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
dataSetResult = new DataSet();
adapter.Fill(dataSetResult, "BankFlowHeader");
dataSetArray[0] = dataSetResult;
}
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_BANKFLOW_REPORT_DATA") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", departementId);
DBClass.AddSimpleParameter(cmd, "@BankAccountId", cashBankId);
DBClass.AddSimpleParameter(cmd, "@DateStart", dateStart);
DBClass.AddSimpleParameter(cmd, "@DateEnd", dateEnd);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
dataSetResult = new DataSet();
adapter.Fill(dataSetResult, "FlowDetail");
dataSetArray[1] = dataSetResult;
}
return(dataSetArray);
}
public DataSet[] GetReportData(int salesId, string inWord)
{
var dataSetArray = new DataSet[3];
DataSet dataSetResult;
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_GET_SALES_INVOICE_DATA") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@InvoiceId", salesId);
DBClass.AddSimpleParameter(cmd, "@InWord", inWord);
var adapter = new SqlDataAdapter(cmd);
dataSetResult = new DataSet();
adapter.Fill(dataSetResult, "SalesInvoiceReport");
dataSetArray[0] = dataSetResult;
}
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_GET_SALES_INVOICE_ITEM_AND_PRESENT_DATA") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@InvoiceId", salesId);
var adapter = new SqlDataAdapter(cmd);
dataSetResult = new DataSet();
adapter.Fill(dataSetResult, "SalesInvoiceReportDetail");
dataSetArray[1] = dataSetResult;
}
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_GET_SALES_INVOICE_PAYMENTS") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@InvoiceId", salesId);
var adapter = new SqlDataAdapter(cmd);
dataSetResult = new DataSet();
adapter.Fill(dataSetResult, "SalesInvoicePayments");
dataSetArray[2] = dataSetResult;
}
return(dataSetArray);
}
public DataSet[] GetUsedSupplyReportData(int departementId, DateTime startingDate, DateTime endDate, DateTime printDate)
{
DataSet[] dataSetArray = new DataSet[2];
DataSet dataSetResult;
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_PRODUCTION_HEADER") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", departementId);
DBClass.AddSimpleParameter(cmd, "@DateStart", startingDate);
DBClass.AddSimpleParameter(cmd, "@DateEnd", endDate);
DBClass.AddSimpleParameter(cmd, "@PrintDate", printDate);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
dataSetResult = new DataSet();
adapter.Fill(dataSetResult, "ProductionHeader");
dataSetArray[0] = dataSetResult;
}
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_SUPPLIES_USING_ITEMS") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", departementId);
DBClass.AddSimpleParameter(cmd, "@DateStart", startingDate);
DBClass.AddSimpleParameter(cmd, "@DateEnd", endDate);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
dataSetResult = new DataSet();
adapter.Fill(dataSetResult, "ProductionDetail");
dataSetArray[1] = dataSetResult;
}
return(dataSetArray);
}
public DataSet GetProductStockForExcell(int departementId, int productId, DateTime date, DateTime printDate)
{
DataSet dataSetResult = new DataSet();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_PRODUCT_STOCK_HEADER_EXCELL") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", departementId);
DBClass.AddSimpleParameter(cmd, "@PrintDate", printDate);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
adapter.Fill(table);
dataSetResult.Tables.Add(table);
}
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_PRODUCT_STOCK_DATA") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", departementId);
DBClass.AddSimpleParameter(cmd, "@ProductId", productId);
DBClass.AddSimpleParameter(cmd, "@ByPassProduct", productId == 0 ? 1 : 0);
DBClass.AddSimpleParameter(cmd, "@Date", date);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
adapter.Fill(table);
dataSetResult.Tables.Add(table);
}
return(dataSetResult);
}
public DataSet[] GetReportData(int proformaId, string inWord)
{
DataSet[] dataSetArray = new DataSet[2];
DataSet dataSetResult;
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_GET_PROFORMA_INVOICE_DATA") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@ProformaInvoiceId", proformaId);
DBClass.AddSimpleParameter(cmd, "@InWord", inWord);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
dataSetResult = new DataSet();
adapter.Fill(dataSetResult, "InvoiceReport");
dataSetArray[0] = dataSetResult;
}
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_GET_PROFORMA_INVOICE_ITEM_AND_PRESENT_DATA") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@ProformaInvoiceId", proformaId);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
dataSetResult = new DataSet();
adapter.Fill(dataSetResult, "InvoiceReportDetail");
dataSetArray[1] = dataSetResult;
}
return(dataSetArray);
}
public override void DeleteBankCRUDLog(BankLogObject logObject)
{
using (DBClass = new MSSQLDatabase())
{
using (DbTransaction txn = DBClass.BeginTransaction())
{
try
{
var cmd = DBClass.GetStoredProcedureCommand("DELETE_BANKFLOW") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@CashBankId", logObject.CashBankID);
DBClass.AddSimpleParameter(cmd, "@SalesVoucher", logObject.SalesVoucher);
var affectedRows = DBClass.ExecuteNonQuery(cmd, txn);
if (affectedRows == 0)
{
throw new Exception("Hapus log gagal");
}
txn.Commit();
}
catch (Exception)
{
txn.Rollback();
throw;
}
}
}
}
public override void DeleteStockCRUDLog(CRUDLogObject logObject)
{
var obj = logObject as StockLogObject;
using (DBClass = new MSSQLDatabase())
{
using (var txn = DBClass.BeginTransaction())
{
try
{
var cmd = DBClass.GetStoredProcedureCommand("DELETE_STOCKFLOW_PO") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@ProductId", obj.ProductID);
DBClass.AddSimpleParameter(cmd, "@DepartementId", obj.DepartementID);
DBClass.AddSimpleParameter(cmd, "@PurchaseVoucher", obj.PurchaseVoucher);
DBClass.AddSimpleParameter(cmd, "@CreatedDate", obj.CreatedDate);
var affectedRows = DBClass.ExecuteNonQuery(cmd, txn);
if (affectedRows == 0)
{
throw new Exception("Hapus log gagal");
}
txn.Commit();
}
catch (Exception)
{
txn.Rollback();
throw;
}
}
}
}
public override int SaveStockCRUDLog(CRUDLogObject logObject)
{
var obj = logObject as StockLogObject;
int objID = 0;
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("SAVE_NEW_STOCKFLOW") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", obj.DepartementID);
DBClass.AddSimpleParameter(cmd, "@ProductId", obj.ProductID);
DBClass.AddSimpleParameter(cmd, "@Description", obj.Description);
DBClass.AddSimpleParameter(cmd, "@PurchaseVoucher", obj.PurchaseVoucher);
DBClass.AddSimpleParameter(cmd, "@Deposit", obj.Deposit);
DBClass.AddSimpleParameter(cmd, "@Withdraw", obj.Withdraw);
DBClass.AddSimpleParameter(cmd, "@Note", obj.Note);
DBClass.AddSimpleParameter(cmd, "@CreatedBy", obj.CreatedBy);
DBClass.AddSimpleParameter(cmd, "@CreatedDate", obj.CreatedDate);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
objID = int.Parse(reader[0].ToString());
}
}
return(objID);
}
public override int SaveBankCRUDLog(BankLogObject logObject)
{
int objID = 0;
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("SAVE_NEW_BANKFLOW") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@CashBankId", logObject.CashBankID);
DBClass.AddSimpleParameter(cmd, "@Description", logObject.Description);
DBClass.AddSimpleParameter(cmd, "@SalesVoucher", logObject.SalesVoucher);
DBClass.AddSimpleParameter(cmd, "@Deposit", logObject.Deposit);
DBClass.AddSimpleParameter(cmd, "@Withdraw", logObject.Withdraw);
DBClass.AddSimpleParameter(cmd, "@Note", logObject.Note);
DBClass.AddSimpleParameter(cmd, "@CreatedBy", logObject.CreatedBy);
DBClass.AddSimpleParameter(cmd, "@CreatedDate", logObject.CreatedDate);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
objID = int.Parse(reader[0].ToString());
}
if (objID == 0)
{
throw new Exception();
}
}
return(objID);
}
public DataSet[] GetReportData(int depositSalesId, string inWords)
{
DataSet[] dataSetArray = new DataSet[1];
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_GET_DEPOSIT_SALES_DATA") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepositSalesId", depositSalesId);
DBClass.AddSimpleParameter(cmd, "@InWords", inWords);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dataSetResult = new DataSet();
adapter.Fill(dataSetResult, "DepositSales");
dataSetArray[0] = dataSetResult;
}
return(dataSetArray);
}
public DataSet[] GetReportData(int adjustmentId, string inWords)
{
DataSet[] dataSetArray = new DataSet[1];
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_GET_BANK_ADJUSTMENT_DATA") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@AdjustmentId", adjustmentId);
DBClass.AddSimpleParameter(cmd, "@InWords", inWords);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dataSetResult = new DataSet();
adapter.Fill(dataSetResult, "Adjustment");
dataSetArray[0] = dataSetResult;
}
return(dataSetArray);
}
public DataSet[] GetCashBankIncomeReportData(int expenseId, string inWords)
{
DataSet[] dataSetArray = new DataSet[1];
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_GET_BANK_INCOME_DATA") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@IncomeId", expenseId);
DBClass.AddSimpleParameter(cmd, "@InWords", inWords);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dataSetResult = new DataSet();
adapter.Fill(dataSetResult, "Income");
dataSetArray[0] = dataSetResult;
}
return(dataSetArray);
}
public DataSet GetStockFlowExcell(int departementId, int productId, DateTime printDate, DateTime dateStart, DateTime dateEnd)
{
DataSet dataSetResult = new DataSet();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_STOCKFLOW_REPORT_HEADER_EXCELL") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", departementId);
DBClass.AddSimpleParameter(cmd, "@DateStart", dateStart);
DBClass.AddSimpleParameter(cmd, "@DateEnd", dateEnd);
DBClass.AddSimpleParameter(cmd, "@PrintDate", printDate);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
adapter.Fill(table);
dataSetResult.Tables.Add(table);
}
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_STOCKFLOW_REPORT_ITEM_EXCELL") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepartementId", departementId);
DBClass.AddSimpleParameter(cmd, "@DateStart", dateStart);
DBClass.AddSimpleParameter(cmd, "@DateEnd", dateEnd);
DBClass.AddSimpleParameter(cmd, "@ProductId", productId);
DBClass.AddSimpleParameter(cmd, "@ByPassProduct", productId == 0 ? 1 : 0);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
adapter.Fill(table);
dataSetResult.Tables.Add(table);
}
var dataRows = dataSetResult.Tables[1].Select();
foreach (var dataRow in dataRows)
{
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("REPORT_STOCKFLOW_REPORT_DATA_EXCELL") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DateStart", dateStart);
DBClass.AddSimpleParameter(cmd, "@DateEnd", dateEnd);
DBClass.AddSimpleParameter(cmd, "@StockId", dataRow[0]);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
var table = new DataTable();
table.TableName = "STOCK-" + dataRow[0];
adapter.Fill(table);
dataSetResult.Tables.Add(table);
}
}
return(dataSetResult);
}
public IEnumerable <CreditPayment> GetSalesCreditPayment(int salesID)
{
var result = new List <CreditPayment>();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_SALES_CREDIT_PAYMENT") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@SalesId", salesID);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
var payment = new CreditPayment
{
CreditPaymentID = int.Parse(reader[0].ToString()),
CreditPaymentType = int.Parse(reader[1].ToString()),
CashLogObject = new CashLogObject
{
DepartementID = int.Parse(reader[2].ToString()),
CashID = string.IsNullOrEmpty(reader[3].ToString()) ? 0 : int.Parse(reader[3].ToString()),
SalesVoucher = reader[5].ToString(),
CreatedDate = DateTime.Parse(reader[6].ToString())
},
BankLogObject = new BankLogObject
{
CashBankID = string.IsNullOrEmpty(reader[4].ToString()) ? 0 : int.Parse(reader[4].ToString()),
SalesVoucher = reader[5].ToString(),
CreatedDate = DateTime.Parse(reader[6].ToString())
}
};
result.Add(payment);
}
}
return(result);
}
public CreditPayment GetEDCPaymentObject(int EDCCreditID, string createdBy, DateTime logDate)
{
var result = new CreditPayment();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_EDC_PAYMENT") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@SalesCreditId", EDCCreditID);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
result.BankLogObject = new BankLogObject
{
CashBankID = int.Parse(reader[0].ToString()),
Description = string.Format("Pelunasan pembayaran via EDC oleh {0}", reader[3]),
Deposit = decimal.Parse(reader[1].ToString()),
Withdraw = 0,
SalesVoucher = reader[2].ToString(),
Note = "",
CreatedBy = createdBy,
CreatedDate = logDate
};
}
}
return(result);
}
public string GetNewProductCode(int categoryID)
{
string productCode = "";
switch (categoryID)
{
case 1:
productCode = "KM-";
break;
case 2:
productCode = "AL-";
break;
case 3:
productCode = "LN-";
break;
}
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("GETPRODUCTNEWCODENUMBER") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@CategoryId", categoryID);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
productCode += reader[0].ToString();
}
}
return(productCode);
}
public T FindbyName(string name)
{
Customer customer = null;
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_CUSTOMER_BY_NAME") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@Name", name);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
customer = new Customer();
customer.CustomerID = int.Parse(reader[0].ToString());
customer.FirstName = StringManipulation.UppercaseFirst(reader[1].ToString());
customer.LastName = StringManipulation.UppercaseFirst(reader[2].ToString());
customer.Address = reader[3].ToString();
customer.Phone = reader[4].ToString();
customer.MobilePhone = reader[5].ToString();
customer.Email = reader[6].ToString();
customer.DepartementID = int.Parse(reader[7].ToString());
customer.StatusId = int.Parse(reader[8].ToString());
customer.Active = bool.Parse(reader[9].ToString());
customer.CreditLimit = decimal.Parse(reader[10].ToString());
}
}
return(customer as T);
}
public T FindbyId(int id)
{
var depositSales = new DepositSales();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_DEPOSIT_SALES_BY_ID") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@DepositSalesId", id);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
depositSales.DepositAccountID = int.Parse(reader[0].ToString());
depositSales.VoucherCode = reader[1].ToString();
depositSales.DepartementID = int.Parse(reader[2].ToString());
depositSales.CashBankId = int.Parse(reader[3].ToString());
depositSales.AccountByName = reader[4].ToString();
depositSales.StartingBalance = decimal.Parse(reader[5].ToString());
depositSales.Balance = decimal.Parse(reader[6].ToString());
depositSales.Note = reader[7].ToString();
depositSales.BankLogObject = new BankLogObject
{
CashBankID = depositSales.CashBankId,
DepositSalesVoucher = depositSales.VoucherCode
};
}
}
return(depositSales as T);
}
public T FindbyId(int id)
{
var cashBankAdjustment = new CashBankAdjustment();
using (DBClass = new MSSQLDatabase())
{
var cmd = DBClass.GetStoredProcedureCommand("APP_GET_CASH_BANK_ADJUSTEMENT_BY_ID") as SqlCommand;
DBClass.AddSimpleParameter(cmd, "@CashBankAdjustmentId", id);
var reader = DBClass.ExecuteReader(cmd);
while (reader.Read())
{
cashBankAdjustment.AdjustmentID = int.Parse(reader[0].ToString());
cashBankAdjustment.VoucherCode = reader[1].ToString();
cashBankAdjustment.DepartementId = int.Parse(reader[2].ToString());
cashBankAdjustment.CashBankId = int.Parse(reader[3].ToString());
cashBankAdjustment.AdjustmentType = reader[4].ToString();
cashBankAdjustment.Nominal = decimal.Parse(reader[5].ToString());
cashBankAdjustment.Note = reader[6].ToString();
cashBankAdjustment.AdjustmentDate = DateTime.Parse(reader[7].ToString());
cashBankAdjustment.LogObject = new BankLogObject
{
CashBankID = cashBankAdjustment.CashBankId,
AdjustmentVoucher = cashBankAdjustment.VoucherCode,
CreatedDate = cashBankAdjustment.AdjustmentDate
};
}
}
return(cashBankAdjustment as T);
}
