//------------------- 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);
}