private DataTable GetPendingPay(string leaseNum)
{
string sql = "";
sql += " select ai.customer_number,";
sql += "ac.customer_name as customername,";
sql += "'' as shopname, ";
sql += "l.premise_name1 as premisename, ";
sql += "'' as shoparea, ";
sql += "0 as totalamount, ";
sql += " 0 as payinfonum, ";
//sql += "---- start payinfo detail ";
sql += "ai.invoice_number as transno, ";
sql += "ail.invoice_line_number as invoicelinenum,";
sql += "ail.charge_code as chargeitem, ";
sql += "ail.charge_description as descr, ";
sql += "ail.invoice_amount as amount, ";
sql += " ail.outstanding_amount as outstanding, ";
sql += " ai.payment_due_date as duedate ";
//sql += " ---- end payinfo detail ";
sql += "from ar_invoice ai, ar_invoice_line ail, ar_customer ac,lm_lease l,ar_customer_site acs ";
sql += " where 1 = 1 ";
sql += " and ai.invoice_number = ail.invoice_number ";
sql += " and Upper(SUBSTR(ail.PAY_STATUS, 1, 1)) in ('U', 'P') and ail.outstanding_amount > 0 ";
sql += " and ai.lease_number = trim(l.lease_number) and l.status = 'A' and l.active = 'A' ";
sql += " and ai.customer_number = ac.customer_number and ac.customer_number = acs.customer_number ";
sql += " and trim(l.site_number) = trim(acs.site_number) ";
sql += " and ai.lease_number ='" + leaseNum + "'";
CRUD biz = new CRUD(DbVendor.Oracle, Runtime.OracleConnStr, false);
try
{
DataTable dt = biz.ExecuteDataTable(sql);
dt.TableName = "PayInfo";
biz.Commit();
return(dt);
}
catch (Exception)
{
biz.Abort();
throw;
}
}
private List <string> GetGroupLease(int userid)
{
CRUD biz = new CRUD(DbVendor.Oracle, Runtime.OracleConnStrLocal, false);
try
{
string sql = "select trim(leasenumber) as leasenumber from SYS_USERS_GROUP_LEASE where UserId=" + userid.ToString();
DataTable dt = biz.ExecuteDataTable(sql);
biz.Commit();
List <string> leases = new List <string>();
for (int i = 0; i < dt.Rows.Count; i++)
{
leases.Add(dt.Rows[i][0].ToString());
}
return(leases);
}
catch (Exception)
{
biz.Abort();
throw;
}
}
