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(); }
public static List <SCInvoiceItem> getInvoiceDetail(Int32 invoiceID) { if (invoiceID < 0) { return(new List <SCInvoiceItem>()); } clsSqlFactory hSql = new clsSqlFactory(); List <SCInvoiceItem> Result = new List <SCInvoiceItem>(); try { clsGlobalVariable objGlobal = new clsGlobalVariable(); String strSql = "select isnull(c.ITEM,'') as ITEMNO, isnull(c.NAME,'') as ITEMNAME,isnull(c.VATCD,'') as VATCD,isnull(c.NUM,0) as NUM,isnull(c.RSUM,0) as RSUM,isnull(c.NOTE,'') as NOTE,c.RTYPE as RTYPE " + ", isnull(c.RSUM,0) / isnull(dbo.fn_AMVATValue(c._UNITID,c.VATCD),1) as RSUM0 " + " FROM ZSC_ContractInvoice a, all_sbil b, ALL_SROW c " + " WHERE a.OID=? and a.SSALID = b.SSALID and a.UnitId = b._UNITID and b._unitid = c._unitid and b.srecno = c.srecno and b.ssalid = c.ssalid"; hSql.NewCommand(strSql); hSql.Com.Parameters.AddWithValue("OID", invoiceID); hSql.ExecuteReader(); int colId; while (hSql.Read()) { SCInvoiceItem item = new SCInvoiceItem(); colId = hSql.Reader.GetOrdinal("ITEMNO"); if (!hSql.Reader.IsDBNull(colId)) { item.ITEMNO = hSql.Reader.GetString(colId); } colId = hSql.Reader.GetOrdinal("ITEMNAME"); if (!hSql.Reader.IsDBNull(colId)) { item.NAME = hSql.Reader.GetString(colId); } colId = hSql.Reader.GetOrdinal("VATCD"); if (!hSql.Reader.IsDBNull(colId)) { item.VATCD = hSql.Reader.GetString(colId); } colId = hSql.Reader.GetOrdinal("NUM"); if (!hSql.Reader.IsDBNull(colId)) { item.NUM = hSql.Reader.GetDecimal(colId); } colId = hSql.Reader.GetOrdinal("RSUM"); if (!hSql.Reader.IsDBNull(colId)) { item.RSUM = hSql.Reader.GetDecimal(colId); } colId = hSql.Reader.GetOrdinal("RSUM0"); if (!hSql.Reader.IsDBNull(colId)) { item.RSUM0 = hSql.Reader.GetDecimal(colId); } colId = hSql.Reader.GetOrdinal("NOTE"); if (!hSql.Reader.IsDBNull(colId)) { item.NOTE = hSql.Reader.GetString(colId); } colId = hSql.Reader.GetOrdinal("RTYPE"); if (!hSql.Reader.IsDBNull(colId)) { item.RTYPE = hSql.Reader.GetInt16(colId); } Result.Add(item); } } catch (Exception ex) { _log.Error("ERROR getInvoiceDetail " + invoiceID + ": ", ex); throw ex; } finally { hSql.Close(); } return(Result); }