public static List <SCInvoice> getContractInvoice(int ContractOID, List <Int32> lstInvoiceType, bool creditInvoice)
{
if (lstInvoiceType == null || lstInvoiceType.Count <= 0)
{
return(new List <SCInvoice>());
}
clsSqlFactory hSql = new clsSqlFactory();
List <SCInvoice> Result = new List <SCInvoice>();
try
{
String strSql = "select a.OID as OID, b.SRECNO , b.BILLD,b.DELD,b.PAIDDATE,b.PAIDSUM,b.CRERECNO,b.CUSTNO,c.LNAME,d.EXPL, b.SSALID,b._UNITID " +
",b.BTYPE,e.SMANID as SMANID ,e.SORDNO as SORDNO,e.DEPT as DEPT, a.InvoiceNo, x.INVSUM,x.INVSUM0 " +
"FROM ZSC_ContractInvoice a, all_sbil b LEFT JOIN cust c on b.custno = c.CUSTNO, unit d, all_SSAL e," +
" (select _UNITID,SRECNO,SSALID,sum(isnull(RSUM,0)) as INVSUM,sum(isnull(RSUM/isnull(dbo.fn_AMVATValue(_UNITID,VATCD),1),0)) as INVSUM0 from all_SROW group by _UNITID,SRECNO,SSALID ) x " +
"WHERE a.ContractOID = ? and a.SSALID = b.SSALID and a.UnitId = b._UNITID and a.UnitId = d.UnitId and b.SSALID=e.SSALID and b._UNITID=e._UNITID and b._UNITID=x._UNITID and b.SSALID=x.SSALID and b.SRECNO=x.SRECNO ";
if (creditInvoice == false)
{
strSql += " AND b.CRERECNO IS NULL ";
}
if (lstInvoiceType.Count == 1)
{
strSql += " AND a.InvoiceType = ?";
strSql += " order by a.InvoiceNo desc ";
hSql.NewCommand(strSql);
hSql.Com.Parameters.AddWithValue("ContractOID", ContractOID);
hSql.Com.Parameters.AddWithValue("InvoiceType", lstInvoiceType[0]);
}
else
{
strSql = MyUtils.BuildWhereInClause(strSql, "InvoiceType", lstInvoiceType);
strSql += " order by a.OID desc ";
hSql.NewCommand(strSql);
hSql.Com.Parameters.AddWithValue("ContractOID", ContractOID);
int i = 0;
foreach (Int32 InvoiceType in lstInvoiceType)
{
hSql.Com.Parameters.AddWithValue("InvoiceType" + i++, InvoiceType);
}
}
hSql.ExecuteReader();
int colId;
while (hSql.Read())
{
SCInvoice item = new SCInvoice();
colId = hSql.Reader.GetOrdinal("OID");
if (!hSql.Reader.IsDBNull(colId))
{
item.OID = hSql.Reader.GetInt32(colId);
}
colId = hSql.Reader.GetOrdinal("SRECNO");
if (!hSql.Reader.IsDBNull(colId))
{
item.SRECNO = hSql.Reader.GetInt32(colId);
}
colId = hSql.Reader.GetOrdinal("BILLD");
if (!hSql.Reader.IsDBNull(colId))
{
item.BILLD = hSql.Reader.GetDateTime(colId);
}
colId = hSql.Reader.GetOrdinal("DELD");
if (!hSql.Reader.IsDBNull(colId))
{
item.DELD = hSql.Reader.GetDateTime(colId);
}
colId = hSql.Reader.GetOrdinal("PAIDDATE");
if (!hSql.Reader.IsDBNull(colId))
{
item.PAIDDATE = hSql.Reader.GetDateTime(colId);
}
colId = hSql.Reader.GetOrdinal("PAIDSUM");
if (!hSql.Reader.IsDBNull(colId))
{
item.PAIDSUM = hSql.Reader.GetInt32(colId);
}
colId = hSql.Reader.GetOrdinal("CRERECNO");
if (!hSql.Reader.IsDBNull(colId))
{
item.SRECNO = hSql.Reader.GetInt32(colId);
}
colId = hSql.Reader.GetOrdinal("CUSTNO");
if (!hSql.Reader.IsDBNull(colId))
{
item.CustNo = hSql.Reader.GetInt32(colId);
}
colId = hSql.Reader.GetOrdinal("LNAME");
if (!hSql.Reader.IsDBNull(colId))
{
item.LNAME = hSql.Reader.GetString(colId);
}
colId = hSql.Reader.GetOrdinal("EXPL");
if (!hSql.Reader.IsDBNull(colId))
{
item.EXPL = hSql.Reader.GetString(colId);
}
colId = hSql.Reader.GetOrdinal("SSALID");
if (!hSql.Reader.IsDBNull(colId))
{
item.SSALID = hSql.Reader.GetInt32(colId);
}
colId = hSql.Reader.GetOrdinal("_UNITID");
if (!hSql.Reader.IsDBNull(colId))
{
item.UnitId = hSql.Reader.GetString(colId);
}
colId = hSql.Reader.GetOrdinal("BTYPE");
if (!hSql.Reader.IsDBNull(colId))
{
item.BTYPE = hSql.Reader.GetString(colId);
}
colId = hSql.Reader.GetOrdinal("SMANID");
if (!hSql.Reader.IsDBNull(colId))
{
item.SmanId = hSql.Reader.GetString(colId);
}
colId = hSql.Reader.GetOrdinal("SORDNO");
if (!hSql.Reader.IsDBNull(colId))
{
item.SORDNO = (Int32)hSql.Reader.GetDecimal(colId);
}
colId = hSql.Reader.GetOrdinal("DEPT");
if (!hSql.Reader.IsDBNull(colId))
{
item.DeptId = hSql.Reader.GetString(colId);
}
colId = hSql.Reader.GetOrdinal("InvoiceNo");
if (!hSql.Reader.IsDBNull(colId))
{
item.InvoiceSeqNr = hSql.Reader.GetInt32(colId);
}
colId = hSql.Reader.GetOrdinal("INVSUM");
if (!hSql.Reader.IsDBNull(colId))
{
item.INVSUM = hSql.Reader.GetDecimal(colId);
}
colId = hSql.Reader.GetOrdinal("INVSUM0");
if (!hSql.Reader.IsDBNull(colId))
{
item.INVSUM0 = hSql.Reader.GetDecimal(colId);
}
Result.Add(item);
}
}
catch (Exception ex)
{
_log.Error("ERROR getContractInvoice " + ContractOID + ": ", ex);
throw ex;
}
finally
{
hSql.Close();
}
return(Result);
}