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