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);
}
private void addInvoiceText(clsSqlFactory hSql, Contract objContract, ref SCInvoice objInv)
{
int RowId = objInv.InvItems.Count;
List <String> strTexts = new List <String>();
List <String> strPrintingFlags = new List <String>();
List <String> strFieldCodes = new List <String>();
String strSql = "select a.C6,b.C3 from CORW a left join CORW b on b.CODAID='INFOTEXT' and a.C4=b.C1 where a.CODAID='ZSCINVTEXT' and a.V1=1 and a.C3 like '%N%' order by a.V2 ";
hSql.NewCommand(strSql);
hSql.ExecuteReader();
while (hSql.Read())
{
String strTmp = hSql.Reader.GetString(0);
strPrintingFlags.Add(hSql.Reader.GetString(1));
getFieldCodesFromString(strTmp, ref strFieldCodes);
strTexts.Add(strTmp);
}
hSql.NewCommand("exec ZSC_SP_PrintContract " + objContract.ContractOID.ToString());
hSql.ExecuteReader();
if (hSql.Read())
{
int colId = -1;
foreach (String strFieldCode in strFieldCodes)
{
colId = hSql.Reader.GetOrdinal(strFieldCode);
if (colId >= 0)
{
String strDataType = hSql.Reader.GetDataTypeName(colId).ToUpper();
String strValue = "";
if (!hSql.Reader.IsDBNull(colId))
{
switch (strDataType)
{
case "DATETIME":
strValue = hSql.Reader.GetDateTime(colId).ToShortDateString();
break;
case "INT":
strValue = hSql.Reader.GetInt32(colId).ToString();
break;
default:
strValue = hSql.Reader.GetString(colId);
break;
}
for (int i = 0; i < strTexts.Count; i++)
{
strTexts[i] = strTexts[i].Replace("$" + strFieldCode + "$", strValue);
}
}
}
}
for (int i = 0; i < strTexts.Count; i++)
{
RowId++;
SCInvoiceItem objRow = new SCInvoiceItem();
objRow.SROWID = RowId;
objRow.NAME = "";
objRow.RTYPE = 8;
objRow.NOTE = strTexts[i];
objRow.RINFO = strPrintingFlags[i];
objRow.EXIDNO = objContract.ContractNo;
objInv.InvItems.Add(objRow);
}
}
}
private void addInvoiceRows(Contract objContract, ref SCInvoice objInv, bool bCapital)
{
clsSqlFactory hSql = new clsSqlFactory();
int RowId = objInv.InvItems.Count;
Decimal nInvoiceSum = 0;
Decimal nInvoiceOrigSum = 0;
Decimal nInvoiceOrigBuyPr = 0;
int nPayPeriod = 1;
clsTaxHandling objTax = new clsTaxHandling();
objTax.Init(2);
String RowVatCd = getVatCode(objTax, objInv.BTYPE);
SCInvoiceItem objRow;
if (objContract.IsInvoiceDetail == false)
{
RowId++;
objRow = new SCInvoiceItem();
objRow.SROWID = RowId;
objRow.NAME = "~1";
objRow.RTYPE = 8;
objRow.NOTE = "CONTRACT NO " + objContract.ContractNo.ToString();
objRow.EXIDNO = objContract.ContractNo;
objInv.InvItems.Add(objRow);
}
switch (objContract.ContractCostData.CostBasis.strValue1)
{
case CostBasisType.Monthly:
nInvoiceSum = objContract.ContractCostData.CostBasedOnService - objContract.ContractCapitalData.CapitalMonthAmount;
break;
case CostBasisType.KmOrHour:
break;
case CostBasisType.KmOrHourWithLump:
nInvoiceSum = objContract.ContractCostData.CostMonthBasis - objContract.ContractCapitalData.CapitalMonthAmount;
break;
default:
break;
}
switch (objContract.ContractPaymentData.PaymentPeriod.strValue1)
{
case PaymentPeriodType.Quarterly:
nPayPeriod = 3;
break;
case PaymentPeriodType.HalfYear:
nPayPeriod = 6;
break;
case PaymentPeriodType.Yearly:
nPayPeriod = 12;
break;
default:
break;
}
if (bCapital)
{
nInvoiceSum = objContract.ContractCapitalData.CapitalMonthAmount * nPayPeriod;
nInvoiceSum = (Decimal)objTax.Add(nInvoiceSum, RowVatCd, objInv.BILLD);
}
else
{
if (objInv.Payer != "")
{
nInvoiceSum = 0;
}
nInvoiceSum = (Decimal)objTax.Add(nInvoiceSum, RowVatCd, objInv.BILLD);
nInvoiceSum = nInvoiceSum * nPayPeriod;
}
foreach (ContractOption objCat in objContract.listContractOptions)
{
if ((objCat.PartNr != null) && (objCat.PartNr != "") &&
(((objCat.PartialPayer == objInv.Payer) && (bCapital == false)) || ((objCat.PartialPayer == "") && (bCapital == true))
)
)
{
RowId++;
objRow = new SCInvoiceItem();
objRow.ITEMNO = objCat.PartNr;
if (objInv.PartPostFix != "")
{
hSql.NewCommand("select 1 from ITEM where ITEMNO=? and SUPLNO=? ");
hSql.Com.Parameters.Add("ITEMNO", objRow.ITEMNO + objInv.PartPostFix);
hSql.Com.Parameters.Add("SUPLNO", objCat.PartSuplNo);
hSql.ExecuteReader();
if (hSql.Read())
{
objRow.ITEMNO += objInv.PartPostFix;
}
}
objRow.SUPLNO = objCat.PartSuplNo;
objRow.NAME = objCat.Name;
objRow.RTYPE = 2;
objRow.SROWID = RowId;
objRow.BUYPR = (Decimal)objTax.Add(objCat.PurchasePr, RowVatCd, objInv.BILLD);
objRow.DISCPC = 0;
objRow.NUM = 1;
objRow.NUM = objRow.NUM * nPayPeriod;
objRow.UNITPR = (Decimal)objTax.Add(objCat.SalePr, RowVatCd, objInv.BILLD);
objRow.RSUM = (decimal)objRow.NUM * objRow.UNITPR;
objRow.IGROUPID = -1;
objRow.VATCD = RowVatCd;
nInvoiceOrigSum += objRow.RSUM;
nInvoiceOrigBuyPr += objRow.BUYPR;
objRow.EXIDNO = objContract.ContractNo;
objInv.InvItems.Add(objRow);
if ((objInv.Payer != "") && (bCapital == false))
{
nInvoiceSum += objRow.RSUM;
}
}
}
if (objContract.IsInvoiceDetail == false)
{
RowId++;
objRow = new SCInvoiceItem();
objRow.SROWID = RowId;
objRow.NAME = "~1";
objRow.RTYPE = 8;
objRow.NOTE = "-----------------------";
objRow.EXIDNO = objContract.ContractNo;
objInv.InvItems.Add(objRow);
}
//adapt row sum
if ((nInvoiceOrigSum != 0) && (objInv.Payer == "") || (bCapital == true))
{
for (int i = 0; i < objInv.InvItems.Count; i++)
{
if (objInv.InvItems[i].RTYPE != 8)
{
Decimal nOrigRSUM = objInv.InvItems[i].RSUM;
objInv.InvItems[i].RSUM = nOrigRSUM * nInvoiceSum / nInvoiceOrigSum;
objInv.InvItems[i].BUYPR = objInv.InvItems[i].BUYPR * nInvoiceSum / nInvoiceOrigSum;
if (nInvoiceOrigSum > nInvoiceSum)
{
if (nOrigRSUM != 0)
{
objInv.InvItems[i].DISCPC = (nOrigRSUM - objInv.InvItems[i].RSUM) / nOrigRSUM;
}
}
else
{
objInv.InvItems[i].UNITPR = objInv.InvItems[i].RSUM / objInv.InvItems[i].NUM;
}
}
}
}
hSql.Close();
}
private bool invoiceContract(Contract objContract, bool bDraft, bool bManual, String Payer, int InvoiceSeqNr, bool bCapital)
{
bool bRet = true;
clsAppConfig objAppConfig = new clsAppConfig();
clsGlobalVariable objGlobal = new clsGlobalVariable();
SCInvoice objInv = new SCInvoice();
clsSqlFactory hSql = new clsSqlFactory();
objInv.InvoiceSeqNr = InvoiceSeqNr;
if (Payer == "")
{
if (objContract.InvoiceCustId != null)
{
objInv.CustNo = objContract.InvoiceCustId.CustNr;
}
else
{
objInv.CustNo = objContract.ContractCustId.CustNr;
}
}
else
{
objInv.CustNo = objAppConfig.getNumberParam("ZSCCAPPAYE", Payer, "V1", "");
}
objInv.DCustNo = objContract.ContractCustId.CustNr;
objInv.UnitId = objContract.InvoiceSiteId.strValue1;
objInv.ContractOID = objContract.ContractOID;
objInv.ExtOrderId = objContract.ExtContractNo;
if (objInv.ExtOrderId.Length > 10)
{
objInv.ExtOrderId = objInv.ExtOrderId.Substring(0, 10);
}
objInv.SalesType = objAppConfig.getStringParam("ZSCSETTING", "TRTYPE", "C3", "");
objInv.SmanId = objContract.RespSmanId.SmanId;
objInv.TPCODE = objContract.ContractPaymentData.PaymentTerm.strValue1;
objInv.TPTIME = objAppConfig.getNumberParam("MAKSUEHDOT", objInv.TPCODE, "V1", "");
if (objContract.CostCenter.strValue1 != "")
{
hSql.NewCommand("select isnull(a.C4,'') from CORW a where a.CODAID='ZSCCOSTCC' and a.C3=? and a.C5=? ");
hSql.Com.Parameters.Add("UNITID", objContract.SiteId.strValue1);
hSql.Com.Parameters.Add("COSTCENTER", objContract.CostCenter.strValue1);
hSql.ExecuteReader();
if (hSql.Read())
{
objInv.DeptId = hSql.Reader.GetString(0);
}
}
objInv.Payer = Payer;
objInv.OUserId = objGlobal.DMSFirstUserName;
objInv.VehiId = objContract.VehiId.VehiId;
objInv.Note = objContract.ContractNo.ToString() + "/" + objContract.VersionNo.ToString() + "/" + objInv.InvoiceSeqNr.ToString();
hSql.NewCommand("select isnull(a.C4,''), isnull(a.C5,'') from CORW a, VEHI b where a.CODAID='ZSCCONV' and a.C2='VEHICLASS2BTYPE' and a.C3=b.CLASS and b.VEHIID=? ");
hSql.Com.Parameters.Add("VEHIID", objContract.VehiId.VehiId);
hSql.ExecuteReader();
if (hSql.Read())
{
objInv.BTYPE = hSql.Reader.GetString(0);
objInv.PartPostFix = hSql.Reader.GetString(1);
}
if (objContract.ContractPaymentData.PaymentGroupingLevel == PaymentGroupingType.Customer)
{
objInv.BTYPE = "0";
}
else
{
if (objContract.PaymentCollecType == PaymentCollectionType.Transfer)
{
objInv.BTYPE = objAppConfig.getStringParam("ZSCSETTING", "INVCAT4", "C3", "");
}
else if (objContract.PaymentCollecType == PaymentCollectionType.Debit)
{
objInv.BTYPE = objAppConfig.getStringParam("ZSCSETTING", "INVCAT5", "C3", "");
}
else if (objContract.PaymentCollecType == PaymentCollectionType.Plain)
{
objInv.BTYPE = objAppConfig.getStringParam("ZSCSETTING", "INVCAT6", "C3", "");
}
else
{
if (bManual == true)
{
objInv.BTYPE = objAppConfig.getStringParam("ZSCSETTING", "INVCAT2", "C3", "");
}
else
{
objInv.BTYPE = objAppConfig.getStringParam("ZSCSETTING", "INVCAT1", "C3", "");
}
}
}
if (Payer != "")
{
objInv.BTYPE = objAppConfig.getStringParam("ZSCCAPPAYE", Payer, "C3", "");
}
if (objContract.NextInvoiceDate == DateTime.MinValue)
{
objInv.BILLD = DateTime.Now;
}
else
{
objInv.BILLD = objContract.NextInvoiceDate;
}
int nInvoiceDay = objContract.ContractPaymentData.InvoiceDate;
if (nInvoiceDay <= 0)
{
nInvoiceDay = objAppConfig.getNumberParam("ZSCSETTING", "INVDATE", "V1", "");
}
if (objInv.BILLD.Day > nInvoiceDay)
{
objInv.BILLD = new DateTime(objInv.BILLD.Year, objInv.BILLD.Month, nInvoiceDay);
objInv.BILLD = objInv.BILLD.AddMonths(1);
}
else if (objInv.BILLD.Day < nInvoiceDay)
{
objInv.BILLD = new DateTime(objInv.BILLD.Year, objInv.BILLD.Month, nInvoiceDay);
}
objInv.CBILLD = new DateTime(objInv.BILLD.Year, objInv.BILLD.Month, objInv.BILLD.Day);
switch (objContract.ContractPaymentData.PaymentPeriod.strValue1)
{
case PaymentPeriodType.Quarterly:
objInv.NBILLD = objInv.CBILLD.AddMonths(3);
break;
case PaymentPeriodType.HalfYear:
objInv.NBILLD = objInv.CBILLD.AddMonths(6);
break;
case PaymentPeriodType.Yearly:
objInv.NBILLD = objInv.CBILLD.AddMonths(12);
break;
default:
objInv.NBILLD = objInv.CBILLD.AddMonths(1);
break;
}
objInv.DELD = objInv.BILLD;
addInvoiceText(hSql, objContract, ref objInv);
objContract.listContractOptions = ContractOption.getContractOption(objContract.ContractOID);
//objContract.loadDetail();
addInvoiceRows(objContract, ref objInv, bCapital);
hSql.Close();
bRet = objInv.saveOrder(bDraft);
if ((bRet == true) && (bManual == true))
{
if (bDraft == true)
{
openInvoice(objInv.UnitId, objInv.SSALID, objInv.SRECNO);
}
else
{
openInvoicePDF(objInv.UnitId, objInv.SRECNO);
}
}
return(bRet);
}