//------------------- Get Payment Information -------------------------------------------------- public List <SalePurchaseEntity> GetPaymentInformation(DBSite site, string payment_made_or_received, string ledger_number = "") { List <SalePurchaseEntity> sale_purchase_entity_list = new List <SalePurchaseEntity>(); SalePurchaseEntity sale_purchase_entity = null; string qry = " SELECT "; qry += " CONVERT( VARCHAR, LedgerId ) + '-' + CONVERT( VARCHAR, LedgerNumber ) as LedgerNumber "; qry += ", LDate "; qry += ", AccountName + ' | ' +CONVERT( VARCHAR, AccountMasterId ) as AccountName "; qry += ", ModeName + ' | ' +CONVERT( VARCHAR, ModeId ) as ModeName "; if (Int32.Parse(payment_made_or_received) == TransactionType.PaymentMade) { qry += ", debit amount"; } else { qry += ", credit amount "; } qry += ", Particulars "; qry += ", BillNumber "; qry += ", CheckNumber "; qry += " FROM vwLedger as ldg "; qry += " WHERE SourceId= " + payment_made_or_received; if (Int32.Parse(payment_made_or_received) == TransactionType.PaymentMade) { qry += " AND drcr ='D'"; } else { qry += " AND drcr ='C'"; } qry += " AND ldg.UserId=" + Util_BLL.User.UserId; qry += " AND ldg.FYear=" + Util_BLL.User.fYear; if (ledger_number != "") { qry += " AND LedgerNumber = " + ledger_number; } DataTable dt = site.ExecuteSelect(qry); DataRow row = null; for (int i = 0; i < dt.Rows.Count; i++) { sale_purchase_entity = new SalePurchaseEntity(); row = dt.Rows[i]; sale_purchase_entity.Id = util.CheckNull(row["LedgerNumber"]); DateTime date = Convert.ToDateTime(row["LDate"]); sale_purchase_entity.BillDate = date.ToShortDateString(); sale_purchase_entity.CheckNumber = util.CheckNull(row["CheckNumber"]); sale_purchase_entity.particulars = util.CheckNull(row["Particulars"]); sale_purchase_entity.BillNumber = util.CheckNull(row["BillNumber"]); sale_purchase_entity.SalePurchaseAmount = util.CheckNullDouble(row["amount"]); sale_purchase_entity.party = util.CheckNull(row["AccountName"]); sale_purchase_entity.mode = util.CheckNull(row["modeName"]); sale_purchase_entity_list.Add(sale_purchase_entity); } return(sale_purchase_entity_list); }
public List <SalaryEntity> GetSalaries(DBSite site, int salaryID = -1) { List <SalaryEntity> salList = new List <SalaryEntity>(); string qry = ""; qry += " SELECT SalaryID, sa.DesignationID, Designation, YearNo, MonthNo, BasicSalary "; qry += " , conveyance, EPF, ESI, EMI, StaffID, st.StudentName "; qry += " , sa.userID, sa.FYear "; qry += " FROM tblSalary as sa " + " LEFT OUTER JOIN tblStudentMaster st on st.StudentMasterID = sa.staffID " + " LEFT OUTER JOIN tblDesignation d ON d.designationId = sa.designationId " + " WHERE sa.UserID = " + Util_BLL.User.UserId; if (salaryID != -1) { qry += " AND SalaryID = " + salaryID; } else { qry += " AND SalaryId IN (SELECT MAX(SalaryID) FROM tblSalary GROUP BY StaffID) "; } DataTable dt = site.ExecuteSelect(qry); SalaryEntity sal; foreach (DataRow dr in dt.Rows) { sal = new SalaryEntity(); sal.SalaryId = util.CheckNullInt(dr["SalaryID"]); sal.StaffId = util.CheckNullInt(dr["StaffId"]); sal.StaffName = util.CheckNull(dr["StudentName"]); sal.DesignationId = util.CheckNullInt(dr["DesignationId"]); sal.Designation = util.CheckNull(dr["Designation"]); sal.YearNo = util.CheckNullInt(dr["YearNo"]); sal.MonthNo = util.CheckNullInt(dr["MonthNo"]); sal.BasicSalary = util.CheckNullDouble(dr["BasicSalary"]); sal.Conveyance = util.CheckNullDouble(dr["Conveyance"]); sal.epf = util.CheckNullDouble(dr["epf"]); sal.esi = util.CheckNullDouble(dr["esi"]); sal.emi = util.CheckNullDouble(dr["emi"]); sal.UserID = util.CheckNullInt(dr["UserID"]); sal.FYear = util.CheckNullInt(dr["FYear"]); salList.Add(sal); } return(salList); }
public List <ProductDetail> GetProductByDate(string fromDate, string toDate, int ProductId, int StockTransactionId) { DBSite site = new DBSite(); List <ProductDetail> product_detail_list = new List <ProductDetail>(); if (fromDate == null) { fromDate = "2013/09/01"; } if (toDate == null) { toDate = "2014/09/01"; } DateTime fDt = DateTime.Parse(fromDate); DateTime tDt = DateTime.Parse(toDate); DataTable dt = site.Execute_sp_productLedger(ProductId, StockTransactionId, -1, fDt, tDt, Util_BLL.User.UserId, 2012); DataRow row = null; ProductDetail product; for (int i = 0; i < dt.Rows.Count; i++) { product = new ProductDetail(); row = dt.Rows[i]; product.ProductLedgerId = util.CheckNullInt(row["ProductLedgerNumber"]); product.ProductId = util.CheckNullInt(row["ProductId"]); product.ProductName = row["ProductName"].ToString(); product.LocationName = util.CheckNull(row["Location"]); product.AccountName = util.CheckNull(row["AccountName"]); product.sold_qty = util.CheckNullDouble(row["credit"]); product.bought_qty = util.CheckNullDouble(row["debit"]); product.openingBalance = util.CheckNullDouble(row["openingBalance"]); product.closingBalance = util.CheckNullDouble(row["runningBalance"]); //666 DateTime date = Convert.ToDateTime(row["BillDate"]); product.MovementDate = date.ToShortDateString(); product.TransactionTypeId = util.CheckNullInt(row["SourceId"]); product.TransactionName = util.CheckNull(row["TransactionName"]); product.Narration = util.CheckNull(row["Particulars"]); product_detail_list.Add(product); } return(product_detail_list); }