public async Task <string> UpdateAdvanceByVoucher(UpdateAdvanceDomainModel advance)
{
string status = "";
int condition = 0;
if (condition > 0)
{
List <tblAdvance> uList = new List <tblAdvance>();
uList = await advanceRepository.GetAll();
}
using (abuhamzapetstoreEntities db = new abuhamzapetstoreEntities())
{
try
{
var result = db.stpUpdateAdvance(advance.voucherNo, advance.amount, advance.transactionType);
}
catch (Exception ex)
{
status = ex.Message;
}
}
return(status);
}
public async Task <string> pendingOrderPayment(PaymentVoucherDomainModel paymentVoucherDM)
{
string status = "";
int condition = 0;
if (condition > 0)
{
List <tblvch> uList = new List <tblvch>();
uList = await voucherRepository.GetAll();
}
using (abuhamzapetstoreEntities db = new abuhamzapetstoreEntities())
{
try
{
var result = db.stpPurchaseOrderPayment(paymentVoucherDM.supplierId, paymentVoucherDM.amountPaid,
paymentVoucherDM.remaingAmount, paymentVoucherDM.voucherNumber);
}
catch (Exception ex)
{
status = ex.Message;
}
}
return(status);
}
public async Task <string> AddUpdateAdvance(AdvanceDomainModel advance)
{
string status = "";
int condition = 0;
if (condition > 0)
{
List <tblAdvance> uList = new List <tblAdvance>();
uList = await advanceRepository.GetAll();
}
int vrNo = 0;
string strVchNo = "";
using (abuhamzapetstoreEntities db = new abuhamzapetstoreEntities())
{
try
{
vrNo = (from c in db.tblvches
orderby c.vch_id descending
select c.vch_id).Take(1).SingleOrDefault();
strVchNo = "0000" + (vrNo + 1);
var result = db.stpAdvance(advance.amount, advance.barcode, advance.description, advance.cus_No, strVchNo, DateTime.Now, advance.cus_Name);
}
catch (Exception ex)
{
status = ex.Message;
}
}
return(status);
}
public async Task <string> DeleteForthSubCategory(int id)
{
string status = "";
int countProduct = 0;
if (id > 0)
{
using (abuhamzapetstoreEntities db = new abuhamzapetstoreEntities())
{
try
{
countProduct = (from p in db.tblProducts
where p.fsubCat_id == id
select 1).Take(1).SingleOrDefault();
if (countProduct == 0)
{
await forthSubCategoryRepository.Delete(f => f.fsubCat_id == id);
status = "Deleted";
}
else
{
status = "NotDelete";
}
}
catch (Exception ex)
{
status = ex.Message;
}
}
}
return(status);
}
public DashboardDomainModel GetDashboardData()
{
DashboardDomainModel dashboardDomainModel = new DashboardDomainModel();
abuhamzapetstoreEntities db = new abuhamzapetstoreEntities();
DataTable dt = new DataTable();
List <dynamic> list = new List <dynamic>();
using (SqlConnection conn = new SqlConnection(@"data source=DESKTOP-TCM883N\SQLEXPRESS;initial catalog=abuhamzapetstore;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"))
using (SqlCommand cmd = new SqlCommand("stpGetDashboardData", conn))
{
SqlDataAdapter adapt = new SqlDataAdapter(cmd);
adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
adapt.Fill(dt);
}
foreach (DataRow row in dt.Rows)
{
dashboardDomainModel.cashInDrawer = Convert.ToDecimal(row["CashInDrawer"]);
dashboardDomainModel.pettyCash = Convert.ToDecimal(row["PettyCash"]);
dashboardDomainModel.purchases = Convert.ToDecimal(row["Purchases"]);
dashboardDomainModel.expenses = Convert.ToDecimal(row["Expenses"]);
dashboardDomainModel.totalPurchase = Convert.ToDecimal(row["TotalPurchase"]);
dashboardDomainModel.totalSale = Convert.ToDecimal(row["TotalSale"]);
dashboardDomainModel.sales = Convert.ToDecimal(row["Sales"]);
dashboardDomainModel.loans = Convert.ToDecimal(row["Loans"]);
dashboardDomainModel.advances = Convert.ToDecimal(row["Advances"]);
dashboardDomainModel.purchasePendings = Convert.ToDecimal(row["PurchasePendings"]);
dashboardDomainModel.totalSuppliers = Convert.ToInt32(row["TotalSuppliers"]);
dashboardDomainModel.totalCustomers = Convert.ToInt32(row["TotalCustomers"]);
}
return(dashboardDomainModel);
}
public async Task <string> CashTransaction(CashTransactionDomainModel cashTransaction)
{
string status = "";
int vrNo = 0;
string strVchNo = "";
int condition = 0;
if (condition > 0)
{
List <tblAccount> uList = new List <tblAccount>();
uList = await accountRepository.GetAll();
}
using (abuhamzapetstoreEntities db = new abuhamzapetstoreEntities())
{
try
{
vrNo = (from c in db.tblvches
orderby c.vch_id descending
select c.vch_id).Take(1).SingleOrDefault();
strVchNo = "0000" + (vrNo + 1);
var result = db.stpCashTransaction(cashTransaction.debitor_Account_Id, cashTransaction.creditor_Account_Id
, cashTransaction.voucherAmount, strVchNo, cashTransaction.description);
}
catch (Exception ex)
{
status = ex.Message;
}
}
return(status);
}
public async Task <string> AddUpdateSupplier(SupplierDomainModel supplier)
{
string status = "";
int sup_id = 0;
if (supplier.sup_id > 0)
{
tblSupplier supplierToUpdate = await supplierRepository.SingleOrDefault(s => s.sup_id == supplier.sup_id);
if (supplierToUpdate != null)
{
supplierToUpdate.name = supplier.name;
supplierToUpdate.contact = supplier.contact;
supplierToUpdate.company = supplier.company;
supplierToUpdate.date = supplier.date;
await supplierRepository.Update(supplierToUpdate);
status = "updated";
}
}
else
{
tblSupplier supplierToAdd = new tblSupplier();
supplierToAdd.name = supplier.name;
supplierToAdd.contact = supplier.contact;
supplierToAdd.company = supplier.company;
supplierToAdd.date = DateTime.Now;
await supplierRepository.Insert(supplierToAdd);
using (abuhamzapetstoreEntities db = new abuhamzapetstoreEntities())
{
try
{
sup_id = (from c in db.tblSuppliers
orderby c.sup_id descending
select c.sup_id).Take(1).SingleOrDefault();
var result = db.stpOpenAccountWithMapping(sup_id, 1, 2, supplier.name);
}
catch (Exception ex)
{
status = ex.Message;
}
}
status = "added";
}
return(status);
}
public async Task <string> DeleteMainCategory(int id)
{
string status = "";
int count = 0;
int countProduct = 0;
if (id > 0)
{
using (abuhamzapetstoreEntities db = new abuhamzapetstoreEntities())
{
try
{
//var result =db.CheckChildCategory(id, 0, 0);
count = (from c in db.tblCategories
where c.mainCat_id == id
select 1).Take(1).SingleOrDefault();
countProduct = (from p in db.tblProducts
where p.mainCat_id == id
select 1).Take(1).SingleOrDefault();
if (count == 0 && countProduct == 0)
{
await mainCategoryRepository.Delete(m => m.mainCat_id == id);
status = "Deleted";
}
else
{
status = "NotDelete";
}
}
catch (Exception ex)
{
status = ex.Message;
}
}
}
return(status);
}
public override async Task GrantResourceOwnerCredentials(OAuthGrantResourceOwnerCredentialsContext context)
{
////context.OwinContext.Response.Headers.Add("Access-Control-Allow-Origin", new[] { "*" });
string error = "";
try
{
using (var db = new abuhamzapetstoreEntities())
{
if (db != null)
{
tblUser user = db.tblUsers.Where(u => u.email == context.UserName && u.password == context.Password).FirstOrDefault();
if (user != null)
{
var identity = new ClaimsIdentity(context.Options.AuthenticationType);
identity.AddClaim(new Claim("Userid", user.user_id.ToString()));
identity.AddClaim(new Claim("Username", user.username));
identity.AddClaim(new Claim("Firstname", user.firstname));
identity.AddClaim(new Claim("Lastname", user.lastname));
identity.AddClaim(new Claim("Email", user.email));
identity.AddClaim(new Claim("Contact", user.contact));
identity.AddClaim(new Claim("Userrole", user.userRoll));
identity.AddClaim(new Claim("Status", user.status));
context.Validated(identity);
}
else
{
context.SetError("invalid_grant", "Provided username and password is incorrect.");
context.Rejected();
}
return;
}
}
}
catch (Exception ex)
{
error = ex.Message;
}
}
public async Task <string> AddUpdateInvoice(SaleOrderDomainModel saleOrder)
{
int condition = 0;
if (condition > 0)
{
List <tblInvoice> uList = new List <tblInvoice>();
uList = await invoiceRepository.GetAll();
}
string status = "";
int vrNo = 0;
int totalQty = 0;
string strVchNo = "";
List <SingleProductDomainModel> singleProductList = new List <SingleProductDomainModel>();
singleProductList = saleOrder.singleProductList;
foreach (SingleProductDomainModel singleDetail in singleProductList)
{
totalQty = Convert.ToInt32(singleDetail.quantity) + totalQty;
}
int lastSaleOrderId = 0;
using (abuhamzapetstoreEntities db = new abuhamzapetstoreEntities())
{
try
{
vrNo = (from c in db.tblvches
orderby c.vch_id descending
select c.vch_id).Take(1).SingleOrDefault();
strVchNo = "0000" + (vrNo + 1);
var result = db.stpSaleOrder(1, totalQty, saleOrder.subTotal,
saleOrder.grandTotal, saleOrder.discount, saleOrder.tenderAmount, saleOrder.remainingCash,
"Customer Name", 1, saleOrder.grandTotal, strVchNo, saleOrder.cus_No);
lastSaleOrderId = (from c in db.tblInvoices
orderby c.invoice_id descending
select c.invoice_id).Take(1).SingleOrDefault();
foreach (SingleProductDomainModel singleDetail in singleProductList)
{
result = db.stpSaleDetail(lastSaleOrderId, singleDetail.quantity, singleDetail.barcode,
singleDetail.sellPrice, strVchNo, singleDetail.purchasePrice);
}
if (saleOrder.isAdvanceVisible == true)
{
result = db.stpUseAdvance(saleOrder.voucherNo, strVchNo, saleOrder.totalAdvance, saleOrder.remainingAdvance);
}
}
catch (Exception ex)
{
status = ex.Message;
}
}
status = "added";
return(status);
}
public DailyReport DailyReport()
{
DailyReport dailyReport = new DailyReport();
List <SaleRecord> saleRecordList = new List <SaleRecord>();
List <PurchaseRecord> purchaseRecordList = new List <PurchaseRecord>();
List <AdvanceRecord> advanceRecordList = new List <AdvanceRecord>();
List <ExpenseRecord> expenseRecordList = new List <ExpenseRecord>();
List <LoanRecord> loanRecordList = new List <LoanRecord>();
List <PurchasePending> purchasePendingList = new List <PurchasePending>();
abuhamzapetstoreEntities db = new abuhamzapetstoreEntities();
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(@"data source=DESKTOP-TCM883N\SQLEXPRESS;initial catalog=abuhamzapetstore;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"))
using (SqlCommand cmd = new SqlCommand("stpGetSaleRecord", conn))
{
SqlDataAdapter adapt = new SqlDataAdapter(cmd);
adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
adapt.Fill(dt);
}
saleRecordList = (from DataRow dr in dt.Rows
select new SaleRecord()
{
vchNo = dr["vchNo"].ToString(),
date = Convert.ToDateTime(dr["date"]),
pendingAmount = Convert.ToDecimal(dr["pendingAmount"]),
paidAmount = Convert.ToDecimal(dr["paidAmount"]),
totalAmount = Convert.ToDecimal(dr["totalAmount"]),
status = dr["status"].ToString(),
vchType = dr["vchType"].ToString(),
description = dr["description"].ToString()
}).ToList();
dt.Clear();
//-------------------------------------------------------------------------------------------
using (SqlConnection conn = new SqlConnection(@"data source=DESKTOP-TCM883N\SQLEXPRESS;initial catalog=abuhamzapetstore;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"))
using (SqlCommand cmd = new SqlCommand("stpGetPurchaseRecord", conn))
{
SqlDataAdapter adapt = new SqlDataAdapter(cmd);
adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
adapt.Fill(dt);
}
purchaseRecordList = (from DataRow dr in dt.Rows
select new PurchaseRecord()
{
vchNo = dr["vchNo"].ToString(),
date = Convert.ToDateTime(dr["date"]),
pendingAmount = Convert.ToDecimal(dr["pendingAmount"]),
paidAmount = Convert.ToDecimal(dr["paidAmount"]),
totalAmount = Convert.ToDecimal(dr["totalAmount"]),
status = dr["status"].ToString(),
vchType = dr["vchType"].ToString(),
description = dr["description"].ToString()
}).ToList();
dt.Clear();
//-------------------------------------------------------------------------------------------
using (SqlConnection conn = new SqlConnection(@"data source=DESKTOP-TCM883N\SQLEXPRESS;initial catalog=abuhamzapetstore;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"))
using (SqlCommand cmd = new SqlCommand("stpGetAdvanceRecord", conn))
{
SqlDataAdapter adapt = new SqlDataAdapter(cmd);
adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
adapt.Fill(dt);
}
advanceRecordList = (from DataRow dr in dt.Rows
select new AdvanceRecord()
{
voucherNo = dr["voucherNo"].ToString(),
date = Convert.ToDateTime(dr["date"]),
amount = Convert.ToDecimal(dr["amount"]),
barcode = dr["barcode"].ToString(),
cus_No = dr["cus_No"].ToString(),
cus_Name = dr["cus_Name"].ToString(),
description = dr["description"].ToString()
}).ToList();
dt.Clear();
//-------------------------------------------------------------------------------------------
using (SqlConnection conn = new SqlConnection(@"data source=DESKTOP-TCM883N\SQLEXPRESS;initial catalog=abuhamzapetstore;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"))
using (SqlCommand cmd = new SqlCommand("stpGetExpenseRecord", conn))
{
SqlDataAdapter adapt = new SqlDataAdapter(cmd);
adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
adapt.Fill(dt);
}
expenseRecordList = (from DataRow dr in dt.Rows
select new ExpenseRecord()
{
vchNo = dr["vchNo"].ToString(),
date = Convert.ToDateTime(dr["date"]),
pendingAmount = Convert.ToDecimal(dr["pendingAmount"]),
paidAmount = Convert.ToDecimal(dr["paidAmount"]),
totalAmount = Convert.ToDecimal(dr["totalAmount"]),
status = dr["status"].ToString(),
vchType = dr["vchType"].ToString(),
description = dr["description"].ToString()
}).ToList();
dt.Clear();
//-------------------------------------------------------------------------------------------
using (SqlConnection conn = new SqlConnection(@"data source=DESKTOP-TCM883N\SQLEXPRESS;initial catalog=abuhamzapetstore;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"))
using (SqlCommand cmd = new SqlCommand("stpGetLoanRecord", conn))
{
SqlDataAdapter adapt = new SqlDataAdapter(cmd);
adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
adapt.Fill(dt);
}
loanRecordList = (from DataRow dr in dt.Rows
select new LoanRecord()
{
vchNo = dr["vchNo"].ToString(),
date = Convert.ToDateTime(dr["date"]),
pendingAmount = Convert.ToDecimal(dr["pendingAmount"]),
paidAmount = Convert.ToDecimal(dr["paidAmount"]),
totalAmount = Convert.ToDecimal(dr["totalAmount"]),
status = dr["status"].ToString(),
vchType = dr["vchType"].ToString(),
customerName = dr["customerName"].ToString(),
customerNo = dr["customerNo"].ToString(),
description = dr["description"].ToString()
}).ToList();
dt.Clear();
//-------------------------------------------------------------------------------------------
using (SqlConnection conn = new SqlConnection(@"data source=DESKTOP-TCM883N\SQLEXPRESS;initial catalog=abuhamzapetstore;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"))
using (SqlCommand cmd = new SqlCommand("stpGetPurchasePendingRecord", conn))
{
SqlDataAdapter adapt = new SqlDataAdapter(cmd);
adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
adapt.Fill(dt);
}
purchasePendingList = (from DataRow dr in dt.Rows
select new PurchasePending()
{
vchNo = dr["vchNo"].ToString(),
date = Convert.ToDateTime(dr["date"]),
pendingAmount = Convert.ToDecimal(dr["pendingAmount"]),
paidAmount = Convert.ToDecimal(dr["paidAmount"]),
totalAmount = Convert.ToDecimal(dr["totalAmount"]),
status = dr["status"].ToString(),
vchType = dr["vchType"].ToString(),
supplierNo = dr["supplierNo"].ToString(),
supplierName = dr["supplierName"].ToString(),
description = dr["description"].ToString()
}).ToList();
dt.Clear();
//-------------------------------------------------------------------------------------------
using (SqlConnection conn = new SqlConnection(@"data source=DESKTOP-TCM883N\SQLEXPRESS;initial catalog=abuhamzapetstore;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"))
using (SqlCommand cmd = new SqlCommand("stpGetDailySummary", conn))
{
SqlDataAdapter adapt = new SqlDataAdapter(cmd);
adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
adapt.Fill(dt);
}
foreach (DataRow row in dt.Rows)
{
dailyReport.cashInDrawer = Convert.ToDecimal(row["CashInDrawer"]);
dailyReport.pettyCash = Convert.ToDecimal(row["PettyCash"]);
dailyReport.totalExpenses = Convert.ToDecimal(row["TotalExpenses"]);
dailyReport.totalPurchase = Convert.ToDecimal(row["TotalPurchase"]);
dailyReport.totalSale = Convert.ToDecimal(row["TotalSale"]);
dailyReport.totalLoans = Convert.ToDecimal(row["TotalLoans"]);
dailyReport.totalAdvances = Convert.ToDecimal(row["TotalAdvances"]);
dailyReport.totalPurchasePendings = Convert.ToDecimal(row["TotalPurchasePendings"]);
}
dailyReport.saleRecordList = saleRecordList;
dailyReport.purchaseRecordList = purchaseRecordList;
dailyReport.advanceRecordList = advanceRecordList;
dailyReport.expenseRecordList = expenseRecordList;
dailyReport.loanRecordList = loanRecordList;
dailyReport.purchasePendingList = purchasePendingList;
return(dailyReport);
}
public async Task <string> AddUpdatePurchaseOrder(PurchaseOrderDomainModel purchaseOrder)
{
string status = "";
if (purchaseOrder.order_id > 0)
{
tblPurchaseOrder purchaseOrderToUpdate = await purchaseOrderRepository.SingleOrDefault(s => s.order_id == purchaseOrder.order_id);
if (purchaseOrderToUpdate != null)
{
await purchaseOrderRepository.Update(purchaseOrderToUpdate);
status = "updated";
}
}
else
{
List <DetailOrderDomainModel> detailOrder = new List <DetailOrderDomainModel>();
detailOrder = purchaseOrder.orderDetails;
string strVchNo = "";
string strOrderDesc = "";
int vrNo = 0;
tblPurchaseOrder purchaseOrderToAdd = new tblPurchaseOrder();
int lastOrderId = 0;
using (abuhamzapetstoreEntities db = new abuhamzapetstoreEntities())
{
try
{
vrNo = (from c in db.tblvches
orderby c.vch_id descending
select c.vch_id).Take(1).SingleOrDefault();
strVchNo = "0000" + (vrNo + 1);
strOrderDesc = purchaseOrder.description;
if (purchaseOrder.description == null)
{
strOrderDesc = "";
}
var result = db.stpPurchaseOrder(1, purchaseOrder.totalAmount, purchaseOrder.paidAmount,
strOrderDesc, purchaseOrder.supplier, purchaseOrder.paidAmount, strVchNo, 1, purchaseOrder.creditorAcc_Id);
lastOrderId = (from c in db.tblPurchaseOrders
orderby c.order_id descending
select c.order_id).Take(1).SingleOrDefault();
tblDetailOrder detailOrderToAdd = new tblDetailOrder();
foreach (DetailOrderDomainModel singleDetail in detailOrder)
{
result = db.stpDetailOrder(lastOrderId, singleDetail.quantity, singleDetail.barcode,
singleDetail.purchasePrice, strVchNo);
}
}
catch (Exception ex)
{
status = ex.Message;
}
}
status = "added";
}
return(status);
}
public UnitOfWork()
{
_dbContext = new abuhamzapetstoreEntities();
}
