public void ProcessExcelSheet(UploadFileModel model, string fileName, string savePath)
{
try
{
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart workBk = doc.WorkbookPart;
WorksheetPart workSht = workBk.WorksheetParts.First();
SheetData shtData = workSht.Worksheet.Elements<SheetData>().First();
Sheet theSheet = workBk.Workbook.Descendants<Sheet>().
Where(s => s.SheetId == 1).FirstOrDefault();
var strData = "<ENVELOPE><HEADER><TALLYREQUEST>Import Data</TALLYREQUEST></HEADER><BODY><IMPORTDATA>" +
"<REQUESTDESC> <REPORTNAME>All Masters</REPORTNAME><STATICVARIABLES><SVCURRENTCOMPANY>" + model.Company +
"</SVCURRENTCOMPANY></STATICVARIABLES></REQUESTDESC><REQUESTDATA>";
string cellDbShip; string cellFrmDate; string cellToDate;
string cellA; string cellC; string cellD; string cellG;
string cellI; string cellJ; string cellL; string cellN;
string cellQ; string cellS; string cellU; string cellV;
int rows = 0;
var itemCode = ""; var itemName = ""; var pmtInstrument = ""; var ordYrWk = "";
var invNo = ""; var invType = ""; var invDate = ""; var invYrWk = "";
decimal qty; decimal priceWOTax; decimal VAT; decimal priceInclVAT;
var item = "";
List<StockItem> stockItems = new List<StockItem>();
List<Invoice> invoices = new List<Invoice>();
List<InvoiceItem> invoiceItems = new List<InvoiceItem>();
cellDbShip = XcelServiceHelper.GetCellValue(workBk,theSheet, "B4");
cellFrmDate = XcelServiceHelper.GetCellValue(workBk, theSheet, "M4");
cellToDate = XcelServiceHelper.GetCellValue(workBk, theSheet, "R4");
Invoice inv;
InvoiceItem invItem;
int rowCount = shtData.Elements<Row>().Count();
rowCount = rowCount + 1;
for (rows = 1; rows <= rowCount; rows++)
{
cellD = "D" + rows;
if (rows >= 7)
{
string theCell = XcelServiceHelper.GetCellValue(workBk, theSheet, cellD);
if (!string.IsNullOrEmpty(theCell))
{
cellA = "A" + rows; //itemcode
cellC = "C" + rows; // Pmt instrument
cellD = "D" + rows; // item name
cellG = "G" + rows; // ord year wk
cellI = "I" + rows; // inv no
cellJ = "J" + rows; //inv type
cellL = "L" + rows; //inv date
cellN = "N" + rows; //inv yr wk
cellQ = "Q" + rows; //qty
cellS = "S" + rows; //price w/out tax
cellU = "U" + rows; //VAT
cellV = "V" + rows; //price incl. of vat
string ratePercVal = "";
itemCode = XcelServiceHelper.GetCellValue(workBk, theSheet, cellA);
itemName = XcelServiceHelper.GetCellValue(workBk, theSheet, cellD);
itemName = itemName.Replace("(", " ");
itemName = itemName.Replace(")", " ");
itemName = itemName.Replace("&", "_");
itemName = itemName.Replace("'", " ");
item = itemName + " - " + itemCode;
StockItem s = stockItems.FirstOrDefault(x => x.ItemCode == itemCode.Trim());
if (s == null)
{
if (itemName.StartsWith("PPP")) ratePercVal = " 5";
else ratePercVal = " 12.5";
strData = strData + @"<TALLYMESSAGE xmlns:UDF=""TallyUDF"">" +
@"<STOCKITEM NAME="""
+ item +
@""" RESERVEDNAME="""">" +
@"<OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS></OLDAUDITENTRYIDS.LIST>" +
"<PARENT>Tupperware Products</PARENT><CATEGORY/><TAXCLASSIFICATIONNAME/><COSTINGMETHOD>Avg. Cost</COSTINGMETHOD>" +
"<VALUATIONMETHOD>Avg. Price</VALUATIONMETHOD><BASEUNITS>NO</BASEUNITS><ADDITIONALUNITS/><EXCISEITEMCLASSIFICATION/>" +
"<ISCOSTCENTRESON>No</ISCOSTCENTRESON><ISBATCHWISEON>No</ISBATCHWISEON><ISPERISHABLEON>No</ISPERISHABLEON><ISENTRYTAXAPPLICABLE>No</ISENTRYTAXAPPLICABLE>" +
"<ISCOSTTRACKINGON>No</ISCOSTTRACKINGON><IGNOREPHYSICALDIFFERENCE>No</IGNOREPHYSICALDIFFERENCE><IGNORENEGATIVESTOCK>No</IGNORENEGATIVESTOCK>" +
"<TREATSALESASMANUFACTURED>No</TREATSALESASMANUFACTURED><TREATPURCHASESASCONSUMED>No</TREATPURCHASESASCONSUMED><TREATREJECTSASSCRAP>No</TREATREJECTSASSCRAP>" +
"<HASMFGDATE>No</HASMFGDATE><ALLOWUSEOFEXPIREDITEMS>No</ALLOWUSEOFEXPIREDITEMS><IGNOREBATCHES>No</IGNOREBATCHES><IGNOREGODOWNS>No</IGNOREGODOWNS>" +
"<CALCONMRP>No</CALCONMRP><EXCLUDEJRNLFORVALUATION>No</EXCLUDEJRNLFORVALUATION><ISMRPINCLOFTAX>No</ISMRPINCLOFTAX><ISADDLTAXEXEMPT>No</ISADDLTAXEXEMPT>" +
"<ISSUPPLEMENTRYDUTYON>No</ISSUPPLEMENTRYDUTYON><REORDERASHIGHER>No</REORDERASHIGHER><MINORDERASHIGHER>No</MINORDERASHIGHER><DENOMINATOR> 1</DENOMINATOR>" +
@"<RATEOFVAT>" + ratePercVal + "</RATEOFVAT><LANGUAGENAME.LIST>" +
@"<NAME.LIST TYPE=""String""><NAME>" + item + "</NAME></NAME.LIST><LANGUAGEID> 1033</LANGUAGEID>" +
"</LANGUAGENAME.LIST><SCHVIDETAILS.LIST> </SCHVIDETAILS.LIST><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST><ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST>" +
"<AUDITENTRIES.LIST> </AUDITENTRIES.LIST><COMPONENTLIST.LIST> </COMPONENTLIST.LIST><ADDITIONALLEDGERS.LIST> </ADDITIONALLEDGERS.LIST>" +
"<SALESLIST.LIST> </SALESLIST.LIST><PURCHASELIST.LIST> </PURCHASELIST.LIST><FULLPRICELIST.LIST> </FULLPRICELIST.LIST>" +
"<BATCHALLOCATIONS.LIST> </BATCHALLOCATIONS.LIST><TRADEREXCISEDUTIES.LIST> </TRADEREXCISEDUTIES.LIST><STANDARDCOSTLIST.LIST> </STANDARDCOSTLIST.LIST>" +
"<STANDARDPRICELIST.LIST> </STANDARDPRICELIST.LIST><EXCISEITEMGODOWN.LIST> </EXCISEITEMGODOWN.LIST><MULTICOMPONENTLIST.LIST> </MULTICOMPONENTLIST.LIST>" +
"<PRICELEVELLIST.LIST> </PRICELEVELLIST.LIST></STOCKITEM></TALLYMESSAGE>";
s = new StockItem
{
ItemCode = itemCode.Trim().ToString(),
ItemName = itemName.Trim().ToString()
};
stockItems.Add(s);
}
pmtInstrument = XcelServiceHelper.GetCellValue(workBk, theSheet, cellC);
ordYrWk = XcelServiceHelper.GetCellValue(workBk, theSheet, cellG);
invYrWk = XcelServiceHelper.GetCellValue(workBk, theSheet, cellN);
invNo = XcelServiceHelper.GetCellValue(workBk, theSheet, cellI);
invDate = XcelServiceHelper.GetCellValue(workBk, theSheet, cellL);
invType = XcelServiceHelper.GetCellValue(workBk, theSheet, cellJ);
qty = Decimal.Parse(XcelServiceHelper.GetCellValue(workBk, theSheet, cellQ));
priceWOTax = Decimal.Parse(XcelServiceHelper.GetCellValue(workBk, theSheet, cellS));
VAT = Decimal.Parse(XcelServiceHelper.GetCellValue(workBk, theSheet, cellU));
priceInclVAT= Decimal.Parse(XcelServiceHelper.GetCellValue(workBk, theSheet, cellV));
inv = invoices.FirstOrDefault(x => x.InvoiceNo == invNo.Trim());
if (inv == null)
{
inv = new Invoice
{
InvoiceNo = invNo.Trim(),
InvoiceDate = DateTime.Parse(invDate),
InvoiceType = invType.Trim(),
InvYearWk = invYrWk.Trim(),
OrdYearWk = ordYrWk.Trim(),
VoucherNo = invType.Trim() + "-" + invNo.Trim()
};
invoices.Add(inv);
}
invItem = inv.Items.FirstOrDefault(x => x.ItemCode == itemCode.Trim() && x.PaymentInstrument == pmtInstrument.Trim());
if (invItem == null)
{
invItem = new InvoiceItem
{
InvoiceNo = invNo.Trim(),
ItemCode = itemCode.Trim(),
ItemName = itemName.Trim(),
PaymentInstrument = pmtInstrument.Trim(),
Quantity = qty,
PriceWOTax = priceWOTax,
VAT = VAT,
PriceInclVAT = priceInclVAT
};
inv.Items.Add(invItem);
}
}
}
}
foreach (var invoice in invoices)
{
decimal invTotalAmt = invoice.Items.Sum(x => x.PriceInclVAT);
decimal invTotalAmt_12_5 = invoice.Items.Where(x => !x.ItemName.StartsWith("PPP")).Sum(x => x.PriceWOTax);
decimal invTotalVAT_12_5 = invoice.Items.Where(x => !x.ItemName.StartsWith("PPP")).Sum(x => x.VAT);
strData = strData + @"<TALLYMESSAGE xmlns:UDF=""TallyUDF"">" +
@"<VOUCHER VCHTYPE=""Purchase"" ACTION=""Create"" OBJVIEW=""Invoice Voucher View"">" +
@"<OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS></OLDAUDITENTRYIDS.LIST>" +
"<DATE>" + invoice.InvoiceDate + "</DATE>" +
"<GUID></GUID><PARTYNAME>Tupperware India Pvt Ltd</PARTYNAME><VOUCHERTYPENAME>Purchase</VOUCHERTYPENAME>" +
"<VOUCHERNUMBER>" + invoice.VoucherNo + "</VOUCHERNUMBER><PARTYLEDGERNAME>TUPPERWARE INDIA PVT LTD</PARTYLEDGERNAME>" +
"<BASICBASEPARTYNAME>Tupperware India Pvt Ltd</BASICBASEPARTYNAME><CSTFORMISSUETYPE/><CSTFORMRECVTYPE/>" +
"<FBTPAYMENTTYPE>Default</FBTPAYMENTTYPE><PERSISTEDVIEW>Invoice Voucher View</PERSISTEDVIEW>" +
"<VCHGSTCLASS/><VOUCHERTYPEORIGNAME>Purchase</VOUCHERTYPEORIGNAME><DIFFACTUALQTY>No</DIFFACTUALQTY>" +
"<ISMSTFROMSYNC>No</ISMSTFROMSYNC><ASORIGINAL>No</ASORIGINAL><AUDITED>No</AUDITED><FORJOBCOSTING>No</FORJOBCOSTING>" +
"<ISOPTIONAL>No</ISOPTIONAL><EFFECTIVEDATE>" + invoice.InvoiceDate + "</EFFECTIVEDATE><USEFOREXCISE>No</USEFOREXCISE>" +
"<ISFORJOBWORKIN>No</ISFORJOBWORKIN><ALLOWCONSUMPTION>No</ALLOWCONSUMPTION><USEFORINTEREST>No</USEFORINTEREST>" +
"<USEFORGAINLOSS>No</USEFORGAINLOSS><USEFORGODOWNTRANSFER>No</USEFORGODOWNTRANSFER><USEFORCOMPOUND>No</USEFORCOMPOUND>" +
"<USEFORSERVICETAX>No</USEFORSERVICETAX><ISEXCISEVOUCHER>No</ISEXCISEVOUCHER><EXCISETAXOVERRIDE>No</EXCISETAXOVERRIDE>" +
"<USEFORFINALPRODUCTION>No</USEFORFINALPRODUCTION><ISTDSOVERRIDDEN>No</ISTDSOVERRIDDEN><ISTCSOVERRIDDEN>No</ISTCSOVERRIDDEN>" +
"<ISTDSTCSCASHVCH>No</ISTDSTCSCASHVCH><INCLUDEADVPYMTVCH>No</INCLUDEADVPYMTVCH><ISSUBWORKSCONTRACT>No</ISSUBWORKSCONTRACT>" +
"<ISVATOVERRIDDEN>No</ISVATOVERRIDDEN><IGNOREORIGVCHDATE>No</IGNOREORIGVCHDATE><ISSERVICETAXOVERRIDDEN>No</ISSERVICETAXOVERRIDDEN>" +
"<ISISDVOUCHER>No</ISISDVOUCHER><ISEXCISEOVERRIDDEN>No</ISEXCISEOVERRIDDEN><ISEXCISESUPPLYVCH>No</ISEXCISESUPPLYVCH>" +
"<ISCANCELLED>No</ISCANCELLED><HASCASHFLOW>No</HASCASHFLOW><ISPOSTDATED>No</ISPOSTDATED><USETRACKINGNUMBER>No</USETRACKINGNUMBER>" +
"<ISINVOICE>Yes</ISINVOICE><MFGJOURNAL>No</MFGJOURNAL><HASDISCOUNTS>No</HASDISCOUNTS><ASPAYSLIP>No</ASPAYSLIP>" +
"<ISCOSTCENTRE>No</ISCOSTCENTRE><ISSTXNONREALIZEDVCH>No</ISSTXNONREALIZEDVCH><ISEXCISEMANUFACTURERON>Yes</ISEXCISEMANUFACTURERON>" +
"<ISBLANKCHEQUE>No</ISBLANKCHEQUE><ISVOID>No</ISVOID><ISONHOLD>No</ISONHOLD><ORDERLINESTATUS>No</ORDERLINESTATUS>" +
"<ISDELETED>No</ISDELETED><CHANGEVCHMODE>No</CHANGEVCHMODE><ALTERID></ALTERID><MASTERID></MASTERID><VOUCHERKEY></VOUCHERKEY>" +
"<EXCLUDEDTAXATIONS.LIST> </EXCLUDEDTAXATIONS.LIST><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST>" +
"<ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST><AUDITENTRIES.LIST> </AUDITENTRIES.LIST>" +
"<DUTYHEADDETAILS.LIST> </DUTYHEADDETAILS.LIST><SUPPLEMENTARYDUTYHEADDETAILS.LIST> </SUPPLEMENTARYDUTYHEADDETAILS.LIST>" +
"<INVOICEDELNOTES.LIST> </INVOICEDELNOTES.LIST><INVOICEORDERLIST.LIST> </INVOICEORDERLIST.LIST><INVOICEINDENTLIST.LIST> </INVOICEINDENTLIST.LIST>" +
"<ATTENDANCEENTRIES.LIST> </ATTENDANCEENTRIES.LIST><ORIGINVOICEDETAILS.LIST> </ORIGINVOICEDETAILS.LIST>" +
"<INVOICEEXPORTLIST.LIST> </INVOICEEXPORTLIST.LIST><LEDGERENTRIES.LIST>" +
@"<OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS></OLDAUDITENTRYIDS.LIST>" +
"<LEDGERNAME>TUPPERWARE INDIA PVT LTD</LEDGERNAME><GSTCLASS/><ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE>" +
"<LEDGERFROMITEM>No</LEDGERFROMITEM><REMOVEZEROENTRIES>No</REMOVEZEROENTRIES><ISPARTYLEDGER>Yes</ISPARTYLEDGER>" +
"<ISLASTDEEMEDPOSITIVE>No</ISLASTDEEMEDPOSITIVE><AMOUNT>" + invTotalAmt + "</AMOUNT><SERVICETAXDETAILS.LIST> </SERVICETAXDETAILS.LIST>" +
"<BANKALLOCATIONS.LIST> </BANKALLOCATIONS.LIST><BILLALLOCATIONS.LIST><NAME>" + invoice.VoucherNo + "</NAME><BILLTYPE>New Ref</BILLTYPE>" +
"<TDSDEDUCTEEISSPECIALRATE>No</TDSDEDUCTEEISSPECIALRATE><AMOUNT>" + invTotalAmt + "</AMOUNT><INTERESTCOLLECTION.LIST> </INTERESTCOLLECTION.LIST>" +
"<STBILLCATEGORIES.LIST> </STBILLCATEGORIES.LIST></BILLALLOCATIONS.LIST><INTERESTCOLLECTION.LIST> </INTERESTCOLLECTION.LIST>" +
"<OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST><ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST>" +
"<AUDITENTRIES.LIST> </AUDITENTRIES.LIST><INPUTCRALLOCS.LIST> </INPUTCRALLOCS.LIST><DUTYHEADDETAILS.LIST> </DUTYHEADDETAILS.LIST>" +
"<EXCISEDUTYHEADDETAILS.LIST> </EXCISEDUTYHEADDETAILS.LIST><SUMMARYALLOCS.LIST> </SUMMARYALLOCS.LIST><STPYMTDETAILS.LIST> </STPYMTDETAILS.LIST>" +
"<EXCISEPAYMENTALLOCATIONS.LIST> </EXCISEPAYMENTALLOCATIONS.LIST><TAXBILLALLOCATIONS.LIST> </TAXBILLALLOCATIONS.LIST><TAXOBJECTALLOCATIONS.LIST> </TAXOBJECTALLOCATIONS.LIST>" +
"<TDSEXPENSEALLOCATIONS.LIST> </TDSEXPENSEALLOCATIONS.LIST><VATSTATUTORYDETAILS.LIST>Yes</VATSTATUTORYDETAILS.LIST><COSTTRACKALLOCATIONS.LIST> </COSTTRACKALLOCATIONS.LIST>" +
"<REFVOUCHERDETAILS.LIST> </REFVOUCHERDETAILS.LIST><INVOICEWISEDETAILS.LIST> </INVOICEWISEDETAILS.LIST></LEDGERENTRIES.LIST>" +
@"<LEDGERENTRIES.LIST><OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS></OLDAUDITENTRYIDS.LIST>" +
@"<BASICRATEOFINVOICETAX.LIST TYPE=""Number""><BASICRATEOFINVOICETAX> 12.50</BASICRATEOFINVOICETAX></BASICRATEOFINVOICETAX.LIST>" +
"<TAXCLASSIFICATIONNAME>Input VAT @ 12.5%</TAXCLASSIFICATIONNAME><LEDGERNAME>Input Vat @12.5 %</LEDGERNAME><GSTCLASS/>" +
"<ISDEEMEDPOSITIVE>Yes</ISDEEMEDPOSITIVE><LEDGERFROMITEM>No</LEDGERFROMITEM><REMOVEZEROENTRIES>No</REMOVEZEROENTRIES><ISPARTYLEDGER>No</ISPARTYLEDGER>" +
"<ISLASTDEEMEDPOSITIVE>Yes</ISLASTDEEMEDPOSITIVE><AMOUNT>-" + invTotalVAT_12_5 + " </AMOUNT><VATASSESSABLEVALUE>-" + invTotalAmt_12_5 + "</VATASSESSABLEVALUE>" +
"<SERVICETAXDETAILS.LIST> </SERVICETAXDETAILS.LIST><BANKALLOCATIONS.LIST> </BANKALLOCATIONS.LIST><BILLALLOCATIONS.LIST> </BILLALLOCATIONS.LIST>" +
"<INTERESTCOLLECTION.LIST> </INTERESTCOLLECTION.LIST><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST><ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST>" +
"<AUDITENTRIES.LIST> </AUDITENTRIES.LIST><INPUTCRALLOCS.LIST> </INPUTCRALLOCS.LIST><DUTYHEADDETAILS.LIST> </DUTYHEADDETAILS.LIST>" +
"<EXCISEDUTYHEADDETAILS.LIST> </EXCISEDUTYHEADDETAILS.LIST><SUMMARYALLOCS.LIST> </SUMMARYALLOCS.LIST><STPYMTDETAILS.LIST> </STPYMTDETAILS.LIST>" +
"<EXCISEPAYMENTALLOCATIONS.LIST> </EXCISEPAYMENTALLOCATIONS.LIST><TAXBILLALLOCATIONS.LIST> </TAXBILLALLOCATIONS.LIST><TAXOBJECTALLOCATIONS.LIST>" +
"<CATEGORY>Input VAT @ 12.5%</CATEGORY><TAXTYPE>VAT</TAXTYPE><TAXNAME>123456</TAXNAME><PARTYLEDGER>TUPPERWARE INDIA PVT LTD</PARTYLEDGER><REFTYPE>Agst Ref</REFTYPE>" +
"<ISOPTIONAL>No</ISOPTIONAL><ISPANVALID>No</ISPANVALID><ZERORATED>No</ZERORATED><EXEMPTED>No</EXEMPTED><ISSPECIALRATE>No</ISSPECIALRATE><ISDEDUCTNOW>No</ISDEDUCTNOW>" +
"<ISPANNOTAVAILABLE>No</ISPANNOTAVAILABLE><ISSUPPLEMENTARY>No</ISSUPPLEMENTARY><HASINPUTCREDIT>No</HASINPUTCREDIT><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST>" +
"<ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST><AUDITENTRIES.LIST> </AUDITENTRIES.LIST>";
#region 12.5% VAT- Subcategory List
var VAT_12_5_Items = invoice.Items.Where(x => !x.ItemName.StartsWith("PPP")).ToList();
foreach (var itm in VAT_12_5_Items)
{
strData = strData + "<SUBCATEGORYALLOCATION.LIST>" +
"<STOCKITEMNAME>" + itm.ItemName + "-" + itm.ItemCode + "</STOCKITEMNAME>" +
"<SUBCATEGORY>VAT</SUBCATEGORY>" +
"<DUTYLEDGER>Input Vat @12.5 %</DUTYLEDGER>" +
"<SUBCATZERORATED>No</SUBCATZERORATED>" +
"<SUBCATEXEMPTED>No</SUBCATEXEMPTED>" +
"<SUBCATISSPECIALRATE>No</SUBCATISSPECIALRATE>" +
"<TAXRATE> 12.50</TAXRATE>" +
"<ASSESSABLEAMOUNT>-" + itm.PriceWOTax + "</ASSESSABLEAMOUNT>" +
"<TAX>-" + itm.PriceWOTax + "</TAX>" +
"<BILLEDQTY> " + itm.Quantity + " no</BILLEDQTY>" +
"</SUBCATEGORYALLOCATION.LIST>";
}
#endregion
strData = strData + "</TAXOBJECTALLOCATIONS.LIST><TDSEXPENSEALLOCATIONS.LIST> </TDSEXPENSEALLOCATIONS.LIST>" +
"<VATSTATUTORYDETAILS.LIST> </VATSTATUTORYDETAILS.LIST><COSTTRACKALLOCATIONS.LIST> </COSTTRACKALLOCATIONS.LIST>" +
"<REFVOUCHERDETAILS.LIST> </REFVOUCHERDETAILS.LIST><INVOICEWISEDETAILS.LIST> </INVOICEWISEDETAILS.LIST>" +
"</LEDGERENTRIES.LIST>";
#region 5% VAT- Subcategory List
var VAT_5_Items = invoice.Items.Where(x => x.ItemName.StartsWith("PPP")).ToList();
if (VAT_5_Items.Count > 0)
{
decimal invTotalAmt_5 = invoice.Items.Where(x => x.ItemName.StartsWith("PPP")).Sum(x => x.PriceWOTax);
decimal invTotalVAT_5 = invoice.Items.Where(x => x.ItemName.StartsWith("PPP")).Sum(x => x.VAT);
strData = strData + "<LEDGERENTRIES.LIST>" +
@"<OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS></OLDAUDITENTRYIDS.LIST>" +
@"<BASICRATEOFINVOICETAX.LIST TYPE=""Number""><BASICRATEOFINVOICETAX> 5</BASICRATEOFINVOICETAX></BASICRATEOFINVOICETAX.LIST>" +
"<TAXCLASSIFICATIONNAME>Input VAT @ 5%</TAXCLASSIFICATIONNAME><LEDGERNAME>Input Vat @5 %</LEDGERNAME><GSTCLASS/>" +
"<ISDEEMEDPOSITIVE>Yes</ISDEEMEDPOSITIVE><LEDGERFROMITEM>No</LEDGERFROMITEM><REMOVEZEROENTRIES>No</REMOVEZEROENTRIES>" +
"<ISPARTYLEDGER>No</ISPARTYLEDGER><ISLASTDEEMEDPOSITIVE>Yes</ISLASTDEEMEDPOSITIVE><AMOUNT>-" + invTotalVAT_5 + "</AMOUNT>" +
"<VATASSESSABLEVALUE>-" + invTotalAmt_5 + "</VATASSESSABLEVALUE><SERVICETAXDETAILS.LIST> </SERVICETAXDETAILS.LIST>" +
"<BANKALLOCATIONS.LIST> </BANKALLOCATIONS.LIST><BILLALLOCATIONS.LIST> </BILLALLOCATIONS.LIST>" +
"<INTERESTCOLLECTION.LIST> </INTERESTCOLLECTION.LIST><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST>" +
"<ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST><AUDITENTRIES.LIST> </AUDITENTRIES.LIST>" +
"<INPUTCRALLOCS.LIST> </INPUTCRALLOCS.LIST><DUTYHEADDETAILS.LIST> </DUTYHEADDETAILS.LIST>" +
"<EXCISEDUTYHEADDETAILS.LIST> </EXCISEDUTYHEADDETAILS.LIST><SUMMARYALLOCS.LIST> </SUMMARYALLOCS.LIST>" +
"<STPYMTDETAILS.LIST> </STPYMTDETAILS.LIST><EXCISEPAYMENTALLOCATIONS.LIST> </EXCISEPAYMENTALLOCATIONS.LIST>" +
"<TAXBILLALLOCATIONS.LIST> </TAXBILLALLOCATIONS.LIST><TAXOBJECTALLOCATIONS.LIST><CATEGORY>Input VAT @ 5%</CATEGORY>" +
"<TAXTYPE>VAT</TAXTYPE><TAXNAME>123456</TAXNAME><PARTYLEDGER>TUPPERWARE INDIA PVT LTD</PARTYLEDGER><REFTYPE>Agst Ref</REFTYPE>" +
"<ISOPTIONAL>No</ISOPTIONAL><ISPANVALID>No</ISPANVALID><ZERORATED>No</ZERORATED><EXEMPTED>No</EXEMPTED><ISSPECIALRATE>No</ISSPECIALRATE>" +
"<ISDEDUCTNOW>No</ISDEDUCTNOW><ISPANNOTAVAILABLE>No</ISPANNOTAVAILABLE><ISSUPPLEMENTARY>No</ISSUPPLEMENTARY><ISPUREAGENT>No</ISPUREAGENT>" +
"<HASINPUTCREDIT>No</HASINPUTCREDIT><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST><ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST>" +
"<AUDITENTRIES.LIST> </AUDITENTRIES.LIST>";
foreach (var itm in VAT_5_Items)
{
strData = strData + "<SUBCATEGORYALLOCATION.LIST>" +
"<STOCKITEMNAME>" + itm.ItemName + "-" + itm.ItemCode + "</STOCKITEMNAME>" +
"<SUBCATEGORY>VAT</SUBCATEGORY>" +
"<DUTYLEDGER>Input Vat @12.5 %</DUTYLEDGER>" +
"<SUBCATZERORATED>No</SUBCATZERORATED>" +
"<SUBCATEXEMPTED>No</SUBCATEXEMPTED>" +
"<SUBCATISSPECIALRATE>No</SUBCATISSPECIALRATE>" +
"<TAXRATE> 12.50</TAXRATE>" +
"<ASSESSABLEAMOUNT>-" + itm.PriceWOTax + "</ASSESSABLEAMOUNT>" +
"<TAX>-" + itm.PriceWOTax + "</TAX>" +
"<BILLEDQTY> " + itm.Quantity + " no</BILLEDQTY>" +
"</SUBCATEGORYALLOCATION.LIST>";
}
strData = strData + "</TAXOBJECTALLOCATIONS.LIST><TDSEXPENSEALLOCATIONS.LIST> </TDSEXPENSEALLOCATIONS.LIST>" +
"<VATSTATUTORYDETAILS.LIST> </VATSTATUTORYDETAILS.LIST><COSTTRACKALLOCATIONS.LIST> </COSTTRACKALLOCATIONS.LIST>" +
"<REFVOUCHERDETAILS.LIST> </REFVOUCHERDETAILS.LIST><INVOICEWISEDETAILS.LIST> </INVOICEWISEDETAILS.LIST>" +
"</LEDGERENTRIES.LIST>";
}
#endregion
#region 12.5% VAT AllInventoriesList
foreach (var itm in VAT_12_5_Items)
{
decimal itmRate = XcelServiceHelper.TruncateDecimal (itm.PriceWOTax / itm.Quantity,2);
strData = strData + "<ALLINVENTORYENTRIES.LIST>" +
"<STOCKITEMNAME>" + itm.ItemName + " - " + itm.ItemCode + "</STOCKITEMNAME><ISDEEMEDPOSITIVE>Yes</ISDEEMEDPOSITIVE>" +
"<ISLASTDEEMEDPOSITIVE>Yes</ISLASTDEEMEDPOSITIVE><ISAUTONEGATE>No</ISAUTONEGATE><ISCUSTOMSCLEARANCE>No</ISCUSTOMSCLEARANCE>" +
"<ISTRACKCOMPONENT>No</ISTRACKCOMPONENT><ISTRACKPRODUCTION>No</ISTRACKPRODUCTION><ISPRIMARYITEM>No</ISPRIMARYITEM>" +
"<ISSCRAP>No</ISSCRAP><RATE>" + itmRate + "/no</RATE><AMOUNT>-" + itm.PriceWOTax + "</AMOUNT><ACTUALQTY> " + itm.Quantity + " no</ACTUALQTY>" +
"<BILLEDQTY> " + itm.Quantity + " no</BILLEDQTY><BATCHALLOCATIONS.LIST><GODOWNNAME>Main Location</GODOWNNAME><BATCHNAME>Primary Batch</BATCHNAME>" +
"<INDENTNO/><ORDERNO/><TRACKINGNUMBER/><DYNAMICCSTISCLEARED>No</DYNAMICCSTISCLEARED><AMOUNT>-" + itm.PriceWOTax + "</AMOUNT>" +
"<ACTUALQTY> " + itm.Quantity + " no</ACTUALQTY><BILLEDQTY> " + itm.Quantity + " no</BILLEDQTY><ADDITIONALDETAILS.LIST> </ADDITIONALDETAILS.LIST>" +
"<VOUCHERCOMPONENTLIST.LIST> </VOUCHERCOMPONENTLIST.LIST></BATCHALLOCATIONS.LIST><ACCOUNTINGALLOCATIONS.LIST>" +
@"<OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS></OLDAUDITENTRYIDS.LIST><TAXCLASSIFICATIONNAME>Input VAT @ 12.5%</TAXCLASSIFICATIONNAME>" +
"<LEDGERNAME>Purchase @12.5 %</LEDGERNAME><GSTCLASS/><ISDEEMEDPOSITIVE>Yes</ISDEEMEDPOSITIVE><LEDGERFROMITEM>No</LEDGERFROMITEM><REMOVEZEROENTRIES>No</REMOVEZEROENTRIES>" +
"<ISPARTYLEDGER>No</ISPARTYLEDGER><ISLASTDEEMEDPOSITIVE>Yes</ISLASTDEEMEDPOSITIVE><AMOUNT>-" + itm.PriceWOTax + "</AMOUNT><SERVICETAXDETAILS.LIST> </SERVICETAXDETAILS.LIST>" +
"<BANKALLOCATIONS.LIST> </BANKALLOCATIONS.LIST><BILLALLOCATIONS.LIST> </BILLALLOCATIONS.LIST><INTERESTCOLLECTION.LIST> </INTERESTCOLLECTION.LIST>" +
"<OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST><ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST><AUDITENTRIES.LIST> </AUDITENTRIES.LIST>" +
"<INPUTCRALLOCS.LIST> </INPUTCRALLOCS.LIST><DUTYHEADDETAILS.LIST> </DUTYHEADDETAILS.LIST><EXCISEDUTYHEADDETAILS.LIST> </EXCISEDUTYHEADDETAILS.LIST>" +
"<SUMMARYALLOCS.LIST> </SUMMARYALLOCS.LIST><STPYMTDETAILS.LIST> </STPYMTDETAILS.LIST><EXCISEPAYMENTALLOCATIONS.LIST> </EXCISEPAYMENTALLOCATIONS.LIST>" +
"<TAXBILLALLOCATIONS.LIST> </TAXBILLALLOCATIONS.LIST><TAXOBJECTALLOCATIONS.LIST> </TAXOBJECTALLOCATIONS.LIST><TDSEXPENSEALLOCATIONS.LIST> </TDSEXPENSEALLOCATIONS.LIST>" +
"<VATSTATUTORYDETAILS.LIST> </VATSTATUTORYDETAILS.LIST><COSTTRACKALLOCATIONS.LIST> </COSTTRACKALLOCATIONS.LIST><REFVOUCHERDETAILS.LIST> </REFVOUCHERDETAILS.LIST>" +
"<INVOICEWISEDETAILS.LIST> </INVOICEWISEDETAILS.LIST></ACCOUNTINGALLOCATIONS.LIST><DUTYHEADDETAILS.LIST> </DUTYHEADDETAILS.LIST>" +
"<SUPPLEMENTARYDUTYHEADDETAILS.LIST> </SUPPLEMENTARYDUTYHEADDETAILS.LIST><TAXOBJECTALLOCATIONS.LIST> </TAXOBJECTALLOCATIONS.LIST>" +
"<REFVOUCHERDETAILS.LIST> </REFVOUCHERDETAILS.LIST><EXCISEALLOCATIONS.LIST> </EXCISEALLOCATIONS.LIST><EXPENSEALLOCATIONS.LIST> </EXPENSEALLOCATIONS.LIST>" +
"</ALLINVENTORYENTRIES.LIST>";
}
#endregion
#region 5% VAT- AllInventoriesList
foreach (var itm in VAT_5_Items)
{
decimal itmRate = XcelServiceHelper.TruncateDecimal(itm.PriceWOTax / itm.Quantity, 2);
strData = strData + "<ALLINVENTORYENTRIES.LIST>" +
"<STOCKITEMNAME>" + itm.ItemName + " - " + itm.ItemCode + "</STOCKITEMNAME><ISDEEMEDPOSITIVE>Yes</ISDEEMEDPOSITIVE>" +
"<ISLASTDEEMEDPOSITIVE>Yes</ISLASTDEEMEDPOSITIVE><ISAUTONEGATE>No</ISAUTONEGATE><ISCUSTOMSCLEARANCE>No</ISCUSTOMSCLEARANCE>" +
"<ISTRACKCOMPONENT>No</ISTRACKCOMPONENT><ISTRACKPRODUCTION>No</ISTRACKPRODUCTION><ISPRIMARYITEM>No</ISPRIMARYITEM>" +
"<ISSCRAP>No</ISSCRAP><RATE>" + itmRate + "/no</RATE><AMOUNT>-" + itm.PriceWOTax + "</AMOUNT><ACTUALQTY> " + itm.Quantity + " no</ACTUALQTY>" +
"<BILLEDQTY> " + itm.Quantity + " no</BILLEDQTY><BATCHALLOCATIONS.LIST><GODOWNNAME>Main Location</GODOWNNAME><BATCHNAME>Primary Batch</BATCHNAME>" +
"<INDENTNO/><ORDERNO/><TRACKINGNUMBER/><DYNAMICCSTISCLEARED>No</DYNAMICCSTISCLEARED><AMOUNT>-" + itm.PriceWOTax + "</AMOUNT>" +
"<ACTUALQTY> " + itm.Quantity + " no</ACTUALQTY><BILLEDQTY> " + itm.Quantity + " no</BILLEDQTY><ADDITIONALDETAILS.LIST> </ADDITIONALDETAILS.LIST>" +
"<VOUCHERCOMPONENTLIST.LIST> </VOUCHERCOMPONENTLIST.LIST></BATCHALLOCATIONS.LIST><ACCOUNTINGALLOCATIONS.LIST>" +
@"<OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS></OLDAUDITENTRYIDS.LIST><TAXCLASSIFICATIONNAME>Input VAT @ 12.5%</TAXCLASSIFICATIONNAME>" +
"<LEDGERNAME>Purchase @12.5 %</LEDGERNAME><GSTCLASS/><ISDEEMEDPOSITIVE>Yes</ISDEEMEDPOSITIVE><LEDGERFROMITEM>No</LEDGERFROMITEM><REMOVEZEROENTRIES>No</REMOVEZEROENTRIES>" +
"<ISPARTYLEDGER>No</ISPARTYLEDGER><ISLASTDEEMEDPOSITIVE>Yes</ISLASTDEEMEDPOSITIVE><AMOUNT>-" + itm.PriceWOTax + "</AMOUNT><SERVICETAXDETAILS.LIST> </SERVICETAXDETAILS.LIST>" +
"<BANKALLOCATIONS.LIST> </BANKALLOCATIONS.LIST><BILLALLOCATIONS.LIST> </BILLALLOCATIONS.LIST><INTERESTCOLLECTION.LIST> </INTERESTCOLLECTION.LIST>" +
"<OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST><ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST><AUDITENTRIES.LIST> </AUDITENTRIES.LIST>" +
"<INPUTCRALLOCS.LIST> </INPUTCRALLOCS.LIST><DUTYHEADDETAILS.LIST> </DUTYHEADDETAILS.LIST><EXCISEDUTYHEADDETAILS.LIST> </EXCISEDUTYHEADDETAILS.LIST>" +
"<SUMMARYALLOCS.LIST> </SUMMARYALLOCS.LIST><STPYMTDETAILS.LIST> </STPYMTDETAILS.LIST><EXCISEPAYMENTALLOCATIONS.LIST> </EXCISEPAYMENTALLOCATIONS.LIST>" +
"<TAXBILLALLOCATIONS.LIST> </TAXBILLALLOCATIONS.LIST><TAXOBJECTALLOCATIONS.LIST> </TAXOBJECTALLOCATIONS.LIST><TDSEXPENSEALLOCATIONS.LIST> </TDSEXPENSEALLOCATIONS.LIST>" +
"<VATSTATUTORYDETAILS.LIST> </VATSTATUTORYDETAILS.LIST><COSTTRACKALLOCATIONS.LIST> </COSTTRACKALLOCATIONS.LIST><REFVOUCHERDETAILS.LIST> </REFVOUCHERDETAILS.LIST>" +
"<INVOICEWISEDETAILS.LIST> </INVOICEWISEDETAILS.LIST></ACCOUNTINGALLOCATIONS.LIST><DUTYHEADDETAILS.LIST> </DUTYHEADDETAILS.LIST>" +
"<SUPPLEMENTARYDUTYHEADDETAILS.LIST> </SUPPLEMENTARYDUTYHEADDETAILS.LIST><TAXOBJECTALLOCATIONS.LIST> </TAXOBJECTALLOCATIONS.LIST>" +
"<REFVOUCHERDETAILS.LIST> </REFVOUCHERDETAILS.LIST><EXCISEALLOCATIONS.LIST> </EXCISEALLOCATIONS.LIST><EXPENSEALLOCATIONS.LIST> </EXPENSEALLOCATIONS.LIST>" +
"</ALLINVENTORYENTRIES.LIST>";
}
#endregion
strData = strData + "<ATTDRECORDS.LIST> </ATTDRECORDS.LIST>" +
"</VOUCHER></TALLYMESSAGE>";
UpdatePurchaseRegister(invoice, model.Company, cellDbShip, DateTime.Parse(cellFrmDate.ToString()), DateTime.Parse(cellToDate.ToString()));
}
strData = strData + "</REQUESTDATA></IMPORTDATA></BODY></ENVELOPE>";
XmlDocument docum = new XmlDocument();
docum.LoadXml(strData);
if (!Directory.Exists(savePath))
{
Directory.CreateDirectory(savePath);
}
docum.Save(savePath + "//purchase.xml");
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
//public void ProcessExcelSheet(UploadFileModel model, string fileName, string savePath)
//{
// ProcessExcelSheet(model, fileName, savePath, true);
// using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false))
// {
// WorkbookPart workBk = doc.WorkbookPart;
// WorksheetPart workSht = workBk.WorksheetParts.First();
// SheetData shtData = workSht.Worksheet.Elements<SheetData>().First();
// Sheet theSheet = workBk.Workbook.Descendants<Sheet>().
// Where(s => s.SheetId == 1).FirstOrDefault();
// var strData = "<ENVELOPE><HEADER><TALLYREQUEST>Import Data</TALLYREQUEST></HEADER><BODY><IMPORTDATA>" +
// "<REQUESTDESC> <REPORTNAME>All Masters</REPORTNAME><STATICVARIABLES><SVCURRENTCOMPANY>" + model.Company +
// "</SVCURRENTCOMPANY></STATICVARIABLES></REQUESTDESC><REQUESTDATA>";
// int rows = 0;
// string cellA; string cellD;
// string cellF; string cellH; string cellJ; string cellQ;
// string cellU; string cellW; string cellY; string cellAB; string cellAD; string cellAssembly;
// var year = ""; var week = ""; var assembly = ""; var unitname = "";
// var consutantCode = ""; var consultantName = "";
// decimal checkedAmt; var item = ""; var itemCode = ""; var itemName = ""; int orderId;
// decimal MRP; decimal ordQty; decimal consPrice; decimal amount; var status = "";
// List<UnitName> units = new List<UnitName>();
// List<UnitConsultant> unitConsultants = new List<UnitConsultant>();
// List<ConsultantOrder> consOrders = new List<ConsultantOrder>();
// List<StockItem> stockItems = new List<StockItem>();
// cellAssembly = "L8";
// assembly = XcelServiceHelper.GetCellValue(workBk, theSheet, cellAssembly);
// week = XcelServiceHelper.GetCellValue(workBk, theSheet, "U9");
// year = XcelServiceHelper.GetCellValue(workBk, theSheet, "U8");
// UnitName u;
// ConsultantOrder co;
// UnitConsultant uc;
// int j = 0;
// int rowCount = shtData.Elements<Row>().Count();
// rowCount = rowCount + 4;
// //foreach (Row r in shtData.Elements<Row>())
// for (rows = 1; rows <= rowCount; rows++)
// {
// //rows += 1;
// cellQ = "Q" + rows;
// if (rows >= 12)
// {
// //Cell theCell = r.Descendants<Cell>().Where(x => x.CellReference == cellQ).FirstOrDefault();
// string theCell = XcelServiceHelper.GetCellValue(workBk, theSheet, cellQ);
// if (!string.IsNullOrEmpty(theCell))
// {
// //
// string ratePercVal = "";
// cellQ = "Q" + rows;
// cellJ = "J" + rows;
// itemCode = XcelServiceHelper.GetCellValue(workBk, theSheet, cellJ);
// itemName = XcelServiceHelper.GetCellValue(workBk, theSheet, cellQ);
// itemName = itemName.Replace("(", " ");
// itemName = itemName.Replace(")", " ");
// itemName = itemName.Replace("&", "_");
// itemName = itemName.Replace("'", " ");
// item = itemName + " - " + itemCode;
// StockItem s = stockItems.FirstOrDefault(x => x.ItemCode == itemCode.Trim());
// if (s == null)
// {
// if (itemName.StartsWith("PPP")) ratePercVal = " 5";
// else ratePercVal = " 12.5";
// strData = strData + @"<TALLYMESSAGE xmlns:UDF=""TallyUDF"">" +
// @"<STOCKITEM NAME="""
// + item +
// @""" RESERVEDNAME="""">" +
// @"<OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS></OLDAUDITENTRYIDS.LIST>" +
// "<PARENT>Tupperware Products</PARENT><CATEGORY/><TAXCLASSIFICATIONNAME/><COSTINGMETHOD>Avg. Cost</COSTINGMETHOD>" +
// "<VALUATIONMETHOD>Avg. Price</VALUATIONMETHOD><BASEUNITS>NO</BASEUNITS><ADDITIONALUNITS/><EXCISEITEMCLASSIFICATION/>" +
// "<ISCOSTCENTRESON>No</ISCOSTCENTRESON><ISBATCHWISEON>No</ISBATCHWISEON><ISPERISHABLEON>No</ISPERISHABLEON><ISENTRYTAXAPPLICABLE>No</ISENTRYTAXAPPLICABLE>" +
// "<ISCOSTTRACKINGON>No</ISCOSTTRACKINGON><IGNOREPHYSICALDIFFERENCE>No</IGNOREPHYSICALDIFFERENCE><IGNORENEGATIVESTOCK>No</IGNORENEGATIVESTOCK>" +
// "<TREATSALESASMANUFACTURED>No</TREATSALESASMANUFACTURED><TREATPURCHASESASCONSUMED>No</TREATPURCHASESASCONSUMED><TREATREJECTSASSCRAP>No</TREATREJECTSASSCRAP>" +
// "<HASMFGDATE>No</HASMFGDATE><ALLOWUSEOFEXPIREDITEMS>No</ALLOWUSEOFEXPIREDITEMS><IGNOREBATCHES>No</IGNOREBATCHES><IGNOREGODOWNS>No</IGNOREGODOWNS>" +
// "<CALCONMRP>No</CALCONMRP><EXCLUDEJRNLFORVALUATION>No</EXCLUDEJRNLFORVALUATION><ISMRPINCLOFTAX>No</ISMRPINCLOFTAX><ISADDLTAXEXEMPT>No</ISADDLTAXEXEMPT>" +
// "<ISSUPPLEMENTRYDUTYON>No</ISSUPPLEMENTRYDUTYON><REORDERASHIGHER>No</REORDERASHIGHER><MINORDERASHIGHER>No</MINORDERASHIGHER><DENOMINATOR> 1</DENOMINATOR>" +
// @"<RATEOFVAT>" + ratePercVal + "</RATEOFVAT><LANGUAGENAME.LIST>" +
// @"<NAME.LIST TYPE=""String""><NAME>" + item + "</NAME></NAME.LIST><LANGUAGEID> 1033</LANGUAGEID>" +
// "</LANGUAGENAME.LIST><SCHVIDETAILS.LIST> </SCHVIDETAILS.LIST><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST><ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST>" +
// "<AUDITENTRIES.LIST> </AUDITENTRIES.LIST><COMPONENTLIST.LIST> </COMPONENTLIST.LIST><ADDITIONALLEDGERS.LIST> </ADDITIONALLEDGERS.LIST>" +
// "<SALESLIST.LIST> </SALESLIST.LIST><PURCHASELIST.LIST> </PURCHASELIST.LIST><FULLPRICELIST.LIST> </FULLPRICELIST.LIST>" +
// "<BATCHALLOCATIONS.LIST> </BATCHALLOCATIONS.LIST><TRADEREXCISEDUTIES.LIST> </TRADEREXCISEDUTIES.LIST><STANDARDCOSTLIST.LIST> </STANDARDCOSTLIST.LIST>" +
// "<STANDARDPRICELIST.LIST> </STANDARDPRICELIST.LIST><EXCISEITEMGODOWN.LIST> </EXCISEITEMGODOWN.LIST><MULTICOMPONENTLIST.LIST> </MULTICOMPONENTLIST.LIST>" +
// "<PRICELEVELLIST.LIST> </PRICELEVELLIST.LIST></STOCKITEM></TALLYMESSAGE>";
// s = new StockItem
// {
// ItemCode = itemCode.Trim().ToString(),
// ItemName = itemName.Trim().ToString()
// };
// stockItems.Add(s);
// }
// cellA = "A" + rows;
// unitname = XcelServiceHelper.GetCellValue(workBk, theSheet, cellA);
// u = units.FirstOrDefault(x => x.Unit == unitname.Trim());
// if (u == null)
// {
// u = new UnitName
// {
// Unit = unitname,
// IsGroupCreated = false
// };
// units.Add(u);
// units.OrderBy(x => x.Unit);
// }
// cellD = "D" + rows;
// consutantCode = XcelServiceHelper.GetCellValue(workBk, theSheet, cellD);
// cellF = "F" + rows;
// consultantName = XcelServiceHelper.GetCellValue(workBk, theSheet, cellF);
// uc = u.UnitConsultants.FirstOrDefault(x => x.Consultant == consultantName.Trim());
// if (uc == null)
// {
// uc = new UnitConsultant
// {
// Consultant = consultantName.Trim(),
// ConsultantId = consutantCode.Trim(),
// UnitName = u
// };
// if (u != null) u.UnitConsultants.Add(uc);
// }
// cellH = "H" + rows;
// orderId = int.Parse(XcelServiceHelper.GetCellValue(workBk, theSheet, cellH));
// cellU = "U" + rows;
// ordQty = Decimal.Parse(XcelServiceHelper.GetCellValue(workBk, theSheet, cellU));
// cellW = "W" + rows;
// MRP = Decimal.Parse(XcelServiceHelper.GetCellValue(workBk, theSheet, cellW));
// cellY = "Y" + rows;
// consPrice = Math.Round(Decimal.Parse(XcelServiceHelper.GetCellValue(workBk, theSheet, cellY)));
// cellAB = "AB" + rows;
// amount = Math.Round(Decimal.Parse(XcelServiceHelper.GetCellValue(workBk, theSheet, cellAB)));
// cellAD = "AD" + rows;
// status = XcelServiceHelper.GetCellValue(workBk, theSheet, cellAD);
// co = uc.ConsultantOrders.FirstOrDefault(x => x.ItemCode == itemCode.Trim());
// if (co == null)
// {
// co = new ConsultantOrder
// {
// OrderId = orderId,
// Amount = amount,
// ItemCode = itemCode.Trim(),
// ItemName = itemName.Trim(),
// MRP = MRP,
// OrdQty = ordQty,
// ConsultantPrice = consPrice,
// Status = status,
// Consultant = uc
// };
// co.VoucherId = j + 1;
// j++;
// if (uc != null)
// {
// //prevId = co.OrderId;
// //prevVoucherId = co.VoucherId;
// uc.ConsultantOrders.Add(co);
// uc.ConsultantOrders.OrderBy(x => x.OrderId).ThenBy(x => x.ItemName);
// }
// }
// }
// }
// }
// foreach (var unit in units)
// {
// unitOfWork = new UnitOfWork();
// foreach (var uCon in unit.UnitConsultants)
// {
// string consultant = uCon.Consultant + " - " + uCon.ConsultantId;
// //newVoucherNo = newVoucherNo + 1;
// strData = strData + @"<TALLYMESSAGE xmlns:UDF=""TallyUDF"">" +
// @"<LEDGER NAME="""
// + consultant +
// @""" RESERVEDNAME="""">" +
// @"<ADDRESS.LIST TYPE=""String""><ADDRESS>" + unit.Unit + "</ADDRESS><ADDRESS>" + assembly + "</ADDRESS></ADDRESS.LIST>" +
// @"<MAILINGNAME.LIST TYPE=""String""><MAILINGNAME>" + consultant + "</MAILINGNAME></MAILINGNAME.LIST>" +
// @"<OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS></OLDAUDITENTRYIDS.LIST>" +
// "<ALTEREDON>" + model.Date + "</ALTEREDON><STATENAME>Maharashtra</STATENAME><PARENT>" + unit.Unit + "</PARENT><TAXCLASSIFICATIONNAME/>" +
// "<TAXTYPE>Others</TAXTYPE><BUSINESSTYPE/><BASICTYPEOFDUTY>Excise Surcharge</BASICTYPEOFDUTY><GSTTYPE/><APPROPRIATEFOR/>" +
// "<SERVICECATEGORY/><EXCISELEDGERCLASSIFICATION/><EXCISEDUTYTYPE/><EXCISENATUREOFPURCHASE/><LEDGERFBTCATEGORY/>" +
// "<ISBILLWISEON>No</ISBILLWISEON><ISCOSTCENTRESON>Yes</ISCOSTCENTRESON><ISINTERESTON>No</ISINTERESTON><ALLOWINMOBILE>No</ALLOWINMOBILE>" +
// "<ISCOSTTRACKINGON>No</ISCOSTTRACKINGON><ISCONDENSED>No</ISCONDENSED><AFFECTSSTOCK>No</AFFECTSSTOCK><FORPAYROLL>No</FORPAYROLL>" +
// "<ISABCENABLED>No</ISABCENABLED><INTERESTONBILLWISE>No</INTERESTONBILLWISE><OVERRIDEINTEREST>No</OVERRIDEINTEREST><OVERRIDEADVINTEREST>No</OVERRIDEADVINTEREST>" +
// "<USEFORVAT>No</USEFORVAT><IGNORETDSEXEMPT>No</IGNORETDSEXEMPT><ISTCSAPPLICABLE>No</ISTCSAPPLICABLE><ISTDSAPPLICABLE>No</ISTDSAPPLICABLE><ISFBTAPPLICABLE>No</ISFBTAPPLICABLE>" +
// "<ISGSTAPPLICABLE>No</ISGSTAPPLICABLE><ISEXCISEAPPLICABLE>No</ISEXCISEAPPLICABLE><ISTDSEXPENSE>No</ISTDSEXPENSE><ISEDLIAPPLICABLE>No</ISEDLIAPPLICABLE>" +
// "<ISRELATEDPARTY>No</ISRELATEDPARTY><USEFORESIELIGIBILITY>No</USEFORESIELIGIBILITY><SHOWINPAYSLIP>No</SHOWINPAYSLIP><USEFORGRATUITY>No</USEFORGRATUITY>" +
// "<ISTDSPROJECTED>No</ISTDSPROJECTED><FORSERVICETAX>No</FORSERVICETAX><ISINPUTCREDIT>No</ISINPUTCREDIT><ISEXEMPTED>No</ISEXEMPTED><ISABATEMENTAPPLICABLE>No</ISABATEMENTAPPLICABLE>" +
// "<ISSTXPARTY>No</ISSTXPARTY><ISSTXNONREALIZEDTYPE>No</ISSTXNONREALIZEDTYPE><TDSDEDUCTEEISSPECIALRATE>No</TDSDEDUCTEEISSPECIALRATE><AUDITED>No</AUDITED><SORTPOSITION> 1000</SORTPOSITION>" +
// @"<RATEOFTAXCALCULATION> 12.50</RATEOFTAXCALCULATION><LANGUAGENAME.LIST><NAME.LIST TYPE=""String""><NAME>" + consultant + "</NAME></NAME.LIST><LANGUAGEID> 1033</LANGUAGEID>" +
// "</LANGUAGENAME.LIST><XBRLDETAIL.LIST> </XBRLDETAIL.LIST><AUDITDETAILS.LIST> </AUDITDETAILS.LIST><SCHVIDETAILS.LIST> </SCHVIDETAILS.LIST><SLABPERIOD.LIST> </SLABPERIOD.LIST>" +
// "<GRATUITYPERIOD.LIST> </GRATUITYPERIOD.LIST><ADDITIONALCOMPUTATIONS.LIST> </ADDITIONALCOMPUTATIONS.LIST><BANKALLOCATIONS.LIST> </BANKALLOCATIONS.LIST><PAYMENTDETAILS.LIST> </PAYMENTDETAILS.LIST>" +
// "<BANKEXPORTFORMATS.LIST> </BANKEXPORTFORMATS.LIST><BILLALLOCATIONS.LIST> </BILLALLOCATIONS.LIST><INTERESTCOLLECTION.LIST> </INTERESTCOLLECTION.LIST><LEDGERCLOSINGVALUES.LIST> </LEDGERCLOSINGVALUES.LIST>" +
// "<LEDGERAUDITCLASS.LIST> </LEDGERAUDITCLASS.LIST><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST><TDSEXEMPTIONRULES.LIST> </TDSEXEMPTIONRULES.LIST><DEDUCTINSAMEVCHRULES.LIST> </DEDUCTINSAMEVCHRULES.LIST>" +
// "<LOWERDEDUCTION.LIST> </LOWERDEDUCTION.LIST><STXABATEMENTDETAILS.LIST> </STXABATEMENTDETAILS.LIST><LEDMULTIADDRESSLIST.LIST> </LEDMULTIADDRESSLIST.LIST><STXTAXDETAILS.LIST> </STXTAXDETAILS.LIST>" +
// "<CHEQUERANGE.LIST> </CHEQUERANGE.LIST><DEFAULTVCHCHEQUEDETAILS.LIST> </DEFAULTVCHCHEQUEDETAILS.LIST><ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST><AUDITENTRIES.LIST> </AUDITENTRIES.LIST>" +
// "<BRSIMPORTEDINFO.LIST> </BRSIMPORTEDINFO.LIST><AUTOBRSCONFIGS.LIST> </AUTOBRSCONFIGS.LIST><BANKURENTRIES.LIST> </BANKURENTRIES.LIST><DEFAULTCHEQUEDETAILS.LIST> </DEFAULTCHEQUEDETAILS.LIST>" +
// "<DEFAULTOPENINGCHEQUEDETAILS.LIST> </DEFAULTOPENINGCHEQUEDETAILS.LIST></LEDGER></TALLYMESSAGE>";
// if (unit != null && unit.IsGroupCreated == false)
// {
// strData = strData + @"<TALLYMESSAGE xmlns:UDF=""TallyUDF"">" +
// @"<GROUP NAME="""
// + unit.Unit +
// @""" ACTION = ""CREATE"">" +
// "<NAME.LIST><NAME>" + unit.Unit + "</NAME></NAME.LIST><PARENT>Sundry Debtors</PARENT><ISSUBLEDGER>No</ISSUBLEDGER><ISBILLWISEON>No</ISBILLWISEON>" +
// "<ISCOSTCENTRESON>No</ISCOSTCENTRESON></GROUP></TALLYMESSAGE>";
// unit.IsGroupCreated = true;
// }
// decimal totalConsultantAmt = uCon.ConsultantOrders.Sum(x => x.ConsultantPrice * x.OrdQty);
// decimal consultantAmount = uCon.ConsultantOrders.Where(x => !x.ItemName.StartsWith("PPP")).Sum(x => x.ConsultantPrice * x.OrdQty);
// decimal netAmount = ((consultantAmount / Decimal.Parse("112.5") * Decimal.Parse("100")));
// //decimal tax = ((netAmount * Decimal.Parse("12.5")) / Decimal.Parse("100"));
// var ordId = uCon.ConsultantOrders.Select(x => x.OrderId).First();
// var voucherId = uCon.ConsultantOrders.Where(x => x.OrderId == ordId).Select(x => x.VoucherId).First();
// netAmount = Math.Round(netAmount, 2);
// decimal tax = consultantAmount - netAmount;
// //tax = TruncateDecimal(tax, 2);
// decimal chkAmt = (consultantAmount - (netAmount + tax));
// //if (chkAmt <= 1 || chkAmt >= -1)
// //{
// // tax = tax + chkAmt;
// //}
// string VoucherNo = week + "-" + ordId + "/" + voucherId;
// strData = strData + @"<TALLYMESSAGE xmlns:UDF=""TallyUDF"">" +
// @"<VOUCHER VCHTYPE=""Sales"" ACTION=""Create"" OBJVIEW=""Invoice Voucher View"">" +
// @"<ADDRESS.LIST TYPE=""String""><ADDRESS>" + unit.Unit + "</ADDRESS><ADDRESS>" + assembly + "</ADDRESS>" +
// @"</ADDRESS.LIST><BASICBUYERADDRESS.LIST TYPE=""String""><BASICBUYERADDRESS>" + unit.Unit + "</BASICBUYERADDRESS>" +
// "<BASICBUYERADDRESS>" + assembly + "</BASICBUYERADDRESS></BASICBUYERADDRESS.LIST>" +
// @"<OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS></OLDAUDITENTRYIDS.LIST><DATE>" + model.Date + "</DATE>" +
// "<PARTYNAME>" + consultant + "</PARTYNAME><VOUCHERTYPENAME>Sales</VOUCHERTYPENAME><VOUCHERNUMBER>" + VoucherNo + "</VOUCHERNUMBER>" +
// "<PARTYLEDGERNAME>" + consultant + "</PARTYLEDGERNAME><BASICBASEPARTYNAME>" + consultant + "</BASICBASEPARTYNAME>" +
// "<CSTFORMISSUETYPE/><CSTFORMRECVTYPE/><FBTPAYMENTTYPE>Default</FBTPAYMENTTYPE><PERSISTEDVIEW>Invoice Voucher View</PERSISTEDVIEW>" +
// "<BASICBUYERNAME>" + consultant + "</BASICBUYERNAME><BASICFINALDESTINATION>" + assembly + "</BASICFINALDESTINATION>" +
// "<VCHGSTCLASS/><DIFFACTUALQTY>No</DIFFACTUALQTY><AUDITED>No</AUDITED><FORJOBCOSTING>No</FORJOBCOSTING><ISOPTIONAL>No</ISOPTIONAL>" +
// "<EFFECTIVEDATE>" + model.Date + "</EFFECTIVEDATE><ISFORJOBWORKIN>No</ISFORJOBWORKIN><ALLOWCONSUMPTION>No</ALLOWCONSUMPTION>" +
// "<USEFORINTEREST>No</USEFORINTEREST><USEFORGAINLOSS>No</USEFORGAINLOSS><USEFORGODOWNTRANSFER>No</USEFORGODOWNTRANSFER>" +
// "<USEFORCOMPOUND>No</USEFORCOMPOUND><EXCISEOPENING>No</EXCISEOPENING><USEFORFINALPRODUCTION>No</USEFORFINALPRODUCTION>" +
// "<ISCANCELLED>No</ISCANCELLED><HASCASHFLOW>No</HASCASHFLOW><ISPOSTDATED>No</ISPOSTDATED><USETRACKINGNUMBER>No</USETRACKINGNUMBER>" +
// "<ISINVOICE>Yes</ISINVOICE><MFGJOURNAL>No</MFGJOURNAL><HASDISCOUNTS>No</HASDISCOUNTS><ASPAYSLIP>No</ASPAYSLIP><ISCOSTCENTRE>No</ISCOSTCENTRE>" +
// "<ISSTXNONREALIZEDVCH>No</ISSTXNONREALIZEDVCH><ISEXCISEMANUFACTURERON>Yes</ISEXCISEMANUFACTURERON><ISBLANKCHEQUE>No</ISBLANKCHEQUE>" +
// "<ISDELETED>No</ISDELETED><ASORIGINAL>No</ASORIGINAL><VCHISFROMSYNC>No</VCHISFROMSYNC><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST>" +
// "<ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST><AUDITENTRIES.LIST> </AUDITENTRIES.LIST><INVOICEDELNOTES.LIST> </INVOICEDELNOTES.LIST>" +
// "<INVOICEORDERLIST.LIST> </INVOICEORDERLIST.LIST><INVOICEINDENTLIST.LIST> </INVOICEINDENTLIST.LIST><ATTENDANCEENTRIES.LIST> </ATTENDANCEENTRIES.LIST>" +
// "<ORIGINVOICEDETAILS.LIST> </ORIGINVOICEDETAILS.LIST><INVOICEEXPORTLIST.LIST> </INVOICEEXPORTLIST.LIST><LEDGERENTRIES.LIST>" +
// @"<OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS></OLDAUDITENTRYIDS.LIST><LEDGERNAME>" + consultant + "</LEDGERNAME>" +
// "<GSTCLASS/><ISDEEMEDPOSITIVE>Yes</ISDEEMEDPOSITIVE><LEDGERFROMITEM>No</LEDGERFROMITEM><REMOVEZEROENTRIES>No</REMOVEZEROENTRIES><ISPARTYLEDGER>Yes</ISPARTYLEDGER>" +
// "<ISLASTDEEMEDPOSITIVE>Yes</ISLASTDEEMEDPOSITIVE><AMOUNT>-" + totalConsultantAmt + "</AMOUNT><CATEGORYALLOCATIONS.LIST><CATEGORY>Primary Cost Category</CATEGORY>" +
// "<ISDEEMEDPOSITIVE>Yes</ISDEEMEDPOSITIVE><COSTCENTREALLOCATIONS.LIST><NAME>" + assembly + "</NAME><AMOUNT>-" + totalConsultantAmt + "</AMOUNT>" +
// "</COSTCENTREALLOCATIONS.LIST></CATEGORYALLOCATIONS.LIST><BANKALLOCATIONS.LIST> </BANKALLOCATIONS.LIST><BILLALLOCATIONS.LIST> </BILLALLOCATIONS.LIST>" +
// "<INTERESTCOLLECTION.LIST> </INTERESTCOLLECTION.LIST><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST><ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST>" +
// "<AUDITENTRIES.LIST> </AUDITENTRIES.LIST><TAXBILLALLOCATIONS.LIST> </TAXBILLALLOCATIONS.LIST><TAXOBJECTALLOCATIONS.LIST> </TAXOBJECTALLOCATIONS.LIST>" +
// "<TDSEXPENSEALLOCATIONS.LIST> </TDSEXPENSEALLOCATIONS.LIST><VATSTATUTORYDETAILS.LIST> </VATSTATUTORYDETAILS.LIST><COSTTRACKALLOCATIONS.LIST> </COSTTRACKALLOCATIONS.LIST>" +
// @"</LEDGERENTRIES.LIST><LEDGERENTRIES.LIST><OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS></OLDAUDITENTRYIDS.LIST>" +
// @"<BASICRATEOFINVOICETAX.LIST TYPE=""Number""><BASICRATEOFINVOICETAX> 12.50</BASICRATEOFINVOICETAX></BASICRATEOFINVOICETAX.LIST><TAXCLASSIFICATIONNAME>Output VAT @ 12.5%</TAXCLASSIFICATIONNAME>" +
// "<ROUNDTYPE>Normal Rounding</ROUNDTYPE><LEDGERNAME>12.5% Vat on Sales</LEDGERNAME><GSTCLASS/><ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE><LEDGERFROMITEM>No</LEDGERFROMITEM>" +
// "<REMOVEZEROENTRIES>No</REMOVEZEROENTRIES><ISPARTYLEDGER>No</ISPARTYLEDGER><ISLASTDEEMEDPOSITIVE>No</ISLASTDEEMEDPOSITIVE><AMOUNT>" + tax + "</AMOUNT>" +
// "<VATASSESSABLEVALUE>" + netAmount + "</VATASSESSABLEVALUE><BANKALLOCATIONS.LIST> </BANKALLOCATIONS.LIST><BILLALLOCATIONS.LIST> </BILLALLOCATIONS.LIST>" +
// "<INTERESTCOLLECTION.LIST> </INTERESTCOLLECTION.LIST><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST><ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST>" +
// "<AUDITENTRIES.LIST> </AUDITENTRIES.LIST><TAXBILLALLOCATIONS.LIST> </TAXBILLALLOCATIONS.LIST><TAXOBJECTALLOCATIONS.LIST><CATEGORY>Output VAT @ 12.5%</CATEGORY>" +
// "<TAXTYPE>VAT</TAXTYPE><TAXNAME>" + VoucherNo + "</TAXNAME><PARTYLEDGER>" + consultant + "</PARTYLEDGER><REFTYPE>New Ref</REFTYPE><ISOPTIONAL>No</ISOPTIONAL>" +
// "<ISPANVALID>No</ISPANVALID><ZERORATED>No</ZERORATED><EXEMPTED>No</EXEMPTED><ISSPECIALRATE>No</ISSPECIALRATE><ISDEDUCTNOW>No</ISDEDUCTNOW><ISPANNOTAVAILABLE>No</ISPANNOTAVAILABLE>" +
// "<ISSUPPLEMENTARY>No</ISSUPPLEMENTARY><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST><ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST><AUDITENTRIES.LIST> </AUDITENTRIES.LIST>";
// decimal trackNetAmtTotal = 0;
// int count = uCon.ConsultantOrders.Where(x => !x.ItemName.StartsWith("PPP")).Count();
// int q = 0;
// foreach (var con in uCon.ConsultantOrders)
// {
// if (!con.ItemName.StartsWith("PPP"))
// {
// decimal itemTotalVal = con.ConsultantPrice * con.OrdQty;
// decimal itemRate = itemTotalVal / con.OrdQty;
// decimal itemNetAmt = (con.ConsultantPrice / Decimal.Parse("112.5") * Decimal.Parse("100"));
// itemNetAmt = Math.Round(itemNetAmt, 2);
// //decimal itemTax = (itemNetAmt * Decimal.Parse("12.5") / Decimal.Parse("100"));
// decimal itemTax = con.ConsultantPrice - itemNetAmt;
// itemRate = itemRate - itemTax;
// decimal itemsTotalAmt = itemRate * con.OrdQty;
// decimal itemTotalTax = itemTax * con.OrdQty;
// decimal itemTotalNetAmt = itemNetAmt * con.OrdQty;
// //----------------------------------------------------
// //INCLUDED TO TRACK NET AMT TOTALS AFFECTING ROUNDING
// //-----------------------------------------------------
// trackNetAmtTotal += itemTotalNetAmt;
// if (q == count - 1) // last item in consultant orders
// {
// if (netAmount > trackNetAmtTotal || netAmount < trackNetAmtTotal)
// {
// checkedAmt = netAmount - trackNetAmtTotal;
// itemsTotalAmt = itemsTotalAmt + checkedAmt;
// itemTotalTax = itemTotalTax - checkedAmt;
// }
// }
// strData = strData + "<SUBCATEGORYALLOCATION.LIST>" +
// "<STOCKITEMNAME>" + con.ItemName + "-" + con.ItemCode + "</STOCKITEMNAME>" +
// "<SUBCATEGORY>VAT</SUBCATEGORY>" +
// "<DUTYLEDGER>12.5% Vat on Sales</DUTYLEDGER>" +
// "<SUBCATZERORATED>No</SUBCATZERORATED>" +
// "<SUBCATEXEMPTED>No</SUBCATEXEMPTED>" +
// "<SUBCATISSPECIALRATE>No</SUBCATISSPECIALRATE>" +
// "<TAXRATE> 12.50</TAXRATE>" +
// "<ASSESSABLEAMOUNT>" + itemsTotalAmt + "</ASSESSABLEAMOUNT>" +
// "<TAX>" + itemTotalTax + "</TAX>" +
// "<BILLEDQTY> " + con.OrdQty + " NO</BILLEDQTY>" +
// "</SUBCATEGORYALLOCATION.LIST>";
// q++;
// }
// }
// strData = strData + "</TAXOBJECTALLOCATIONS.LIST><TDSEXPENSEALLOCATIONS.LIST> </TDSEXPENSEALLOCATIONS.LIST>" +
// "<VATSTATUTORYDETAILS.LIST> </VATSTATUTORYDETAILS.LIST><COSTTRACKALLOCATIONS.LIST> </COSTTRACKALLOCATIONS.LIST>" +
// "</LEDGERENTRIES.LIST>";
// q = 0;
// trackNetAmtTotal = 0;
// foreach (var con in uCon.ConsultantOrders)
// {
// if (!con.ItemName.StartsWith("PPP"))
// {
// decimal itemTotalVal = con.ConsultantPrice * con.OrdQty;
// decimal itemRate = itemTotalVal / con.OrdQty;
// decimal itemNetAmt = (con.ConsultantPrice / Decimal.Parse("112.5") * Decimal.Parse("100"));
// itemNetAmt = Math.Round(itemNetAmt, 2);
// //decimal itemTax = (itemNetAmt * Decimal.Parse("12.5") / Decimal.Parse("100"));
// decimal itemTax = con.ConsultantPrice - itemNetAmt;
// itemRate = itemRate - itemTax;
// decimal itemsTotalAmt = itemRate * con.OrdQty;
// decimal itemTotalTax = itemTax * con.OrdQty;
// decimal itemTotalNetAmt = itemNetAmt * con.OrdQty;
// //----------------------------------------------------
// //INCLUDED TO TRACK NET AMT TOTALS AFFECTING ROUNDING
// //-----------------------------------------------------
// trackNetAmtTotal += itemTotalNetAmt;
// if (q == count - 1) // last item in consultant orders
// {
// if (netAmount > trackNetAmtTotal || netAmount < trackNetAmtTotal)
// {
// checkedAmt = netAmount - trackNetAmtTotal;
// itemsTotalAmt = itemsTotalAmt + checkedAmt;
// itemTotalTax = itemTotalTax - checkedAmt;
// itemRate = itemsTotalAmt / con.OrdQty;
// }
// }
// //-----------------------------------------------------------------------
// strData = strData + "<ALLINVENTORYENTRIES.LIST><STOCKITEMNAME>" + con.ItemName + "-" + con.ItemCode + "</STOCKITEMNAME>" +
// "<ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE><ISLASTDEEMEDPOSITIVE>No</ISLASTDEEMEDPOSITIVE><ISAUTONEGATE>No</ISAUTONEGATE>" +
// "<ISCUSTOMSCLEARANCE>No</ISCUSTOMSCLEARANCE><ISTRACKCOMPONENT>No</ISTRACKCOMPONENT><ISTRACKPRODUCTION>No</ISTRACKPRODUCTION>" +
// "<ISPRIMARYITEM>No</ISPRIMARYITEM><ISSCRAP>No</ISSCRAP><RATE>" + itemRate + "/NO</RATE><AMOUNT>" + itemsTotalAmt + "</AMOUNT>" + //<AMOUNT>" + itemNetAmt + "</AMOUNT>
// "<ACTUALQTY> " + con.OrdQty + " NO</ACTUALQTY><BILLEDQTY> " + con.OrdQty + " NO</BILLEDQTY><BATCHALLOCATIONS.LIST>" +
// "<GODOWNNAME>Main Location</GODOWNNAME><BATCHNAME>Primary Batch</BATCHNAME><INDENTNO/><ORDERNO/><TRACKINGNUMBER/>" +
// "<DYNAMICCSTISCLEARED>No</DYNAMICCSTISCLEARED><AMOUNT>" + itemsTotalAmt + "</AMOUNT><ACTUALQTY> " + con.OrdQty + " NO</ACTUALQTY>" +
// "<BILLEDQTY> " + con.OrdQty + " NO</BILLEDQTY><ADDITIONALDETAILS.LIST> </ADDITIONALDETAILS.LIST><VOUCHERCOMPONENTLIST.LIST> </VOUCHERCOMPONENTLIST.LIST>" +
// @"</BATCHALLOCATIONS.LIST><ACCOUNTINGALLOCATIONS.LIST><OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS>" +
// "</OLDAUDITENTRYIDS.LIST><TAXCLASSIFICATIONNAME>Output VAT @ 12.5%</TAXCLASSIFICATIONNAME><LEDGERNAME>Sales @12.5%</LEDGERNAME><GSTCLASS/>" +
// "<ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE><LEDGERFROMITEM>No</LEDGERFROMITEM><REMOVEZEROENTRIES>No</REMOVEZEROENTRIES><ISPARTYLEDGER>No</ISPARTYLEDGER>" +
// "<ISLASTDEEMEDPOSITIVE>No</ISLASTDEEMEDPOSITIVE><AMOUNT>" + itemsTotalAmt + "</AMOUNT><BANKALLOCATIONS.LIST> </BANKALLOCATIONS.LIST>" +
// "<BILLALLOCATIONS.LIST> </BILLALLOCATIONS.LIST><INTERESTCOLLECTION.LIST> </INTERESTCOLLECTION.LIST><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST>" +
// "<ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST><AUDITENTRIES.LIST> </AUDITENTRIES.LIST><TAXBILLALLOCATIONS.LIST> </TAXBILLALLOCATIONS.LIST>" +
// "<TAXOBJECTALLOCATIONS.LIST> </TAXOBJECTALLOCATIONS.LIST><TDSEXPENSEALLOCATIONS.LIST> </TDSEXPENSEALLOCATIONS.LIST><VATSTATUTORYDETAILS.LIST> </VATSTATUTORYDETAILS.LIST>" +
// "<COSTTRACKALLOCATIONS.LIST> </COSTTRACKALLOCATIONS.LIST></ACCOUNTINGALLOCATIONS.LIST><TAXOBJECTALLOCATIONS.LIST> </TAXOBJECTALLOCATIONS.LIST>" +
// "<EXCISEALLOCATIONS.LIST> </EXCISEALLOCATIONS.LIST><EXPENSEALLOCATIONS.LIST> </EXPENSEALLOCATIONS.LIST></ALLINVENTORYENTRIES.LIST>";
// q++;
// itemNetAmt = XcelServiceHelper.TruncateDecimal(itemNetAmt, 2);
// decimal chkItemTax = con.ConsultantPrice - (itemNetAmt + itemTax);
// if (chkItemTax <= 1 || chkItemTax >= -1)
// {
// itemTax = itemTax + chkItemTax;
// }
// PostedTransaction trans;
// //using (var db = new eXmlContext())
// //{
// trans = unitOfWork.PostedTransactions.All()
// .Where(x => x.ItemCode == con.ItemCode)
// .Where(x => x.OrderId == con.OrderId)
// .Where(x => x.ConsultantCode == con.Consultant.ConsultantId)
// .FirstOrDefault();
// if (trans == null)
// {
// trans = new PostedTransaction
// {
// Company = model.Company,
// UnitName = unit.Unit,
// AssemblyName = assembly,
// ConsultantName = uCon.Consultant,
// ConsultantCode = uCon.ConsultantId,
// PostDate = DateTime.Now,
// Year = year,
// Week = week,
// InvoiceNo = VoucherNo,
// GrossAmount = con.Amount,
// ConsultantPrice = con.ConsultantPrice,
// NetAmount = itemNetAmt,
// VatAmount = itemTax,
// ItemCode = con.ItemCode,
// ItemName = con.ItemName,
// OrderId = con.OrderId,
// OrderQty = con.OrdQty,
// Status = con.Status,
// PayStatus = enPaymentStatus.Pending,
// InventoryStatus = enInventoryStatus.Pending,
// PaymentAmount = 0,
// PostType = enPostType.Invoice_12_5_WithAddress
// };
// unitOfWork.PostedTransactions.Insert(trans);
// }
// else
// {
// trans.ItemCode = con.ItemCode;
// trans.GrossAmount = con.Amount;
// trans.ConsultantPrice = con.ConsultantPrice;
// trans.NetAmount = itemNetAmt;
// trans.VatAmount = itemTax + (con.ConsultantPrice - (itemNetAmt + itemTax));
// trans.ItemCode = con.ItemCode;
// trans.ItemName = con.ItemName;
// trans.OrderId = con.OrderId;
// trans.OrderQty = con.OrdQty;
// trans.Status = con.Status;
// unitOfWork.PostedTransactions.Update(trans);
// }
// //}
// }
// }
// #region VAT_5%
// if (uCon.ConsultantOrders.Count(x => x.ItemName.StartsWith("PPP")) > 0) // 5 % VAT Items
// {
// consultantAmount = uCon.ConsultantOrders.Where(x => x.ItemName.StartsWith("PPP")).Sum(x => x.ConsultantPrice * x.OrdQty);
// netAmount = ((consultantAmount / Decimal.Parse("105") * Decimal.Parse("100")));
// //tax = ((netAmount * Decimal.Parse("5")) / Decimal.Parse("100"));
// netAmount = Math.Round(netAmount, 2);
// tax = consultantAmount - netAmount;
// //tax = TruncateDecimal(tax, 2);
// chkAmt = (consultantAmount - (netAmount + tax));
// //if (chkAmt <= 1 || chkAmt >= -1)
// //{
// // tax = tax + chkAmt;
// //}
// strData = strData + @"<LEDGERENTRIES.LIST><OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS></OLDAUDITENTRYIDS.LIST>" +
// @"<BASICRATEOFINVOICETAX.LIST TYPE=""Number""><BASICRATEOFINVOICETAX> 5</BASICRATEOFINVOICETAX></BASICRATEOFINVOICETAX.LIST><TAXCLASSIFICATIONNAME>Output VAT @ 5%</TAXCLASSIFICATIONNAME>" +
// "<ROUNDTYPE>Normal Rounding</ROUNDTYPE><LEDGERNAME>Output Vat @5%</LEDGERNAME><GSTCLASS/><ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE><LEDGERFROMITEM>No</LEDGERFROMITEM>" +
// "<REMOVEZEROENTRIES>No</REMOVEZEROENTRIES><ISPARTYLEDGER>No</ISPARTYLEDGER><ISLASTDEEMEDPOSITIVE>No</ISLASTDEEMEDPOSITIVE><AMOUNT>" + tax + "</AMOUNT>" +
// "<VATASSESSABLEVALUE>" + netAmount + "</VATASSESSABLEVALUE><BANKALLOCATIONS.LIST> </BANKALLOCATIONS.LIST><BILLALLOCATIONS.LIST> </BILLALLOCATIONS.LIST>" +
// "<INTERESTCOLLECTION.LIST> </INTERESTCOLLECTION.LIST><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST><ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST>" +
// "<AUDITENTRIES.LIST> </AUDITENTRIES.LIST><TAXBILLALLOCATIONS.LIST> </TAXBILLALLOCATIONS.LIST><TAXOBJECTALLOCATIONS.LIST><CATEGORY>Output VAT @ 5%</CATEGORY>" +
// "<TAXTYPE>VAT</TAXTYPE><TAXNAME>" + VoucherNo + "</TAXNAME><PARTYLEDGER>" + consultant + "</PARTYLEDGER><REFTYPE>New Ref</REFTYPE><ISOPTIONAL>No</ISOPTIONAL>" +
// "<ISPANVALID>No</ISPANVALID><ZERORATED>No</ZERORATED><EXEMPTED>No</EXEMPTED><ISSPECIALRATE>No</ISSPECIALRATE><ISDEDUCTNOW>No</ISDEDUCTNOW><ISPANNOTAVAILABLE>No</ISPANNOTAVAILABLE>" +
// "<ISSUPPLEMENTARY>No</ISSUPPLEMENTARY><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST><ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST><AUDITENTRIES.LIST> </AUDITENTRIES.LIST>";
// var PercentItems = uCon.ConsultantOrders.Where(x => x.ItemName.StartsWith("PPP")).ToList();
// trackNetAmtTotal = 0;
// count = PercentItems.Count();
// q = 0;
// foreach (var con in PercentItems)
// {
// decimal itemTotalVal = con.ConsultantPrice * con.OrdQty;
// decimal itemRate = itemTotalVal / con.OrdQty;
// decimal itemNetAmt = (con.ConsultantPrice / Decimal.Parse("105") * Decimal.Parse("100"));
// itemNetAmt = Math.Round(itemNetAmt, 2);
// //decimal itemTax = (itemNetAmt * Decimal.Parse("5") / Decimal.Parse("100"));
// decimal itemTax = con.ConsultantPrice - itemNetAmt;
// itemRate = itemRate - itemTax;
// decimal itemsTotalAmt = itemRate * con.OrdQty;
// decimal itemTotalTax = itemTax * con.OrdQty;
// decimal itemTotalNetAmt = itemNetAmt * con.OrdQty;
// //----------------------------------------------------
// //INCLUDED TO TRACK NET AMT TOTALS AFFECTING ROUNDING
// //-----------------------------------------------------
// trackNetAmtTotal += itemTotalNetAmt;
// if (q == count - 1) // last item in consultant orders
// {
// if (netAmount > trackNetAmtTotal || netAmount < trackNetAmtTotal)
// {
// checkedAmt = netAmount - trackNetAmtTotal;
// itemsTotalAmt = itemsTotalAmt + checkedAmt;
// itemTotalTax = itemTotalTax - checkedAmt;
// }
// }
// strData = strData + "<SUBCATEGORYALLOCATION.LIST>" +
// "<STOCKITEMNAME>" + con.ItemName + "-" + con.ItemCode + "</STOCKITEMNAME>" +
// "<SUBCATEGORY>VAT</SUBCATEGORY>" +
// "<DUTYLEDGER>12.5% Vat on Sales</DUTYLEDGER>" +
// "<SUBCATZERORATED>No</SUBCATZERORATED>" +
// "<SUBCATEXEMPTED>No</SUBCATEXEMPTED>" +
// "<SUBCATISSPECIALRATE>No</SUBCATISSPECIALRATE>" +
// "<TAXRATE> 12.50</TAXRATE>" +
// "<ASSESSABLEAMOUNT>" + itemsTotalAmt + "</ASSESSABLEAMOUNT>" +
// "<TAX>" + itemTotalTax + "</TAX>" +
// "<BILLEDQTY> " + con.OrdQty + " NO</BILLEDQTY>" +
// "</SUBCATEGORYALLOCATION.LIST>";
// q++;
// }
// strData = strData + "</TAXOBJECTALLOCATIONS.LIST><TDSEXPENSEALLOCATIONS.LIST> </TDSEXPENSEALLOCATIONS.LIST>" +
// "<VATSTATUTORYDETAILS.LIST> </VATSTATUTORYDETAILS.LIST><COSTTRACKALLOCATIONS.LIST> </COSTTRACKALLOCATIONS.LIST>" +
// "</LEDGERENTRIES.LIST>";
// q = 0;
// trackNetAmtTotal = 0;
// foreach (var con in PercentItems)
// {
// decimal itemTotalVal = con.ConsultantPrice * con.OrdQty;
// decimal itemRate = itemTotalVal / con.OrdQty;
// decimal itemNetAmt = (con.ConsultantPrice / Decimal.Parse("105") * Decimal.Parse("100"));
// itemNetAmt = Math.Round(itemNetAmt, 2);
// //decimal itemTax = (itemNetAmt * Decimal.Parse("5") / Decimal.Parse("100"));
// decimal itemTax = con.ConsultantPrice - itemNetAmt;
// itemRate = itemRate - itemTax;
// decimal itemsTotalAmt = itemRate * con.OrdQty;
// decimal itemTotalTax = itemTax * con.OrdQty;
// decimal itemTotalNetAmt = itemNetAmt * con.OrdQty;
// //itemNetAmt = Math.Round(itemNetAmt, 0);
// //----------------------------------------------------
// //INCLUDED TO TRACK NET AMT TOTALS AFFECTING ROUNDING
// //-----------------------------------------------------
// trackNetAmtTotal += itemTotalNetAmt;
// if (q == count - 1) // last item in consultant orders
// {
// if (netAmount > trackNetAmtTotal || netAmount < trackNetAmtTotal)
// {
// checkedAmt = netAmount - trackNetAmtTotal;
// itemsTotalAmt = itemsTotalAmt + checkedAmt;
// itemTotalTax = itemTotalTax - checkedAmt;
// itemRate = itemsTotalAmt / con.OrdQty;
// }
// }
// strData = strData + "<ALLINVENTORYENTRIES.LIST><STOCKITEMNAME>" + con.ItemName + "-" + con.ItemCode + "</STOCKITEMNAME>" +
// "<ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE><ISLASTDEEMEDPOSITIVE>No</ISLASTDEEMEDPOSITIVE><ISAUTONEGATE>No</ISAUTONEGATE>" +
// "<ISCUSTOMSCLEARANCE>No</ISCUSTOMSCLEARANCE><ISTRACKCOMPONENT>No</ISTRACKCOMPONENT><ISTRACKPRODUCTION>No</ISTRACKPRODUCTION>" +
// "<ISPRIMARYITEM>No</ISPRIMARYITEM><ISSCRAP>No</ISSCRAP><RATE>" + itemRate + "/NO</RATE><AMOUNT>" + itemsTotalAmt + "</AMOUNT>" + //<AMOUNT>" + itemNetAmt + "</AMOUNT>
// "<ACTUALQTY> " + con.OrdQty + " NO</ACTUALQTY><BILLEDQTY> " + con.OrdQty + " NO</BILLEDQTY><BATCHALLOCATIONS.LIST>" +
// "<GODOWNNAME>Main Location</GODOWNNAME><BATCHNAME>Primary Batch</BATCHNAME><INDENTNO/><ORDERNO/><TRACKINGNUMBER/>" +
// "<DYNAMICCSTISCLEARED>No</DYNAMICCSTISCLEARED><AMOUNT>" + itemsTotalAmt + "</AMOUNT><ACTUALQTY> " + con.OrdQty + " NO</ACTUALQTY>" +
// "<BILLEDQTY> " + con.OrdQty + " NO</BILLEDQTY><ADDITIONALDETAILS.LIST> </ADDITIONALDETAILS.LIST><VOUCHERCOMPONENTLIST.LIST> </VOUCHERCOMPONENTLIST.LIST>" +
// @"</BATCHALLOCATIONS.LIST><ACCOUNTINGALLOCATIONS.LIST><OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS>" +
// "</OLDAUDITENTRYIDS.LIST><TAXCLASSIFICATIONNAME>Output VAT @ 5%</TAXCLASSIFICATIONNAME><LEDGERNAME>Sales @5%</LEDGERNAME><GSTCLASS/>" +
// "<ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE><LEDGERFROMITEM>No</LEDGERFROMITEM><REMOVEZEROENTRIES>No</REMOVEZEROENTRIES><ISPARTYLEDGER>No</ISPARTYLEDGER>" +
// "<ISLASTDEEMEDPOSITIVE>No</ISLASTDEEMEDPOSITIVE><AMOUNT>" + itemsTotalAmt + "</AMOUNT><BANKALLOCATIONS.LIST> </BANKALLOCATIONS.LIST>" +
// "<BILLALLOCATIONS.LIST> </BILLALLOCATIONS.LIST><INTERESTCOLLECTION.LIST> </INTERESTCOLLECTION.LIST><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST>" +
// "<ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST><AUDITENTRIES.LIST> </AUDITENTRIES.LIST><TAXBILLALLOCATIONS.LIST> </TAXBILLALLOCATIONS.LIST>" +
// "<TAXOBJECTALLOCATIONS.LIST> </TAXOBJECTALLOCATIONS.LIST><TDSEXPENSEALLOCATIONS.LIST> </TDSEXPENSEALLOCATIONS.LIST><VATSTATUTORYDETAILS.LIST> </VATSTATUTORYDETAILS.LIST>" +
// "<COSTTRACKALLOCATIONS.LIST> </COSTTRACKALLOCATIONS.LIST></ACCOUNTINGALLOCATIONS.LIST><TAXOBJECTALLOCATIONS.LIST> </TAXOBJECTALLOCATIONS.LIST>" +
// "<EXCISEALLOCATIONS.LIST> </EXCISEALLOCATIONS.LIST><EXPENSEALLOCATIONS.LIST> </EXPENSEALLOCATIONS.LIST></ALLINVENTORYENTRIES.LIST>";
// q++;
// itemNetAmt = XcelServiceHelper.TruncateDecimal(itemNetAmt, 2);
// decimal chkItemTax = con.ConsultantPrice - (itemNetAmt + itemTax);
// if (chkItemTax <= 1 || chkItemTax >= -1)
// {
// itemTax = itemTax + chkItemTax;
// }
// PostedTransaction trans;
// trans = unitOfWork.PostedTransactions.All()
// .Where(x => x.ItemCode == con.ItemCode)
// .Where(x => x.OrderId == con.OrderId)
// .Where(x => x.ConsultantCode == con.Consultant.ConsultantId)
// .FirstOrDefault();
// if (trans == null)
// {
// trans = new PostedTransaction
// {
// Company = model.Company,
// UnitName = unit.Unit,
// AssemblyName = assembly,
// ConsultantName = uCon.Consultant,
// ConsultantCode = uCon.ConsultantId,
// PostDate = DateTime.Now,
// Year = year,
// Week = week,
// InvoiceNo = VoucherNo,
// GrossAmount = con.Amount,
// ConsultantPrice = con.ConsultantPrice,
// NetAmount = itemNetAmt,
// VatAmount = itemTax,
// ItemCode = con.ItemCode,
// ItemName = con.ItemName,
// OrderId = con.OrderId,
// OrderQty = con.OrdQty,
// Status = con.Status,
// PayStatus = enPaymentStatus.Pending,
// InventoryStatus = enInventoryStatus.Pending,
// PaymentAmount = 0,
// PostType = enPostType.Invoice_12_5_WithAddress
// };
// unitOfWork.PostedTransactions.Insert(trans);
// }
// else
// {
// trans.ItemCode = con.ItemCode;
// trans.GrossAmount = con.Amount;
// trans.ConsultantPrice = con.ConsultantPrice;
// trans.NetAmount = itemNetAmt;
// trans.VatAmount = itemTax + (con.ConsultantPrice - (itemNetAmt + itemTax));
// trans.ItemCode = con.ItemCode;
// trans.ItemName = con.ItemName;
// trans.OrderId = con.OrderId;
// trans.OrderQty = con.OrdQty;
// trans.Status = con.Status;
// unitOfWork.PostedTransactions.Update(trans);
// }
// }
// }
// #endregion VAT_5%
// strData = strData + "<ATTDRECORDS.LIST> </ATTDRECORDS.LIST>" +
// "</VOUCHER></TALLYMESSAGE>";
// }
// unitOfWork.Commit();
// unitOfWork.Dispose();
// }
// strData = strData + "</REQUESTDATA></IMPORTDATA></BODY></ENVELOPE>";
// //strData = strData + @"<TALLYMESSAGE xmlns:UDF=""TallyUDF""><COMPANY><REMOTECMPINFO.LIST MERGE=""Yes""><NAME>1992944e-597f-4d18-bdde-35856a4fbddc</NAME>" +
// //"<REMOTECMPNAME>" + model.Company + " </REMOTECMPNAME><REMOTECMPSTATE>Maharashtra</REMOTECMPSTATE></REMOTECMPINFO.LIST>" +
// // "</COMPANY></TALLYMESSAGE></REQUESTDATA></IMPORTDATA></BODY></ENVELOPE>";
// XmlDocument docum = new XmlDocument();
// docum.LoadXml(strData);
// if (!Directory.Exists(savePath))
// {
// Directory.CreateDirectory(savePath);
// }
// docum.Save(savePath + "//payment.xml");
// }
//}
public void ProcessExcelSheet(UploadFileModel model, string fileName, string savePath)
{
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart workBk = doc.WorkbookPart;
WorksheetPart workSht = workBk.WorksheetParts.First();
SheetData shtData = workSht.Worksheet.Elements<SheetData>().First();
Sheet theSheet = workBk.Workbook.Descendants<Sheet>().
Where(s => s.SheetId == 1).FirstOrDefault();
//var strData = "<ENVELOPE><HEADER><TALLYREQUEST>Import Data</TALLYREQUEST></HEADER><BODY><IMPORTDATA>" +
// "<REQUESTDESC> <REPORTNAME>All Masters</REPORTNAME><STATICVARIABLES><SVCURRENTCOMPANY>" + model.Company +
// "</SVCURRENTCOMPANY></STATICVARIABLES></REQUESTDESC><REQUESTDATA>";
XmlWriter objXmlWriter = XmlWriter.Create(new BufferedStream
(new FileStream(savePath + "//payment.xml", FileMode.Create, System.Security.AccessControl.FileSystemRights.Write,
FileShare.None, 1024, FileOptions.SequentialScan)),
new XmlWriterSettings { Indent = true, CloseOutput = true, OmitXmlDeclaration = true , WriteEndDocumentOnClose = false});
using (objXmlWriter)
{
//writing xml contents here
objXmlWriter.WriteStartElement("ENVELOPE");
objXmlWriter.WriteStartElement("HEADER");
objXmlWriter.WriteElementString("TALLYREQUEST","Import Data");
objXmlWriter.WriteEndElement();
objXmlWriter.WriteStartElement("BODY");
objXmlWriter.WriteStartElement("IMPORTDATA");
objXmlWriter.WriteStartElement("REQUESTDESC");
objXmlWriter.WriteElementString("REPORTNAME", "All Masters");
objXmlWriter.WriteStartElement("STATICVARIABLES");
objXmlWriter.WriteElementString("SVCURRENTCOMPANY", model.Company);
objXmlWriter.WriteEndElement();
objXmlWriter.WriteEndElement();
objXmlWriter.WriteStartElement("REQUESTDATA");
objXmlWriter.Flush();
}
int rows = 0;
string cellA; string cellD;
string cellF; string cellH; string cellJ; string cellQ;
string cellU; string cellW; string cellY; string cellAB; string cellAD; string cellAssembly;
var year = ""; var week = ""; var assembly = ""; var unitname = "";
var consutantCode = ""; var consultantName = "";
decimal checkedAmt; var item = ""; var itemCode = ""; var itemName = ""; int orderId;
decimal MRP; decimal ordQty; decimal consPrice; decimal amount; var status = "";
List<UnitName> units = new List<UnitName>();
List<UnitConsultant> unitConsultants = new List<UnitConsultant>();
List<ConsultantOrder> consOrders = new List<ConsultantOrder>();
List<StockItem> stockItems = new List<StockItem>();
cellAssembly = "L8";
assembly = XcelServiceHelper.GetCellValue(workBk, theSheet, cellAssembly);
week = XcelServiceHelper.GetCellValue(workBk, theSheet, "U9");
year = XcelServiceHelper.GetCellValue(workBk, theSheet, "U8");
UnitName u;
ConsultantOrder co;
UnitConsultant uc;
int j = 0;
int rowCount = shtData.Elements<Row>().Count();
rowCount = rowCount + 4;
//foreach (Row r in shtData.Elements<Row>())
XmlWriter objXmlWriter2 = XmlWriter.Create(new BufferedStream
(new FileStream(savePath + "//payment.xml", FileMode.Append, System.Security.AccessControl.FileSystemRights.Write,
FileShare.None, 16384, FileOptions.SequentialScan)),
new XmlWriterSettings { Indent = true, CloseOutput = true, OmitXmlDeclaration = true,
WriteEndDocumentOnClose = false, ConformanceLevel = ConformanceLevel.Fragment });
using (objXmlWriter2)
{
for (rows = 1; rows <= rowCount; rows++)
{
//rows += 1;
cellQ = "Q" + rows;
if (rows >= 12)
{
//Cell theCell = r.Descendants<Cell>().Where(x => x.CellReference == cellQ).FirstOrDefault();
string theCell = XcelServiceHelper.GetCellValue(workBk, theSheet, cellQ);
if (!string.IsNullOrEmpty(theCell))
{
//
string ratePercVal = "";
cellQ = "Q" + rows;
cellJ = "J" + rows;
itemCode = XcelServiceHelper.GetCellValue(workBk, theSheet, cellJ);
itemName = XcelServiceHelper.GetCellValue(workBk, theSheet, cellQ);
itemName = itemName.Replace("(", " ");
itemName = itemName.Replace(")", " ");
itemName = itemName.Replace("&", "_");
itemName = itemName.Replace("'", " ");
item = itemName + " - " + itemCode;
StockItem s = stockItems.FirstOrDefault(x => x.ItemCode == itemCode.Trim());
if (s == null)
{
if (itemName.StartsWith("PPP")) ratePercVal = " 5";
else ratePercVal = " 12.5";
objXmlWriter2.WriteStartElement("TALLYMESSAGE"); objXmlWriter2.WriteAttributeString("xmlns", "UDF", null, "TallyUDF");
objXmlWriter2.WriteStartElement("STOCKITEM"); objXmlWriter2.WriteAttributeString("NAME", item);
objXmlWriter2.WriteAttributeString("RESERVEDNAME", ""); objXmlWriter2.WriteStartElement("OLDAUDITENTRYIDS.LIST");
objXmlWriter2.WriteAttributeString("TYPE", "Number"); objXmlWriter2.WriteElementString("OLDAUDITENTRYIDS", "-1");
objXmlWriter2.WriteEndElement(); objXmlWriter2.WriteElementString("PARENT", "Tupperware Products");
objXmlWriter2.WriteStartElement("CATEGORY"); objXmlWriter2.WriteEndElement(); objXmlWriter2.WriteStartElement("TAXCLASSIFICATIONNAME"); objXmlWriter2.WriteEndElement();
objXmlWriter2.WriteElementString("COSTINGMETHOD", "Avg. Cost"); objXmlWriter2.WriteElementString("VALUATIONMETHOD", "Avg. Price");
objXmlWriter2.WriteElementString("BASEUNITS", "NO"); objXmlWriter2.WriteStartElement("ADDITIONALUNITS"); objXmlWriter2.WriteEndElement();
objXmlWriter2.WriteStartElement("EXCISEITEMCLASSIFICATION"); objXmlWriter2.WriteEndElement();
objXmlWriter2.WriteElementString("ISCOSTCENTRESON", "NO"); objXmlWriter2.WriteElementString("ISBATCHWISEON", "NO");
objXmlWriter2.WriteElementString("ISPERISHABLEON", "NO"); objXmlWriter2.WriteElementString("ISENTRYTAXAPPLICABLE", "NO");
objXmlWriter2.WriteElementString("ISCOSTTRACKINGON", "NO"); objXmlWriter2.WriteElementString("IGNOREPHYSICALDIFFERENCE", "NO");
objXmlWriter2.WriteElementString("IGNORENEGATIVESTOCK", "NO"); objXmlWriter2.WriteElementString("TREATSALESASMANUFACTURED", "NO");
objXmlWriter2.WriteElementString("TREATPURCHASESASCONSUMED", "NO"); objXmlWriter2.WriteElementString("TREATREJECTSASSCRAP", "NO");
objXmlWriter2.WriteElementString("HASMFGDATE", "NO"); objXmlWriter2.WriteElementString("ALLOWUSEOFEXPIREDITEMS", "NO");
objXmlWriter2.WriteElementString("IGNOREBATCHES", "NO"); objXmlWriter2.WriteElementString("IGNOREGODOWNS", "NO");
objXmlWriter2.WriteElementString("CALCONMRP", "NO"); objXmlWriter2.WriteElementString("EXCLUDEJRNLFORVALUATION", "NO");
objXmlWriter2.WriteElementString("ISMRPINCLOFTAX", "NO"); objXmlWriter2.WriteElementString("ISADDLTAXEXEMPT", "NO");
objXmlWriter2.WriteElementString("ISSUPPLEMENTRYDUTYON", "NO"); objXmlWriter2.WriteElementString("REORDERASHIGHER", "NO");
objXmlWriter2.WriteElementString("MINORDERASHIGHER", "NO"); objXmlWriter2.WriteElementString("DENOMINATOR", "1");
objXmlWriter2.WriteElementString("RATEOFVAT", ratePercVal); objXmlWriter2.WriteStartElement("LANGUAGENAME.LIST");
objXmlWriter2.WriteStartElement("NAME.LIST"); objXmlWriter2.WriteAttributeString("TYPE", "String");
objXmlWriter2.WriteElementString("NAME", item); objXmlWriter2.WriteEndElement();
objXmlWriter2.WriteElementString("LANGUAGEID", "1033"); objXmlWriter2.WriteEndElement();
objXmlWriter2.WriteStartElement("SCHVIDETAILS.LIST"); objXmlWriter2.WriteEndElement(); objXmlWriter2.WriteStartElement("OLDAUDITENTRIES.LIST"); objXmlWriter2.WriteEndElement();
objXmlWriter2.WriteStartElement("ACCOUNTAUDITENTRIES.LIST"); objXmlWriter2.WriteEndElement(); objXmlWriter2.WriteStartElement("AUDITENTRIES.LIST"); objXmlWriter2.WriteEndElement();
objXmlWriter2.WriteStartElement("COMPONENTLIST.LIST"); objXmlWriter2.WriteEndElement(); objXmlWriter2.WriteStartElement("ADDITIONALLEDGERS.LIST"); objXmlWriter2.WriteEndElement();
objXmlWriter2.WriteStartElement("SALESLIST.LIST"); objXmlWriter2.WriteEndElement(); objXmlWriter2.WriteStartElement("PURCHASELIST.LIST"); objXmlWriter2.WriteEndElement();
objXmlWriter2.WriteStartElement("FULLPRICELIST.LIST"); objXmlWriter2.WriteEndElement(); objXmlWriter2.WriteStartElement("BATCHALLOCATIONS.LIST"); objXmlWriter2.WriteEndElement();
objXmlWriter2.WriteStartElement("TRADEREXCISEDUTIES.LIST"); objXmlWriter2.WriteEndElement(); objXmlWriter2.WriteStartElement("STANDARDCOSTLIST.LIST"); objXmlWriter2.WriteEndElement();
objXmlWriter2.WriteStartElement("STANDARDPRICELIST.LIST"); objXmlWriter2.WriteEndElement(); objXmlWriter2.WriteStartElement("EXCISEITEMGODOWN.LIST"); objXmlWriter2.WriteEndElement();
objXmlWriter2.WriteStartElement("MULTICOMPONENTLIST.LIST"); objXmlWriter2.WriteEndElement(); objXmlWriter2.WriteStartElement("PRICELEVELLIST.LIST"); objXmlWriter2.WriteEndElement();
objXmlWriter2.WriteEndElement(); objXmlWriter2.WriteEndElement();
s = new StockItem
{
ItemCode = itemCode.Trim().ToString(),
ItemName = itemName.Trim().ToString()
};
stockItems.Add(s);
}
cellA = "A" + rows;
unitname = XcelServiceHelper.GetCellValue(workBk, theSheet, cellA);
u = units.FirstOrDefault(x => x.Unit == unitname.Trim());
if (u == null)
{
u = new UnitName
{
Unit = unitname,
IsGroupCreated = false
};
units.Add(u);
units.OrderBy(x => x.Unit);
}
cellD = "D" + rows;
consutantCode = XcelServiceHelper.GetCellValue(workBk, theSheet, cellD);
cellF = "F" + rows;
consultantName = XcelServiceHelper.GetCellValue(workBk, theSheet, cellF);
uc = u.UnitConsultants.FirstOrDefault(x => x.Consultant == consultantName.Trim());
if (uc == null)
{
uc = new UnitConsultant
{
Consultant = consultantName.Trim(),
ConsultantId = consutantCode.Trim(),
UnitName = u
};
if (u != null) u.UnitConsultants.Add(uc);
}
cellH = "H" + rows;
orderId = int.Parse(XcelServiceHelper.GetCellValue(workBk, theSheet, cellH));
cellU = "U" + rows;
ordQty = Decimal.Parse(XcelServiceHelper.GetCellValue(workBk, theSheet, cellU));
cellW = "W" + rows;
MRP = Decimal.Parse(XcelServiceHelper.GetCellValue(workBk, theSheet, cellW));
cellY = "Y" + rows;
consPrice = Math.Round(Decimal.Parse(XcelServiceHelper.GetCellValue(workBk, theSheet, cellY)));
cellAB = "AB" + rows;
amount = Math.Round(Decimal.Parse(XcelServiceHelper.GetCellValue(workBk, theSheet, cellAB)));
cellAD = "AD" + rows;
status = XcelServiceHelper.GetCellValue(workBk, theSheet, cellAD);
co = uc.ConsultantOrders.FirstOrDefault(x => x.ItemCode == itemCode.Trim());
if (co == null)
{
co = new ConsultantOrder
{
OrderId = orderId,
Amount = amount,
ItemCode = itemCode.Trim(),
ItemName = itemName.Trim(),
MRP = MRP,
OrdQty = ordQty,
ConsultantPrice = consPrice,
Status = status,
Consultant = uc
};
co.VoucherId = j + 1;
j++;
if (uc != null)
{
//prevId = co.OrderId;
//prevVoucherId = co.VoucherId;
uc.ConsultantOrders.Add(co);
uc.ConsultantOrders.OrderBy(x => x.OrderId).ThenBy(x => x.ItemName);
}
}
}
}
}
objXmlWriter2.Flush();
objXmlWriter2.Dispose();
}
foreach (var unit in units)
{
unitOfWork = new UnitOfWork();
XmlWriter objXmlWriter3 = XmlWriter.Create(new BufferedStream
(new FileStream(savePath + "//payment.xml", FileMode.Append, System.Security.AccessControl.FileSystemRights.Write,
FileShare.None, 16384, FileOptions.SequentialScan)),
new XmlWriterSettings
{
Indent = true,
CloseOutput = true,
OmitXmlDeclaration = true,
WriteEndDocumentOnClose = false,
ConformanceLevel = ConformanceLevel.Fragment
});
using (objXmlWriter3)
{
foreach (var uCon in unit.UnitConsultants)
{
string consultant = uCon.Consultant + " - " + uCon.ConsultantId;
//newVoucherNo = newVoucherNo + 1;
objXmlWriter3.WriteStartElement("TALLYMESSAGE"); objXmlWriter3.WriteAttributeString("xmlns", "UDF", null, "TallyUDF");
objXmlWriter3.WriteStartElement("LEDGER"); objXmlWriter3.WriteAttributeString("NAME", consultant); objXmlWriter3.WriteAttributeString("RESERVEDNAME", "");
objXmlWriter3.WriteStartElement("ADDRESS.LIST"); objXmlWriter3.WriteAttributeString("TYPE", "String"); objXmlWriter3.WriteElementString("ADDRESS", unit.Unit);
objXmlWriter3.WriteElementString("ADDRESS", assembly); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("MAILINGNAME.LIST");
objXmlWriter3.WriteAttributeString("TYPE", "String"); objXmlWriter3.WriteElementString("MAILINGNAME", consultant); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("OLDAUDITENTRYIDS.LIST"); objXmlWriter3.WriteAttributeString("TYPE", "Number"); objXmlWriter3.WriteElementString("OLDAUDITENTRYIDS", "-1");
objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteElementString("ALTEREDON", model.Date); objXmlWriter3.WriteElementString("STATENAME", "Maharashtra");
objXmlWriter3.WriteElementString("PARENT", unit.Unit); objXmlWriter3.WriteStartElement("TAXCLASSIFICATIONNAME"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteElementString("TAXTYPE", "Others"); objXmlWriter3.WriteStartElement("BUSINESSTYPE"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteElementString("BASICTYPEOFDUTY", "Excise Surcharge"); objXmlWriter3.WriteStartElement("GSTTYPE"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("APPROPRIATEFOR"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("SERVICECATEGORY"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("EXCISELEDGERCLASSIFICATION"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("EXCISEDUTYTYPE"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("EXCISENATUREOFPURCHASE"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("LEDGERFBTCATEGORY"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteElementString("ISBILLWISEON", "No"); objXmlWriter3.WriteElementString("ISCOSTCENTRESON", "Yes"); objXmlWriter3.WriteElementString("ISINTERESTON", "No");
objXmlWriter3.WriteElementString("ALLOWINMOBILE", "No"); objXmlWriter3.WriteElementString("ISCOSTTRACKINGON", "No"); objXmlWriter3.WriteElementString("ISCONDENSED", "No");
objXmlWriter3.WriteElementString("AFFECTSSTOCK", "No"); objXmlWriter3.WriteElementString("FORPAYROLL", "No"); objXmlWriter3.WriteElementString("ISABCENABLED", "No");
objXmlWriter3.WriteElementString("INTERESTONBILLWISE", "No"); objXmlWriter3.WriteElementString("OVERRIDEINTEREST", "No"); objXmlWriter3.WriteElementString("OVERRIDEADVINTEREST", "No");
objXmlWriter3.WriteElementString("USEFORVAT", "No"); objXmlWriter3.WriteElementString("IGNORETDSEXEMPT", "No"); objXmlWriter3.WriteElementString("ISTCSAPPLICABLE", "No");
objXmlWriter3.WriteElementString("ISTDSAPPLICABLE", "No"); objXmlWriter3.WriteElementString("ISFBTAPPLICABLE", "No"); objXmlWriter3.WriteElementString("ISGSTAPPLICABLE", "No");
objXmlWriter3.WriteElementString("ISEXCISEAPPLICABLE", "No"); objXmlWriter3.WriteElementString("ISTDSEXPENSE", "No"); objXmlWriter3.WriteElementString("ISEDLIAPPLICABLE", "No");
objXmlWriter3.WriteElementString("ISRELATEDPARTY", "No"); objXmlWriter3.WriteElementString("USEFORESIELIGIBILITY", "No"); objXmlWriter3.WriteElementString("SHOWINPAYSLIP", "No");
objXmlWriter3.WriteElementString("USEFORGRATUITY", "No"); objXmlWriter3.WriteElementString("ISTDSPROJECTED", "No"); objXmlWriter3.WriteElementString("FORSERVICETAX", "No");
objXmlWriter3.WriteElementString("ISINPUTCREDIT", "No"); objXmlWriter3.WriteElementString("ISEXEMPTED", "No"); objXmlWriter3.WriteElementString("ISABATEMENTAPPLICABLE", "No");
objXmlWriter3.WriteElementString("ISSTXPARTY", "No"); objXmlWriter3.WriteElementString("ISSTXNONREALIZEDTYPE", "No"); objXmlWriter3.WriteElementString("TDSDEDUCTEEISSPECIALRATE", "No");
objXmlWriter3.WriteElementString("AUDITED", "No"); objXmlWriter3.WriteElementString("SORTPOSITION", "1000"); objXmlWriter3.WriteElementString("RATEOFTAXCALCULATION", "12.50");
objXmlWriter3.WriteStartElement("LANGUAGENAME.LIST"); objXmlWriter3.WriteStartElement("NAME.LIST"); objXmlWriter3.WriteAttributeString("TYPE", "String");
objXmlWriter3.WriteElementString("NAME", consultant); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteElementString("LANGUAGEID", "1033"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("XBRLDETAIL.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("AUDITDETAILS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("SCHVIDETAILS.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("SLABPERIOD.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("GRATUITYPERIOD.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("ADDITIONALCOMPUTATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("BANKALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("PAYMENTDETAILS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("BANKEXPORTFORMATS.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("BILLALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("INTERESTCOLLECTION.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("LEDGERCLOSINGVALUES.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("LEDGERAUDITCLASS.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("OLDAUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("TDSEXEMPTIONRULES.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("DEDUCTINSAMEVCHRULES.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("LOWERDEDUCTION.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("STXABATEMENTDETAILS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("LEDMULTIADDRESSLIST.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("STXTAXDETAILS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("CHEQUERANGE.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("DEFAULTVCHCHEQUEDETAILS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("ACCOUNTAUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("AUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("BRSIMPORTEDINFO.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("AUTOBRSCONFIGS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("BANKURENTRIES.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("DEFAULTCHEQUEDETAILS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("DEFAULTOPENINGCHEQUEDETAILS.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteEndElement();
if (unit != null && unit.IsGroupCreated == false)
{
objXmlWriter3.WriteStartElement("TALLYMESSAGE"); objXmlWriter3.WriteAttributeString("xmlns", "UDF", null, "TallyUDF");
objXmlWriter3.WriteStartElement("GROUP"); objXmlWriter3.WriteAttributeString("NAME", unit.Unit); objXmlWriter3.WriteAttributeString("ACTION", "CREATE");
objXmlWriter3.WriteStartElement("NAME.LIST"); objXmlWriter3.WriteElementString("NAME", unit.Unit); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteElementString("PARENT", "Sundry Debtors"); objXmlWriter3.WriteElementString("ISSUBLEDGER", "No"); objXmlWriter3.WriteElementString("ISBILLWISEON", "No");
objXmlWriter3.WriteElementString("ISCOSTCENTRESON", "No"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteEndElement();
}
decimal totalConsultantAmt = uCon.ConsultantOrders.Sum(x => x.ConsultantPrice * x.OrdQty);
decimal consultantAmount = uCon.ConsultantOrders.Where(x => !x.ItemName.StartsWith("PPP")).Sum(x => x.ConsultantPrice * x.OrdQty);
decimal netAmount = ((consultantAmount / Decimal.Parse("112.5") * Decimal.Parse("100")));
//decimal tax = ((netAmount * Decimal.Parse("12.5")) / Decimal.Parse("100"));
var ordId = uCon.ConsultantOrders.Select(x => x.OrderId).First();
var voucherId = uCon.ConsultantOrders.Where(x => x.OrderId == ordId).Select(x => x.VoucherId).First();
netAmount = Math.Round(netAmount, 2);
decimal tax = consultantAmount - netAmount;
//tax = TruncateDecimal(tax, 2);
decimal chkAmt = (consultantAmount - (netAmount + tax));
//if (chkAmt <= 1 || chkAmt >= -1)
//{
// tax = tax + chkAmt;
//}
string VoucherNo = week + "-" + ordId + "/" + voucherId;
objXmlWriter3.WriteStartElement("TALLYMESSAGE"); objXmlWriter3.WriteAttributeString("xmlns", "UDF", null, "TallyUDF");
objXmlWriter3.WriteStartElement("VOUCHER"); objXmlWriter3.WriteAttributeString("VCHTYPE", "Sales"); objXmlWriter3.WriteAttributeString("ACTION", "Create");
objXmlWriter3.WriteAttributeString("OBJVIEW", "Invoice Voucher View"); objXmlWriter3.WriteStartElement("ADDRESS.LIST"); objXmlWriter3.WriteAttributeString("TYPE", "String");
objXmlWriter3.WriteElementString("ADDRESS", unit.Unit); objXmlWriter3.WriteElementString("ADDRESS", assembly); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("BASICBUYERADDRESS.LIST"); objXmlWriter3.WriteAttributeString("TYPE", "String");
objXmlWriter3.WriteElementString("BASICBUYERADDRESS", unit.Unit); objXmlWriter3.WriteElementString("BASICBUYERADDRESS", assembly); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("OLDAUDITENTRYIDS.LIST"); objXmlWriter3.WriteAttributeString("TYPE", "Number"); objXmlWriter3.WriteElementString("OLDAUDITENTRYIDS", "-1");
objXmlWriter3.WriteElementString("DATE", model.Date); objXmlWriter3.WriteElementString("PARTYNAME", consultant); objXmlWriter3.WriteElementString("VOUCHERTYPENAME", "Sales");
objXmlWriter3.WriteElementString("VOUCHERNUMBER", VoucherNo); objXmlWriter3.WriteElementString("PARTYLEDGERNAME", consultant); objXmlWriter3.WriteElementString("BASICBASEPARTYNAME", consultant);
objXmlWriter3.WriteStartElement("CSTFORMISSUETYPE"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("CSTFORMRECVTYPE"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteElementString("FBTPAYMENTTYPE", "Default"); objXmlWriter3.WriteElementString("PERSISTEDVIEW", "Invoice Voucher View");objXmlWriter3.WriteElementString("BASICBUYERNAME", consultant);
objXmlWriter3.WriteElementString("BASICFINALDESTINATION", assembly); objXmlWriter3.WriteStartElement("VCHGSTCLASS"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteElementString("DIFFACTUALQTY", "No");
objXmlWriter3.WriteElementString("AUDITED", "No"); objXmlWriter3.WriteElementString("FORJOBCOSTING", "No"); objXmlWriter3.WriteElementString("ISOPTIONAL", "No");
objXmlWriter3.WriteElementString("EFFECTIVEDATE", model.Date); objXmlWriter3.WriteElementString("ISFORJOBWORKIN", "No"); objXmlWriter3.WriteElementString("ALLOWCONSUMPTION", "No");
objXmlWriter3.WriteElementString("USEFORINTEREST", "No"); objXmlWriter3.WriteElementString("USEFORGAINLOSS", "No"); objXmlWriter3.WriteElementString("USEFORGODOWNTRANSFER", "No");
objXmlWriter3.WriteElementString("USEFORCOMPOUND", "No"); objXmlWriter3.WriteElementString("EXCISEOPENING", "No"); objXmlWriter3.WriteElementString("USEFORFINALPRODUCTION", "No");
objXmlWriter3.WriteElementString("ISCANCELLED", "No"); objXmlWriter3.WriteElementString("HASCASHFLOW", "No"); objXmlWriter3.WriteElementString("ISPOSTDATED", "No");
objXmlWriter3.WriteElementString("USETRACKINGNUMBER", "No"); objXmlWriter3.WriteElementString("ISINVOICE", "Yes"); objXmlWriter3.WriteElementString("MFGJOURNAL", "No");
objXmlWriter3.WriteElementString("HASDISCOUNTS", "No"); objXmlWriter3.WriteElementString("ASPAYSLIP", "No"); objXmlWriter3.WriteElementString("ISCOSTCENTRE", "No");
objXmlWriter3.WriteElementString("ISSTXNONREALIZEDVCH", "No"); objXmlWriter3.WriteElementString("ISEXCISEMANUFACTURERON", "Yes"); objXmlWriter3.WriteElementString("ISBLANKCHEQUE", "No");
objXmlWriter3.WriteElementString("ISDELETED", "No"); objXmlWriter3.WriteElementString("ASORIGINAL", "No"); objXmlWriter3.WriteElementString("VCHISFROMSYNC", "No");
objXmlWriter3.WriteStartElement("OLDAUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("ACCOUNTAUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("AUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("INVOICEDELNOTES.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("INVOICEORDERLIST.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("INVOICEINDENTLIST.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("ATTENDANCEENTRIES.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("ORIGINVOICEDETAILS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("INVOICEEXPORTLIST.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("LEDGERENTRIES.LIST");
objXmlWriter3.WriteStartElement("OLDAUDITENTRYIDS.LIST"); objXmlWriter3.WriteAttributeString("TYPE", "Number"); objXmlWriter3.WriteElementString("OLDAUDITENTRYIDS", "-1");
objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteElementString("LEDGERNAME", consultant); objXmlWriter3.WriteStartElement("GSTCLASS"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteElementString("ISDEEMEDPOSITIVE", "Yes"); objXmlWriter3.WriteElementString("LEDGERFROMITEM", "No"); objXmlWriter3.WriteElementString("REMOVEZEROENTRIES", "No");
objXmlWriter3.WriteElementString("ISPARTYLEDGER", "Yes"); objXmlWriter3.WriteElementString("ISLASTDEEMEDPOSITIVE", "Yes"); objXmlWriter3.WriteElementString("AMOUNT", "-" + totalConsultantAmt);
objXmlWriter3.WriteStartElement("CATEGORYALLOCATIONS.LIST"); objXmlWriter3.WriteElementString("CATEGORY", "Primary Cost Category"); objXmlWriter3.WriteElementString("ISDEEMEDPOSITIVE", "Yes");
objXmlWriter3.WriteStartElement("COSTCENTREALLOCATIONS.LIST"); objXmlWriter3.WriteElementString("NAME", assembly); objXmlWriter3.WriteElementString("AMOUNT", "-" + totalConsultantAmt);
objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("BANKALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("BILLALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();objXmlWriter3.WriteStartElement("INTERESTCOLLECTION.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("OLDAUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("ACCOUNTAUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("AUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("TAXBILLALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("TAXOBJECTALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("TDSEXPENSEALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("VATSTATUTORYDETAILS.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("COSTTRACKALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("LEDGERENTRIES.LIST");
objXmlWriter3.WriteStartElement("OLDAUDITENTRYIDS.LIST"); objXmlWriter3.WriteAttributeString("TYPE", "Number"); objXmlWriter3.WriteElementString("OLDAUDITENTRYIDS", "-1");
objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("BASICRATEOFINVOICETAX.LIST"); objXmlWriter3.WriteAttributeString("TYPE", "Number");
objXmlWriter3.WriteElementString("BASICRATEOFINVOICETAX", "12.50"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteElementString("TAXCLASSIFICATIONNAME", "Output VAT @ 12.5%");
objXmlWriter3.WriteElementString("ROUNDTYPE", "Normal Rounding"); objXmlWriter3.WriteElementString("LEDGERNAME", "12.5% Vat on Sales"); objXmlWriter3.WriteStartElement("GSTCLASS"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteElementString("ISDEEMEDPOSITIVE", "No"); objXmlWriter3.WriteElementString("LEDGERFROMITEM", "No"); objXmlWriter3.WriteElementString("REMOVEZEROENTRIES", "No");
objXmlWriter3.WriteElementString("ISPARTYLEDGER", "No"); objXmlWriter3.WriteElementString("ISLASTDEEMEDPOSITIVE", "No"); objXmlWriter3.WriteElementString("AMOUNT", tax.ToString());
objXmlWriter3.WriteElementString("VATASSESSABLEVALUE", netAmount.ToString()); objXmlWriter3.WriteStartElement("BANKALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("BILLALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("INTERESTCOLLECTION.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("OLDAUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("ACCOUNTAUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("AUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("TAXBILLALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("TAXOBJECTALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteElementString("CATEGORY", "Output VAT @ 12.5%");
objXmlWriter3.WriteElementString("TAXTYPE", "VAT"); objXmlWriter3.WriteElementString("TAXNAME", VoucherNo); objXmlWriter3.WriteElementString("PARTYLEDGER", consultant);
objXmlWriter3.WriteElementString("REFTYPE", "New Ref"); objXmlWriter3.WriteElementString("ISOPTIONAL", "No"); objXmlWriter3.WriteElementString("ISPANVALID", "No");
objXmlWriter3.WriteElementString("ZERORATED", "No"); objXmlWriter3.WriteElementString("EXEMPTED", "No"); objXmlWriter3.WriteElementString("ISSPECIALRATE", "No");
objXmlWriter3.WriteElementString("ISDEDUCTNOW", "No"); objXmlWriter3.WriteElementString("ISPANNOTAVAILABLE", "No"); objXmlWriter3.WriteElementString("ISSUPPLEMENTARY", "No");
objXmlWriter3.WriteStartElement("OLDAUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("ACCOUNTAUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("AUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement();
decimal trackNetAmtTotal = 0;
int count = uCon.ConsultantOrders.Where(x => !x.ItemName.StartsWith("PPP")).Count();
int q = 0;
foreach (var con in uCon.ConsultantOrders)
{
if (!con.ItemName.StartsWith("PPP"))
{
decimal itemTotalVal = con.ConsultantPrice * con.OrdQty;
decimal itemRate = itemTotalVal / con.OrdQty;
decimal itemNetAmt = (con.ConsultantPrice / Decimal.Parse("112.5") * Decimal.Parse("100"));
itemNetAmt = Math.Round(itemNetAmt, 2);
//decimal itemTax = (itemNetAmt * Decimal.Parse("12.5") / Decimal.Parse("100"));
decimal itemTax = con.ConsultantPrice - itemNetAmt;
itemRate = itemRate - itemTax;
decimal itemsTotalAmt = itemRate * con.OrdQty;
decimal itemTotalTax = itemTax * con.OrdQty;
decimal itemTotalNetAmt = itemNetAmt * con.OrdQty;
//----------------------------------------------------
//INCLUDED TO TRACK NET AMT TOTALS AFFECTING ROUNDING
//-----------------------------------------------------
trackNetAmtTotal += itemTotalNetAmt;
if (q == count - 1) // last item in consultant orders
{
if (netAmount > trackNetAmtTotal || netAmount < trackNetAmtTotal)
{
checkedAmt = netAmount - trackNetAmtTotal;
itemsTotalAmt = itemsTotalAmt + checkedAmt;
itemTotalTax = itemTotalTax - checkedAmt;
}
}
objXmlWriter3.WriteStartElement("SUBCATEGORYALLOCATION.LIST"); objXmlWriter3.WriteElementString("STOCKITEMNAME", con.ItemName + "-" + con.ItemCode);
objXmlWriter3.WriteElementString("SUBCATEGORY", "VAT"); objXmlWriter3.WriteElementString("DUTYLEDGER", "12.5% Vat on Sales");
objXmlWriter3.WriteElementString("SUBCATZERORATED", "No"); objXmlWriter3.WriteElementString("SUBCATEXEMPTED", "No");
objXmlWriter3.WriteElementString("SUBCATISSPECIALRATE", "No"); objXmlWriter3.WriteElementString("TAXRATE", " 12.50");
objXmlWriter3.WriteElementString("ASSESSABLEAMOUNT", itemsTotalAmt.ToString()); objXmlWriter3.WriteElementString("TAX", itemTotalTax.ToString());
objXmlWriter3.WriteElementString("BILLEDQTY", con.OrdQty.ToString()); objXmlWriter3.WriteEndElement();
q++;
}
}
objXmlWriter3.WriteStartElement("TAXOBJECTALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("TDSEXPENSEALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("VATSTATUTORYDETAILS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("COSTTRACKALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteEndElement();
q = 0;
trackNetAmtTotal = 0;
foreach (var con in uCon.ConsultantOrders)
{
if (!con.ItemName.StartsWith("PPP"))
{
decimal itemTotalVal = con.ConsultantPrice * con.OrdQty;
decimal itemRate = itemTotalVal / con.OrdQty;
decimal itemNetAmt = (con.ConsultantPrice / Decimal.Parse("112.5") * Decimal.Parse("100"));
itemNetAmt = Math.Round(itemNetAmt, 2);
//decimal itemTax = (itemNetAmt * Decimal.Parse("12.5") / Decimal.Parse("100"));
decimal itemTax = con.ConsultantPrice - itemNetAmt;
itemRate = itemRate - itemTax;
decimal itemsTotalAmt = itemRate * con.OrdQty;
decimal itemTotalTax = itemTax * con.OrdQty;
decimal itemTotalNetAmt = itemNetAmt * con.OrdQty;
//----------------------------------------------------
//INCLUDED TO TRACK NET AMT TOTALS AFFECTING ROUNDING
//-----------------------------------------------------
trackNetAmtTotal += itemTotalNetAmt;
if (q == count - 1) // last item in consultant orders
{
if (netAmount > trackNetAmtTotal || netAmount < trackNetAmtTotal)
{
checkedAmt = netAmount - trackNetAmtTotal;
itemsTotalAmt = itemsTotalAmt + checkedAmt;
itemTotalTax = itemTotalTax - checkedAmt;
itemRate = itemsTotalAmt / con.OrdQty;
}
}
//-----------------------------------------------------------------------
objXmlWriter3.WriteStartElement("ALLINVENTORYENTRIES.LIST"); objXmlWriter3.WriteElementString("STOCKITEMNAME", con.ItemName + "-" + con.ItemCode);
objXmlWriter3.WriteElementString("ISDEEMEDPOSITIVE", "No"); objXmlWriter3.WriteElementString("ISLASTDEEMEDPOSITIVE", "No");
objXmlWriter3.WriteElementString("ISAUTONEGATE", "No"); objXmlWriter3.WriteElementString("ISCUSTOMSCLEARANCE", "No");
objXmlWriter3.WriteElementString("ISTRACKCOMPONENT", "No"); objXmlWriter3.WriteElementString("ISTRACKPRODUCTION", "No");
objXmlWriter3.WriteElementString("ISPRIMARYITEM", "No"); objXmlWriter3.WriteElementString("ISSCRAP", "No");
objXmlWriter3.WriteElementString("RATE", itemRate.ToString() + "/NO"); objXmlWriter3.WriteElementString("AMOUNT", itemsTotalAmt.ToString());
objXmlWriter3.WriteElementString("ACTUALQTY", con.OrdQty.ToString()); objXmlWriter3.WriteElementString("BILLEDQTY", con.OrdQty.ToString());
objXmlWriter3.WriteStartElement("BATCHALLOCATIONS.LIST"); objXmlWriter3.WriteElementString("GODOWNNAME", "Main Location");
objXmlWriter3.WriteElementString("BATCHNAME", "Primary Batch"); objXmlWriter3.WriteStartElement("INDENTNO"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("ORDERNO"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("TRACKINGNUMBER"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteElementString("DYNAMICCSTISCLEARED", "No"); objXmlWriter3.WriteElementString("AMOUNT", itemsTotalAmt.ToString());
objXmlWriter3.WriteElementString("ACTUALQTY", con.OrdQty.ToString() + " NO"); objXmlWriter3.WriteElementString("BILLEDQTY", con.OrdQty.ToString() + " NO");
objXmlWriter3.WriteStartElement("ADDITIONALDETAILS.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("VOUCHERCOMPONENTLIST.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("ACCOUNTINGALLOCATIONS.LIST"); objXmlWriter3.WriteStartElement("OLDAUDITENTRYIDS.LIST");
objXmlWriter3.WriteAttributeString("TYPE", "Number"); objXmlWriter3.WriteElementString("OLDAUDITENTRYIDS", "-1"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteElementString("TAXCLASSIFICATIONNAME", "Output VAT @ 12.5%"); objXmlWriter3.WriteElementString("LEDGERNAME", "Sales @12.5%");
objXmlWriter3.WriteStartElement("GSTCLASS"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteElementString("ISDEEMEDPOSITIVE", "No");
objXmlWriter3.WriteElementString("LEDGERFROMITEM", "No"); objXmlWriter3.WriteElementString("REMOVEZEROENTRIES", "No");
objXmlWriter3.WriteElementString("ISPARTYLEDGER", "No"); objXmlWriter3.WriteElementString("ISLASTDEEMEDPOSITIVE", "No");
objXmlWriter3.WriteElementString("AMOUNT", itemsTotalAmt.ToString()); objXmlWriter3.WriteStartElement("BANKALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("BILLALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("INTERESTCOLLECTION.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("OLDAUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("ACCOUNTAUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("AUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("TAXBILLALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("TAXOBJECTALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("TDSEXPENSEALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("VATSTATUTORYDETAILS.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("COSTTRACKALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("OLDAUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("ACCOUNTAUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("TAXOBJECTALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("EXCISEALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("EXPENSEALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteEndElement();
q++;
itemNetAmt = XcelServiceHelper.TruncateDecimal(itemNetAmt, 2);
decimal chkItemTax = con.ConsultantPrice - (itemNetAmt + itemTax);
if (chkItemTax <= 1 || chkItemTax >= -1)
{
itemTax = itemTax + chkItemTax;
}
PostedTransaction trans;
//using (var db = new eXmlContext())
//{
trans = unitOfWork.PostedTransactions.All()
.Where(x => x.ItemCode == con.ItemCode)
.Where(x => x.OrderId == con.OrderId)
.Where(x => x.ConsultantCode == con.Consultant.ConsultantId)
.FirstOrDefault();
if (trans == null)
{
trans = new PostedTransaction
{
Company = model.Company,
UnitName = unit.Unit,
AssemblyName = assembly,
ConsultantName = uCon.Consultant,
ConsultantCode = uCon.ConsultantId,
PostDate = DateTime.Now,
Year = year,
Week = week,
InvoiceNo = VoucherNo,
GrossAmount = con.Amount,
ConsultantPrice = con.ConsultantPrice,
NetAmount = itemNetAmt,
VatAmount = itemTax,
ItemCode = con.ItemCode,
ItemName = con.ItemName,
OrderId = con.OrderId,
OrderQty = con.OrdQty,
Status = con.Status,
PayStatus = enPaymentStatus.Pending,
InventoryStatus = enInventoryStatus.Pending,
PaymentAmount = 0,
PostType = enPostType.Invoice_12_5_WithAddress
};
unitOfWork.PostedTransactions.Insert(trans);
}
else
{
trans.ItemCode = con.ItemCode;
trans.GrossAmount = con.Amount;
trans.ConsultantPrice = con.ConsultantPrice;
trans.NetAmount = itemNetAmt;
trans.VatAmount = itemTax + (con.ConsultantPrice - (itemNetAmt + itemTax));
trans.ItemCode = con.ItemCode;
trans.ItemName = con.ItemName;
trans.OrderId = con.OrderId;
trans.OrderQty = con.OrdQty;
trans.Status = con.Status;
unitOfWork.PostedTransactions.Update(trans);
}
//}
}
}
#region VAT_5%
if (uCon.ConsultantOrders.Count(x => x.ItemName.StartsWith("PPP")) > 0) // 5 % VAT Items
{
consultantAmount = uCon.ConsultantOrders.Where(x => x.ItemName.StartsWith("PPP")).Sum(x => x.ConsultantPrice * x.OrdQty);
netAmount = ((consultantAmount / Decimal.Parse("105") * Decimal.Parse("100")));
//tax = ((netAmount * Decimal.Parse("5")) / Decimal.Parse("100"));
netAmount = Math.Round(netAmount, 2);
tax = consultantAmount - netAmount;
//tax = TruncateDecimal(tax, 2);
chkAmt = (consultantAmount - (netAmount + tax));
//if (chkAmt <= 1 || chkAmt >= -1)
//{
// tax = tax + chkAmt;
//}
objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("LEDGERENTRIES.LIST");
objXmlWriter3.WriteStartElement("OLDAUDITENTRYIDS.LIST"); objXmlWriter3.WriteAttributeString("TYPE", "Number"); objXmlWriter3.WriteElementString("OLDAUDITENTRYIDS", "-1");
objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("BASICRATEOFINVOICETAX.LIST"); objXmlWriter3.WriteAttributeString("TYPE", "Number");
objXmlWriter3.WriteElementString("BASICRATEOFINVOICETAX", "5"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteElementString("TAXCLASSIFICATIONNAME", "Output VAT @ 5%");
objXmlWriter3.WriteElementString("ROUNDTYPE", "Normal Rounding"); objXmlWriter3.WriteElementString("LEDGERNAME", "Output Vat @5%"); objXmlWriter3.WriteStartElement("GSTCLASS"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteElementString("ISDEEMEDPOSITIVE", "No"); objXmlWriter3.WriteElementString("LEDGERFROMITEM", "No"); objXmlWriter3.WriteElementString("REMOVEZEROENTRIES", "No");
objXmlWriter3.WriteElementString("ISPARTYLEDGER", "No"); objXmlWriter3.WriteElementString("ISLASTDEEMEDPOSITIVE", "No"); objXmlWriter3.WriteElementString("AMOUNT", tax.ToString());
objXmlWriter3.WriteElementString("VATASSESSABLEVALUE", netAmount.ToString()); objXmlWriter3.WriteStartElement("BANKALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("BILLALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("INTERESTCOLLECTION.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("OLDAUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("ACCOUNTAUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("AUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("TAXBILLALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("TAXOBJECTALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteElementString("CATEGORY", "Output VAT @ 5%");
objXmlWriter3.WriteElementString("TAXTYPE", "VAT"); objXmlWriter3.WriteElementString("TAXNAME", VoucherNo); objXmlWriter3.WriteElementString("PARTYLEDGER", consultant);
objXmlWriter3.WriteElementString("REFTYPE", "New Ref"); objXmlWriter3.WriteElementString("ISOPTIONAL", "No"); objXmlWriter3.WriteElementString("ISPANVALID", "No");
objXmlWriter3.WriteElementString("ZERORATED", "No"); objXmlWriter3.WriteElementString("EXEMPTED", "No"); objXmlWriter3.WriteElementString("ISSPECIALRATE", "No");
objXmlWriter3.WriteElementString("ISDEDUCTNOW", "No"); objXmlWriter3.WriteElementString("ISPANNOTAVAILABLE", "No"); objXmlWriter3.WriteElementString("ISSUPPLEMENTARY", "No");
objXmlWriter3.WriteStartElement("OLDAUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("ACCOUNTAUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("AUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement();
var PercentItems = uCon.ConsultantOrders.Where(x => x.ItemName.StartsWith("PPP")).ToList();
trackNetAmtTotal = 0;
count = PercentItems.Count();
q = 0;
foreach (var con in PercentItems)
{
decimal itemTotalVal = con.ConsultantPrice * con.OrdQty;
decimal itemRate = itemTotalVal / con.OrdQty;
decimal itemNetAmt = (con.ConsultantPrice / Decimal.Parse("105") * Decimal.Parse("100"));
itemNetAmt = Math.Round(itemNetAmt, 2);
//decimal itemTax = (itemNetAmt * Decimal.Parse("5") / Decimal.Parse("100"));
decimal itemTax = con.ConsultantPrice - itemNetAmt;
itemRate = itemRate - itemTax;
decimal itemsTotalAmt = itemRate * con.OrdQty;
decimal itemTotalTax = itemTax * con.OrdQty;
decimal itemTotalNetAmt = itemNetAmt * con.OrdQty;
//----------------------------------------------------
//INCLUDED TO TRACK NET AMT TOTALS AFFECTING ROUNDING
//-----------------------------------------------------
trackNetAmtTotal += itemTotalNetAmt;
if (q == count - 1) // last item in consultant orders
{
if (netAmount > trackNetAmtTotal || netAmount < trackNetAmtTotal)
{
checkedAmt = netAmount - trackNetAmtTotal;
itemsTotalAmt = itemsTotalAmt + checkedAmt;
itemTotalTax = itemTotalTax - checkedAmt;
}
}
objXmlWriter3.WriteStartElement("SUBCATEGORYALLOCATION.LIST"); objXmlWriter3.WriteElementString("STOCKITEMNAME", con.ItemName + "-" + con.ItemCode);
objXmlWriter3.WriteElementString("SUBCATEGORY", "VAT"); objXmlWriter3.WriteElementString("DUTYLEDGER", "5% Vat on Sales");
objXmlWriter3.WriteElementString("SUBCATZERORATED", "No"); objXmlWriter3.WriteElementString("SUBCATEXEMPTED", "No");
objXmlWriter3.WriteElementString("SUBCATISSPECIALRATE", "No"); objXmlWriter3.WriteElementString("TAXRATE", "5");
objXmlWriter3.WriteElementString("ASSESSABLEAMOUNT", itemsTotalAmt.ToString()); objXmlWriter3.WriteElementString("TAX", itemTotalTax.ToString());
objXmlWriter3.WriteElementString("BILLEDQTY", con.OrdQty.ToString()); objXmlWriter3.WriteEndElement();
q++;
}
objXmlWriter3.WriteStartElement("TAXOBJECTALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("TDSEXPENSEALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("VATSTATUTORYDETAILS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("COSTTRACKALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteEndElement();
q = 0;
trackNetAmtTotal = 0;
foreach (var con in PercentItems)
{
decimal itemTotalVal = con.ConsultantPrice * con.OrdQty;
decimal itemRate = itemTotalVal / con.OrdQty;
decimal itemNetAmt = (con.ConsultantPrice / Decimal.Parse("105") * Decimal.Parse("100"));
itemNetAmt = Math.Round(itemNetAmt, 2);
//decimal itemTax = (itemNetAmt * Decimal.Parse("5") / Decimal.Parse("100"));
decimal itemTax = con.ConsultantPrice - itemNetAmt;
itemRate = itemRate - itemTax;
decimal itemsTotalAmt = itemRate * con.OrdQty;
decimal itemTotalTax = itemTax * con.OrdQty;
decimal itemTotalNetAmt = itemNetAmt * con.OrdQty;
//itemNetAmt = Math.Round(itemNetAmt, 0);
//----------------------------------------------------
//INCLUDED TO TRACK NET AMT TOTALS AFFECTING ROUNDING
//-----------------------------------------------------
trackNetAmtTotal += itemTotalNetAmt;
if (q == count - 1) // last item in consultant orders
{
if (netAmount > trackNetAmtTotal || netAmount < trackNetAmtTotal)
{
checkedAmt = netAmount - trackNetAmtTotal;
itemsTotalAmt = itemsTotalAmt + checkedAmt;
itemTotalTax = itemTotalTax - checkedAmt;
itemRate = itemsTotalAmt / con.OrdQty;
}
}
objXmlWriter3.WriteStartElement("ALLINVENTORYENTRIES.LIST"); objXmlWriter3.WriteElementString("STOCKITEMNAME", con.ItemName + "-" + con.ItemCode);
objXmlWriter3.WriteElementString("ISDEEMEDPOSITIVE", "No"); objXmlWriter3.WriteElementString("ISLASTDEEMEDPOSITIVE", "No");
objXmlWriter3.WriteElementString("ISAUTONEGATE", "No"); objXmlWriter3.WriteElementString("ISCUSTOMSCLEARANCE", "No");
objXmlWriter3.WriteElementString("ISTRACKCOMPONENT", "No"); objXmlWriter3.WriteElementString("ISTRACKPRODUCTION", "No");
objXmlWriter3.WriteElementString("ISPRIMARYITEM", "No"); objXmlWriter3.WriteElementString("ISSCRAP", "No");
objXmlWriter3.WriteElementString("RATE", itemRate.ToString() + "/NO"); objXmlWriter3.WriteElementString("AMOUNT", itemsTotalAmt.ToString());
objXmlWriter3.WriteElementString("ACTUALQTY", con.OrdQty.ToString()); objXmlWriter3.WriteElementString("BILLEDQTY", con.OrdQty.ToString());
objXmlWriter3.WriteStartElement("BATCHALLOCATIONS.LIST"); objXmlWriter3.WriteElementString("GODOWNNAME", "Main Location");
objXmlWriter3.WriteElementString("BATCHNAME", "Primary Batch"); objXmlWriter3.WriteStartElement("INDENTNO"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("ORDERNO"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("TRACKINGNUMBER"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteElementString("DYNAMICCSTISCLEARED", "No"); objXmlWriter3.WriteElementString("AMOUNT", itemsTotalAmt.ToString());
objXmlWriter3.WriteElementString("ACTUALQTY", con.OrdQty.ToString() + " NO"); objXmlWriter3.WriteElementString("BILLEDQTY", con.OrdQty.ToString() + " NO");
objXmlWriter3.WriteStartElement("ADDITIONALDETAILS.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("VOUCHERCOMPONENTLIST.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("ACCOUNTINGALLOCATIONS.LIST"); objXmlWriter3.WriteStartElement("OLDAUDITENTRYIDS.LIST");
objXmlWriter3.WriteAttributeString("TYPE", "Number"); objXmlWriter3.WriteElementString("OLDAUDITENTRYIDS", "-1"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteElementString("TAXCLASSIFICATIONNAME", "Output VAT @ 5%"); objXmlWriter3.WriteElementString("LEDGERNAME", "Sales @ 5%");
objXmlWriter3.WriteStartElement("GSTCLASS"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteElementString("ISDEEMEDPOSITIVE", "No");
objXmlWriter3.WriteElementString("LEDGERFROMITEM", "No"); objXmlWriter3.WriteElementString("REMOVEZEROENTRIES", "No");
objXmlWriter3.WriteElementString("ISPARTYLEDGER", "No"); objXmlWriter3.WriteElementString("ISLASTDEEMEDPOSITIVE", "No");
objXmlWriter3.WriteElementString("AMOUNT", itemsTotalAmt.ToString()); objXmlWriter3.WriteStartElement("BANKALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("BILLALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("INTERESTCOLLECTION.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("OLDAUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("ACCOUNTAUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("AUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("TAXBILLALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("TAXOBJECTALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("TDSEXPENSEALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("VATSTATUTORYDETAILS.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("COSTTRACKALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("OLDAUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("ACCOUNTAUDITENTRIES.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("TAXOBJECTALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteStartElement("EXCISEALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteStartElement("EXPENSEALLOCATIONS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteEndElement();
q++;
itemNetAmt = XcelServiceHelper.TruncateDecimal(itemNetAmt, 2);
decimal chkItemTax = con.ConsultantPrice - (itemNetAmt + itemTax);
if (chkItemTax <= 1 || chkItemTax >= -1)
{
itemTax = itemTax + chkItemTax;
}
PostedTransaction trans;
trans = unitOfWork.PostedTransactions.All()
.Where(x => x.ItemCode == con.ItemCode)
.Where(x => x.OrderId == con.OrderId)
.Where(x => x.ConsultantCode == con.Consultant.ConsultantId)
.FirstOrDefault();
if (trans == null)
{
trans = new PostedTransaction
{
Company = model.Company,
UnitName = unit.Unit,
AssemblyName = assembly,
ConsultantName = uCon.Consultant,
ConsultantCode = uCon.ConsultantId,
PostDate = DateTime.Now,
Year = year,
Week = week,
InvoiceNo = VoucherNo,
GrossAmount = con.Amount,
ConsultantPrice = con.ConsultantPrice,
NetAmount = itemNetAmt,
VatAmount = itemTax,
ItemCode = con.ItemCode,
ItemName = con.ItemName,
OrderId = con.OrderId,
OrderQty = con.OrdQty,
Status = con.Status,
PayStatus = enPaymentStatus.Pending,
InventoryStatus = enInventoryStatus.Pending,
PaymentAmount = 0,
PostType = enPostType.Invoice_12_5_WithAddress
};
unitOfWork.PostedTransactions.Insert(trans);
}
else
{
trans.ItemCode = con.ItemCode;
trans.GrossAmount = con.Amount;
trans.ConsultantPrice = con.ConsultantPrice;
trans.NetAmount = itemNetAmt;
trans.VatAmount = itemTax + (con.ConsultantPrice - (itemNetAmt + itemTax));
trans.ItemCode = con.ItemCode;
trans.ItemName = con.ItemName;
trans.OrderId = con.OrderId;
trans.OrderQty = con.OrdQty;
trans.Status = con.Status;
unitOfWork.PostedTransactions.Update(trans);
}
}
}
#endregion VAT_5%
objXmlWriter3.WriteStartElement("ATTDRECORDS.LIST"); objXmlWriter3.WriteEndElement();
objXmlWriter3.WriteEndElement(); objXmlWriter3.WriteEndElement();
//strData = strData + "<ATTDRECORDS.LIST> </ATTDRECORDS.LIST>" +
//"</VOUCHER></TALLYMESSAGE>";
objXmlWriter3.Flush();
}
//objXmlWriter3.Dispose();
}
unitOfWork.Commit();
unitOfWork.Dispose();
}
string closeTag = "</REQUESTDATA></IMPORTDATA></BODY></ENVELOPE>";
var fs = System.IO.File.Open(savePath + "//payment.xml", System.IO.FileMode.Open,System.IO.FileAccess.ReadWrite);
fs.Position = fs.Length;
var writer = new StreamWriter(fs); // after the Position is set
writer.Write(closeTag); // NOT WriteLine !!
writer.Close();
fs.Close();
}
}
public static void processExcelSheet(UploadFileModel model, string fileName, string savePath)
{
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart workBk = doc.WorkbookPart;
WorksheetPart workSht = workBk.WorksheetParts.First();
SheetData shtData = workSht.Worksheet.Elements<SheetData>().First();
Sheet theSheet = workBk.Workbook.Descendants<Sheet>().
Where(s => s.SheetId == 1).FirstOrDefault();
var strData = "<ENVELOPE><HEADER><TALLYREQUEST>Import Data</TALLYREQUEST></HEADER><BODY><IMPORTDATA>" +
"<REQUESTDESC> <REPORTNAME>All Masters</REPORTNAME><STATICVARIABLES><SVCURRENTCOMPANY>" + model.Company +
"</SVCURRENTCOMPANY></STATICVARIABLES></REQUESTDESC><REQUESTDATA>";
int rows = 0;
string cellA; string cellB; string cellC; string cellD; string cellE; string cellG;
string cellF; string cellH; string cellL; string cellO; string cellJ; string cellQ;
string cellU; string cellW; string cellY; string cellAB; string cellAD; string cellAssembly;
var yearwk = ""; var year = ""; var week = ""; var assembly = ""; var date1 = ""; var unitname = "";
var cofNo = ""; var consutantCode = ""; var consultantName = ""; decimal grossAmt; decimal netAmt;
decimal vatAmt; decimal checkedAmt; string partyName; var panNo = ""; int mnth; var narration = "";
var month = ""; var day = ""; var item = ""; var itemCode =""; var itemName = ""; int orderId;
decimal MRP; decimal ordQty; decimal consPrice; decimal amount; var status ="";
//if (model.Type == enPostType.Invoice_12_5_WithAddress)
//{
List<UnitName> units = new List<UnitName>();
List<UnitConsultant> unitConsultants = new List<UnitConsultant>();
List<ConsultantOrder> consOrders = new List<ConsultantOrder>();
List<StockItem> stockItems = new List<StockItem>();
cellAssembly = "L8";
assembly = GetCellValue(workBk, theSheet, cellAssembly);
week = GetCellValue(workBk,theSheet,"U9");
year = GetCellValue(workBk, theSheet, "U8");
UnitName u;
ConsultantOrder co;
UnitConsultant uc;
int j =0 ;
int rowCount = shtData.Elements<Row>().Count();
rowCount = rowCount + 4;
//foreach (Row r in shtData.Elements<Row>())
for (rows = 1; rows <= rowCount; rows++ )
{
//rows += 1;
cellQ = "Q" + rows;
if (rows >= 12)
{
//Cell theCell = r.Descendants<Cell>().Where(x => x.CellReference == cellQ).FirstOrDefault();
string theCell = GetCellValue(workBk, theSheet, cellQ);
if (!string.IsNullOrEmpty(theCell))
{
//
cellQ = "Q" + rows;
cellJ = "J" + rows;
itemCode = GetCellValue(workBk, theSheet, cellJ);
itemName = GetCellValue(workBk, theSheet, cellQ);
itemName = itemName.Replace("(", " ");
itemName = itemName.Replace(")", " ");
itemName = itemName.Replace("&", "_");
item = itemName + " - " + itemCode;
StockItem s = stockItems.FirstOrDefault(x => x.ItemCode == itemCode.Trim());
if (s == null)
{
strData = strData + @"<TALLYMESSAGE xmlns:UDF=""TallyUDF"">" +
@"<STOCKITEM NAME="""
+ item +
@""" RESERVEDNAME="""">" +
@"<OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS></OLDAUDITENTRYIDS.LIST>" +
"<PARENT>Tupperware Products</PARENT><CATEGORY/><TAXCLASSIFICATIONNAME/><COSTINGMETHOD>Avg. Cost</COSTINGMETHOD>" +
"<VALUATIONMETHOD>Avg. Price</VALUATIONMETHOD><BASEUNITS>NO</BASEUNITS><ADDITIONALUNITS/><EXCISEITEMCLASSIFICATION/>" +
"<ISCOSTCENTRESON>No</ISCOSTCENTRESON><ISBATCHWISEON>No</ISBATCHWISEON><ISPERISHABLEON>No</ISPERISHABLEON><ISENTRYTAXAPPLICABLE>No</ISENTRYTAXAPPLICABLE>" +
"<ISCOSTTRACKINGON>No</ISCOSTTRACKINGON><IGNOREPHYSICALDIFFERENCE>No</IGNOREPHYSICALDIFFERENCE><IGNORENEGATIVESTOCK>No</IGNORENEGATIVESTOCK>" +
"<TREATSALESASMANUFACTURED>No</TREATSALESASMANUFACTURED><TREATPURCHASESASCONSUMED>No</TREATPURCHASESASCONSUMED><TREATREJECTSASSCRAP>No</TREATREJECTSASSCRAP>" +
"<HASMFGDATE>No</HASMFGDATE><ALLOWUSEOFEXPIREDITEMS>No</ALLOWUSEOFEXPIREDITEMS><IGNOREBATCHES>No</IGNOREBATCHES><IGNOREGODOWNS>No</IGNOREGODOWNS>" +
"<CALCONMRP>No</CALCONMRP><EXCLUDEJRNLFORVALUATION>No</EXCLUDEJRNLFORVALUATION><ISMRPINCLOFTAX>No</ISMRPINCLOFTAX><ISADDLTAXEXEMPT>No</ISADDLTAXEXEMPT>" +
"<ISSUPPLEMENTRYDUTYON>No</ISSUPPLEMENTRYDUTYON><REORDERASHIGHER>No</REORDERASHIGHER><MINORDERASHIGHER>No</MINORDERASHIGHER><DENOMINATOR> 1</DENOMINATOR>" +
@"<RATEOFVAT> 12.5</RATEOFVAT><LANGUAGENAME.LIST><NAME.LIST TYPE=""String""><NAME>" + item + "</NAME></NAME.LIST><LANGUAGEID> 1033</LANGUAGEID>" +
"</LANGUAGENAME.LIST><SCHVIDETAILS.LIST> </SCHVIDETAILS.LIST><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST><ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST>" +
"<AUDITENTRIES.LIST> </AUDITENTRIES.LIST><COMPONENTLIST.LIST> </COMPONENTLIST.LIST><ADDITIONALLEDGERS.LIST> </ADDITIONALLEDGERS.LIST>" +
"<SALESLIST.LIST> </SALESLIST.LIST><PURCHASELIST.LIST> </PURCHASELIST.LIST><FULLPRICELIST.LIST> </FULLPRICELIST.LIST>" +
"<BATCHALLOCATIONS.LIST> </BATCHALLOCATIONS.LIST><TRADEREXCISEDUTIES.LIST> </TRADEREXCISEDUTIES.LIST><STANDARDCOSTLIST.LIST> </STANDARDCOSTLIST.LIST>" +
"<STANDARDPRICELIST.LIST> </STANDARDPRICELIST.LIST><EXCISEITEMGODOWN.LIST> </EXCISEITEMGODOWN.LIST><MULTICOMPONENTLIST.LIST> </MULTICOMPONENTLIST.LIST>" +
"<PRICELEVELLIST.LIST> </PRICELEVELLIST.LIST></STOCKITEM></TALLYMESSAGE>";
s = new StockItem
{
ItemCode = itemCode.Trim().ToString(),
ItemName = itemName.Trim().ToString()
};
stockItems.Add(s);
}
cellA = "A" + rows;
unitname = GetCellValue(workBk, theSheet, cellA);
u = units.FirstOrDefault(x => x.Unit == unitname.Trim());
if (u == null)
{
u = new UnitName
{
Unit = unitname,
IsGroupCreated = false
};
units.Add(u);
units.OrderBy(x => x.Unit);
}
cellD = "D" + rows;
consutantCode = GetCellValue(workBk, theSheet, cellD);
cellF = "F" + rows;
consultantName = GetCellValue(workBk, theSheet, cellF);
uc = u.UnitConsultants.FirstOrDefault(x => x.Consultant == consultantName.Trim());
if (uc == null)
{
uc = new UnitConsultant
{
Consultant = consultantName.Trim(),
ConsultantId = consutantCode.Trim(),
UnitName = u
};
if (u != null) u.UnitConsultants.Add(uc);
}
cellH = "H" + rows;
orderId = int.Parse(GetCellValue(workBk, theSheet, cellH));
cellU = "U" + rows;
ordQty = Decimal.Parse(GetCellValue(workBk, theSheet, cellU));
cellW = "W" + rows;
MRP = Decimal.Parse(GetCellValue(workBk, theSheet, cellW));
cellY = "Y" + rows;
consPrice = Math.Round(Decimal.Parse(GetCellValue(workBk, theSheet, cellY)));
cellAB = "AB" + rows;
amount = Math.Round(Decimal.Parse(GetCellValue(workBk, theSheet, cellAB)));
cellAD = "AD" + rows;
status = GetCellValue(workBk, theSheet, cellAD);
co = uc.ConsultantOrders.FirstOrDefault(x => x.ItemCode == itemCode.Trim());
if (co == null)
{
co = new ConsultantOrder
{
OrderId = orderId,
Amount = amount,
ItemCode = itemCode.Trim(),
ItemName = itemName.Trim(),
MRP = MRP,
OrdQty = ordQty,
ConsultantPrice = consPrice,
Status = status,
Consultant = uc
};
co.VoucherId = j + 1;
j++;
if (uc != null)
{
//prevId = co.OrderId;
//prevVoucherId = co.VoucherId;
uc.ConsultantOrders.Add(co);
uc.ConsultantOrders.OrderBy(x => x.OrderId).ThenBy(x => x.ItemName);
}
}
}
}
}
foreach (var unit in units)
{
foreach (var uCon in unit.UnitConsultants )
{
string consultant = uCon.Consultant + " - " + uCon.ConsultantId;
//newVoucherNo = newVoucherNo + 1;
strData = strData + @"<TALLYMESSAGE xmlns:UDF=""TallyUDF"">" +
@"<LEDGER NAME="""
+ consultant +
@""" RESERVEDNAME="""">" +
@"<ADDRESS.LIST TYPE=""String""><ADDRESS>" + unit.Unit + "</ADDRESS><ADDRESS>" + assembly + "</ADDRESS></ADDRESS.LIST>" +
@"<MAILINGNAME.LIST TYPE=""String""><MAILINGNAME>" + consultant + "</MAILINGNAME></MAILINGNAME.LIST>" +
@"<OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS></OLDAUDITENTRYIDS.LIST>" +
"<ALTEREDON>" + model.Date + "</ALTEREDON><STATENAME>Maharashtra</STATENAME><PARENT>" + unit.Unit + "</PARENT><TAXCLASSIFICATIONNAME/>" +
"<TAXTYPE>Others</TAXTYPE><BUSINESSTYPE/><BASICTYPEOFDUTY>Excise Surcharge</BASICTYPEOFDUTY><GSTTYPE/><APPROPRIATEFOR/>" +
"<SERVICECATEGORY/><EXCISELEDGERCLASSIFICATION/><EXCISEDUTYTYPE/><EXCISENATUREOFPURCHASE/><LEDGERFBTCATEGORY/>" +
"<ISBILLWISEON>No</ISBILLWISEON><ISCOSTCENTRESON>Yes</ISCOSTCENTRESON><ISINTERESTON>No</ISINTERESTON><ALLOWINMOBILE>No</ALLOWINMOBILE>" +
"<ISCOSTTRACKINGON>No</ISCOSTTRACKINGON><ISCONDENSED>No</ISCONDENSED><AFFECTSSTOCK>No</AFFECTSSTOCK><FORPAYROLL>No</FORPAYROLL>" +
"<ISABCENABLED>No</ISABCENABLED><INTERESTONBILLWISE>No</INTERESTONBILLWISE><OVERRIDEINTEREST>No</OVERRIDEINTEREST><OVERRIDEADVINTEREST>No</OVERRIDEADVINTEREST>" +
"<USEFORVAT>No</USEFORVAT><IGNORETDSEXEMPT>No</IGNORETDSEXEMPT><ISTCSAPPLICABLE>No</ISTCSAPPLICABLE><ISTDSAPPLICABLE>No</ISTDSAPPLICABLE><ISFBTAPPLICABLE>No</ISFBTAPPLICABLE>" +
"<ISGSTAPPLICABLE>No</ISGSTAPPLICABLE><ISEXCISEAPPLICABLE>No</ISEXCISEAPPLICABLE><ISTDSEXPENSE>No</ISTDSEXPENSE><ISEDLIAPPLICABLE>No</ISEDLIAPPLICABLE>" +
"<ISRELATEDPARTY>No</ISRELATEDPARTY><USEFORESIELIGIBILITY>No</USEFORESIELIGIBILITY><SHOWINPAYSLIP>No</SHOWINPAYSLIP><USEFORGRATUITY>No</USEFORGRATUITY>" +
"<ISTDSPROJECTED>No</ISTDSPROJECTED><FORSERVICETAX>No</FORSERVICETAX><ISINPUTCREDIT>No</ISINPUTCREDIT><ISEXEMPTED>No</ISEXEMPTED><ISABATEMENTAPPLICABLE>No</ISABATEMENTAPPLICABLE>" +
"<ISSTXPARTY>No</ISSTXPARTY><ISSTXNONREALIZEDTYPE>No</ISSTXNONREALIZEDTYPE><TDSDEDUCTEEISSPECIALRATE>No</TDSDEDUCTEEISSPECIALRATE><AUDITED>No</AUDITED><SORTPOSITION> 1000</SORTPOSITION>" +
@"<RATEOFTAXCALCULATION> 12.50</RATEOFTAXCALCULATION><LANGUAGENAME.LIST><NAME.LIST TYPE=""String""><NAME>" + consultant + "</NAME></NAME.LIST><LANGUAGEID> 1033</LANGUAGEID>" +
"</LANGUAGENAME.LIST><XBRLDETAIL.LIST> </XBRLDETAIL.LIST><AUDITDETAILS.LIST> </AUDITDETAILS.LIST><SCHVIDETAILS.LIST> </SCHVIDETAILS.LIST><SLABPERIOD.LIST> </SLABPERIOD.LIST>" +
"<GRATUITYPERIOD.LIST> </GRATUITYPERIOD.LIST><ADDITIONALCOMPUTATIONS.LIST> </ADDITIONALCOMPUTATIONS.LIST><BANKALLOCATIONS.LIST> </BANKALLOCATIONS.LIST><PAYMENTDETAILS.LIST> </PAYMENTDETAILS.LIST>" +
"<BANKEXPORTFORMATS.LIST> </BANKEXPORTFORMATS.LIST><BILLALLOCATIONS.LIST> </BILLALLOCATIONS.LIST><INTERESTCOLLECTION.LIST> </INTERESTCOLLECTION.LIST><LEDGERCLOSINGVALUES.LIST> </LEDGERCLOSINGVALUES.LIST>" +
"<LEDGERAUDITCLASS.LIST> </LEDGERAUDITCLASS.LIST><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST><TDSEXEMPTIONRULES.LIST> </TDSEXEMPTIONRULES.LIST><DEDUCTINSAMEVCHRULES.LIST> </DEDUCTINSAMEVCHRULES.LIST>" +
"<LOWERDEDUCTION.LIST> </LOWERDEDUCTION.LIST><STXABATEMENTDETAILS.LIST> </STXABATEMENTDETAILS.LIST><LEDMULTIADDRESSLIST.LIST> </LEDMULTIADDRESSLIST.LIST><STXTAXDETAILS.LIST> </STXTAXDETAILS.LIST>" +
"<CHEQUERANGE.LIST> </CHEQUERANGE.LIST><DEFAULTVCHCHEQUEDETAILS.LIST> </DEFAULTVCHCHEQUEDETAILS.LIST><ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST><AUDITENTRIES.LIST> </AUDITENTRIES.LIST>" +
"<BRSIMPORTEDINFO.LIST> </BRSIMPORTEDINFO.LIST><AUTOBRSCONFIGS.LIST> </AUTOBRSCONFIGS.LIST><BANKURENTRIES.LIST> </BANKURENTRIES.LIST><DEFAULTCHEQUEDETAILS.LIST> </DEFAULTCHEQUEDETAILS.LIST>" +
"<DEFAULTOPENINGCHEQUEDETAILS.LIST> </DEFAULTOPENINGCHEQUEDETAILS.LIST></LEDGER></TALLYMESSAGE>";
if (unit != null && unit.IsGroupCreated == false)
{
strData = strData + @"<TALLYMESSAGE xmlns:UDF=""TallyUDF"">" +
@"<GROUP NAME="""
+ unit.Unit +
@""" ACTION = ""CREATE"">" +
"<NAME.LIST><NAME>" + unit.Unit + "</NAME></NAME.LIST><PARENT>Sundry Debtors</PARENT><ISSUBLEDGER>No</ISSUBLEDGER><ISBILLWISEON>No</ISBILLWISEON>" +
"<ISCOSTCENTRESON>No</ISCOSTCENTRESON></GROUP></TALLYMESSAGE>";
unit.IsGroupCreated = true;
}
decimal totalConsultantAmt = uCon.ConsultantOrders.Sum(x => x.ConsultantPrice * x.OrdQty);
decimal consultantAmount = uCon.ConsultantOrders.Where(x => !x.ItemName.StartsWith("PPP")).Sum(x => x.ConsultantPrice * x.OrdQty);
decimal netAmount =((consultantAmount /Decimal.Parse("112.5") * Decimal.Parse("100")));
//decimal tax = ((netAmount * Decimal.Parse("12.5")) / Decimal.Parse("100"));
var ordId = uCon.ConsultantOrders.Select(x => x.OrderId).First();
var voucherId = uCon.ConsultantOrders.Where(x => x.OrderId == ordId).Select(x => x.VoucherId).First();
netAmount = Math.Round(netAmount, 2);
decimal tax = consultantAmount - netAmount;
//tax = TruncateDecimal(tax, 2);
decimal chkAmt = (consultantAmount - (netAmount + tax));
//if (chkAmt <= 1 || chkAmt >= -1)
//{
// tax = tax + chkAmt;
//}
string VoucherNo = week + "-" + ordId + "/" + voucherId;
strData = strData + @"<TALLYMESSAGE xmlns:UDF=""TallyUDF"">" +
@"<VOUCHER VCHTYPE=""Sales"" ACTION=""Create"" OBJVIEW=""Invoice Voucher View"">" +
@"<ADDRESS.LIST TYPE=""String""><ADDRESS>" + unit.Unit + "</ADDRESS><ADDRESS>" + assembly + "</ADDRESS>" +
@"</ADDRESS.LIST><BASICBUYERADDRESS.LIST TYPE=""String""><BASICBUYERADDRESS>" + unit.Unit + "</BASICBUYERADDRESS>" +
"<BASICBUYERADDRESS>" + assembly + "</BASICBUYERADDRESS></BASICBUYERADDRESS.LIST>" +
@"<OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS></OLDAUDITENTRYIDS.LIST><DATE>" + model.Date + "</DATE>" +
"<PARTYNAME>" + consultant + "</PARTYNAME><VOUCHERTYPENAME>Sales</VOUCHERTYPENAME><VOUCHERNUMBER>" + VoucherNo + "</VOUCHERNUMBER>" +
"<PARTYLEDGERNAME>" + consultant + "</PARTYLEDGERNAME><BASICBASEPARTYNAME>" + consultant + "</BASICBASEPARTYNAME>" +
"<CSTFORMISSUETYPE/><CSTFORMRECVTYPE/><FBTPAYMENTTYPE>Default</FBTPAYMENTTYPE><PERSISTEDVIEW>Invoice Voucher View</PERSISTEDVIEW>" +
"<BASICBUYERNAME>" + consultant + "</BASICBUYERNAME><BASICFINALDESTINATION>" + assembly + "</BASICFINALDESTINATION>" +
"<VCHGSTCLASS/><DIFFACTUALQTY>No</DIFFACTUALQTY><AUDITED>No</AUDITED><FORJOBCOSTING>No</FORJOBCOSTING><ISOPTIONAL>No</ISOPTIONAL>" +
"<EFFECTIVEDATE>" + model.Date + "</EFFECTIVEDATE><ISFORJOBWORKIN>No</ISFORJOBWORKIN><ALLOWCONSUMPTION>No</ALLOWCONSUMPTION>" +
"<USEFORINTEREST>No</USEFORINTEREST><USEFORGAINLOSS>No</USEFORGAINLOSS><USEFORGODOWNTRANSFER>No</USEFORGODOWNTRANSFER>" +
"<USEFORCOMPOUND>No</USEFORCOMPOUND><EXCISEOPENING>No</EXCISEOPENING><USEFORFINALPRODUCTION>No</USEFORFINALPRODUCTION>" +
"<ISCANCELLED>No</ISCANCELLED><HASCASHFLOW>No</HASCASHFLOW><ISPOSTDATED>No</ISPOSTDATED><USETRACKINGNUMBER>No</USETRACKINGNUMBER>" +
"<ISINVOICE>Yes</ISINVOICE><MFGJOURNAL>No</MFGJOURNAL><HASDISCOUNTS>No</HASDISCOUNTS><ASPAYSLIP>No</ASPAYSLIP><ISCOSTCENTRE>No</ISCOSTCENTRE>" +
"<ISSTXNONREALIZEDVCH>No</ISSTXNONREALIZEDVCH><ISEXCISEMANUFACTURERON>Yes</ISEXCISEMANUFACTURERON><ISBLANKCHEQUE>No</ISBLANKCHEQUE>" +
"<ISDELETED>No</ISDELETED><ASORIGINAL>No</ASORIGINAL><VCHISFROMSYNC>No</VCHISFROMSYNC><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST>" +
"<ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST><AUDITENTRIES.LIST> </AUDITENTRIES.LIST><INVOICEDELNOTES.LIST> </INVOICEDELNOTES.LIST>" +
"<INVOICEORDERLIST.LIST> </INVOICEORDERLIST.LIST><INVOICEINDENTLIST.LIST> </INVOICEINDENTLIST.LIST><ATTENDANCEENTRIES.LIST> </ATTENDANCEENTRIES.LIST>" +
"<ORIGINVOICEDETAILS.LIST> </ORIGINVOICEDETAILS.LIST><INVOICEEXPORTLIST.LIST> </INVOICEEXPORTLIST.LIST><LEDGERENTRIES.LIST>" +
@"<OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS></OLDAUDITENTRYIDS.LIST><LEDGERNAME>" + consultant + "</LEDGERNAME>" +
"<GSTCLASS/><ISDEEMEDPOSITIVE>Yes</ISDEEMEDPOSITIVE><LEDGERFROMITEM>No</LEDGERFROMITEM><REMOVEZEROENTRIES>No</REMOVEZEROENTRIES><ISPARTYLEDGER>Yes</ISPARTYLEDGER>" +
"<ISLASTDEEMEDPOSITIVE>Yes</ISLASTDEEMEDPOSITIVE><AMOUNT>-" + totalConsultantAmt + "</AMOUNT><CATEGORYALLOCATIONS.LIST><CATEGORY>Primary Cost Category</CATEGORY>" +
"<ISDEEMEDPOSITIVE>Yes</ISDEEMEDPOSITIVE><COSTCENTREALLOCATIONS.LIST><NAME>" + assembly + "</NAME><AMOUNT>-" + totalConsultantAmt + "</AMOUNT>" +
"</COSTCENTREALLOCATIONS.LIST></CATEGORYALLOCATIONS.LIST><BANKALLOCATIONS.LIST> </BANKALLOCATIONS.LIST><BILLALLOCATIONS.LIST> </BILLALLOCATIONS.LIST>" +
"<INTERESTCOLLECTION.LIST> </INTERESTCOLLECTION.LIST><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST><ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST>" +
"<AUDITENTRIES.LIST> </AUDITENTRIES.LIST><TAXBILLALLOCATIONS.LIST> </TAXBILLALLOCATIONS.LIST><TAXOBJECTALLOCATIONS.LIST> </TAXOBJECTALLOCATIONS.LIST>" +
"<TDSEXPENSEALLOCATIONS.LIST> </TDSEXPENSEALLOCATIONS.LIST><VATSTATUTORYDETAILS.LIST> </VATSTATUTORYDETAILS.LIST><COSTTRACKALLOCATIONS.LIST> </COSTTRACKALLOCATIONS.LIST>" +
@"</LEDGERENTRIES.LIST><LEDGERENTRIES.LIST><OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS></OLDAUDITENTRYIDS.LIST>" +
@"<BASICRATEOFINVOICETAX.LIST TYPE=""Number""><BASICRATEOFINVOICETAX> 12.50</BASICRATEOFINVOICETAX></BASICRATEOFINVOICETAX.LIST><TAXCLASSIFICATIONNAME>Output VAT @ 12.5%</TAXCLASSIFICATIONNAME>" +
"<ROUNDTYPE>Normal Rounding</ROUNDTYPE><LEDGERNAME>12.5% Vat on Sales</LEDGERNAME><GSTCLASS/><ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE><LEDGERFROMITEM>No</LEDGERFROMITEM>" +
"<REMOVEZEROENTRIES>No</REMOVEZEROENTRIES><ISPARTYLEDGER>No</ISPARTYLEDGER><ISLASTDEEMEDPOSITIVE>No</ISLASTDEEMEDPOSITIVE><AMOUNT>" + tax + "</AMOUNT>" +
"<VATASSESSABLEVALUE>" + netAmount + "</VATASSESSABLEVALUE><BANKALLOCATIONS.LIST> </BANKALLOCATIONS.LIST><BILLALLOCATIONS.LIST> </BILLALLOCATIONS.LIST>" +
"<INTERESTCOLLECTION.LIST> </INTERESTCOLLECTION.LIST><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST><ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST>" +
"<AUDITENTRIES.LIST> </AUDITENTRIES.LIST><TAXBILLALLOCATIONS.LIST> </TAXBILLALLOCATIONS.LIST><TAXOBJECTALLOCATIONS.LIST><CATEGORY>Output VAT @ 12.5%</CATEGORY>" +
"<TAXTYPE>VAT</TAXTYPE><TAXNAME>" + VoucherNo + "</TAXNAME><PARTYLEDGER>" + consultant + "</PARTYLEDGER><REFTYPE>New Ref</REFTYPE><ISOPTIONAL>No</ISOPTIONAL>" +
"<ISPANVALID>No</ISPANVALID><ZERORATED>No</ZERORATED><EXEMPTED>No</EXEMPTED><ISSPECIALRATE>No</ISSPECIALRATE><ISDEDUCTNOW>No</ISDEDUCTNOW><ISPANNOTAVAILABLE>No</ISPANNOTAVAILABLE>" +
"<ISSUPPLEMENTARY>No</ISSUPPLEMENTARY><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST><ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST><AUDITENTRIES.LIST> </AUDITENTRIES.LIST>";
decimal trackNetAmtTotal = 0;
int count = uCon.ConsultantOrders.Where(x => !x.ItemName.StartsWith("PPP")).Count();
int q = 0;
foreach (var con in uCon.ConsultantOrders)
{
if (!con.ItemName.StartsWith("PPP"))
{
decimal itemTotalVal = con.ConsultantPrice * con.OrdQty;
decimal itemRate = itemTotalVal / con.OrdQty;
decimal itemNetAmt = (con.ConsultantPrice / Decimal.Parse("112.5") * Decimal.Parse("100"));
itemNetAmt = Math.Round(itemNetAmt, 2);
//decimal itemTax = (itemNetAmt * Decimal.Parse("12.5") / Decimal.Parse("100"));
decimal itemTax = con.ConsultantPrice - itemNetAmt;
itemRate = itemRate - itemTax;
decimal itemsTotalAmt = itemRate * con.OrdQty;
decimal itemTotalTax = itemTax * con.OrdQty;
decimal itemTotalNetAmt = itemNetAmt * con.OrdQty;
//----------------------------------------------------
//INCLUDED TO TRACK NET AMT TOTALS AFFECTING ROUNDING
//-----------------------------------------------------
trackNetAmtTotal += itemTotalNetAmt;
if (q == count - 1) // last item in consultant orders
{
//check if net amount == tracknetamttotal
if (netAmount > trackNetAmtTotal || netAmount < trackNetAmtTotal)
{
checkedAmt = netAmount - trackNetAmtTotal;
itemsTotalAmt = itemsTotalAmt + checkedAmt;
itemTotalTax = itemTotalTax - checkedAmt;
}
}
strData = strData + "<SUBCATEGORYALLOCATION.LIST>" +
"<STOCKITEMNAME>" + con.ItemName + "-" + con.ItemCode + "</STOCKITEMNAME>" +
"<SUBCATEGORY>VAT</SUBCATEGORY>" +
"<DUTYLEDGER>12.5% Vat on Sales</DUTYLEDGER>" +
"<SUBCATZERORATED>No</SUBCATZERORATED>" +
"<SUBCATEXEMPTED>No</SUBCATEXEMPTED>" +
"<SUBCATISSPECIALRATE>No</SUBCATISSPECIALRATE>" +
"<TAXRATE> 12.50</TAXRATE>" +
"<ASSESSABLEAMOUNT>" + itemsTotalAmt + "</ASSESSABLEAMOUNT>" +
"<TAX>" + itemTotalTax + "</TAX>" +
"<BILLEDQTY> " + con.OrdQty + " NO</BILLEDQTY>" +
"</SUBCATEGORYALLOCATION.LIST>";
q++;
}
}
strData = strData + "</TAXOBJECTALLOCATIONS.LIST><TDSEXPENSEALLOCATIONS.LIST> </TDSEXPENSEALLOCATIONS.LIST>" +
"<VATSTATUTORYDETAILS.LIST> </VATSTATUTORYDETAILS.LIST><COSTTRACKALLOCATIONS.LIST> </COSTTRACKALLOCATIONS.LIST>" +
"</LEDGERENTRIES.LIST>";
q = 0;
trackNetAmtTotal = 0;
foreach (var con in uCon.ConsultantOrders)
{
if (!con.ItemName.StartsWith("PPP"))
{
decimal itemTotalVal = con.ConsultantPrice * con.OrdQty;
decimal itemRate = itemTotalVal / con.OrdQty;
decimal itemNetAmt = (con.ConsultantPrice / Decimal.Parse("112.5") * Decimal.Parse("100"));
itemNetAmt = Math.Round(itemNetAmt, 2);
//decimal itemTax = (itemNetAmt * Decimal.Parse("12.5") / Decimal.Parse("100"));
decimal itemTax = con.ConsultantPrice - itemNetAmt;
itemRate = itemRate - itemTax;
decimal itemsTotalAmt = itemRate * con.OrdQty;
decimal itemTotalTax = itemTax * con.OrdQty;
decimal itemTotalNetAmt = itemNetAmt * con.OrdQty;
//----------------------------------------------------
//INCLUDED TO TRACK NET AMT TOTALS AFFECTING ROUNDING
//-----------------------------------------------------
trackNetAmtTotal += itemTotalNetAmt;
if (q == count - 1) // last item in consultant orders
{
//check if net amount == tracknetamttotal
if (netAmount > trackNetAmtTotal || netAmount < trackNetAmtTotal)
{
checkedAmt = netAmount - trackNetAmtTotal;
itemsTotalAmt = itemsTotalAmt + checkedAmt;
itemTotalTax = itemTotalTax - checkedAmt;
itemRate = itemsTotalAmt / con.OrdQty;
}
}
//-----------------------------------------------------------------------
strData = strData + "<ALLINVENTORYENTRIES.LIST><STOCKITEMNAME>" + con.ItemName + "-" + con.ItemCode + "</STOCKITEMNAME>" +
"<ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE><ISLASTDEEMEDPOSITIVE>No</ISLASTDEEMEDPOSITIVE><ISAUTONEGATE>No</ISAUTONEGATE>" +
"<ISCUSTOMSCLEARANCE>No</ISCUSTOMSCLEARANCE><ISTRACKCOMPONENT>No</ISTRACKCOMPONENT><ISTRACKPRODUCTION>No</ISTRACKPRODUCTION>" +
"<ISPRIMARYITEM>No</ISPRIMARYITEM><ISSCRAP>No</ISSCRAP><RATE>" + itemRate + "/NO</RATE><AMOUNT>" + itemsTotalAmt + "</AMOUNT>" + //<AMOUNT>" + itemNetAmt + "</AMOUNT>
"<ACTUALQTY> " + con.OrdQty + " NO</ACTUALQTY><BILLEDQTY> " + con.OrdQty + " NO</BILLEDQTY><BATCHALLOCATIONS.LIST>" +
"<GODOWNNAME>Main Location</GODOWNNAME><BATCHNAME>Primary Batch</BATCHNAME><INDENTNO/><ORDERNO/><TRACKINGNUMBER/>" +
"<DYNAMICCSTISCLEARED>No</DYNAMICCSTISCLEARED><AMOUNT>" + itemsTotalAmt + "</AMOUNT><ACTUALQTY> " + con.OrdQty + " NO</ACTUALQTY>" +
"<BILLEDQTY> " + con.OrdQty + " NO</BILLEDQTY><ADDITIONALDETAILS.LIST> </ADDITIONALDETAILS.LIST><VOUCHERCOMPONENTLIST.LIST> </VOUCHERCOMPONENTLIST.LIST>" +
@"</BATCHALLOCATIONS.LIST><ACCOUNTINGALLOCATIONS.LIST><OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS>" +
"</OLDAUDITENTRYIDS.LIST><TAXCLASSIFICATIONNAME>Output VAT @ 12.5%</TAXCLASSIFICATIONNAME><LEDGERNAME>Sales @12.5%</LEDGERNAME><GSTCLASS/>" +
"<ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE><LEDGERFROMITEM>No</LEDGERFROMITEM><REMOVEZEROENTRIES>No</REMOVEZEROENTRIES><ISPARTYLEDGER>No</ISPARTYLEDGER>" +
"<ISLASTDEEMEDPOSITIVE>No</ISLASTDEEMEDPOSITIVE><AMOUNT>" + itemsTotalAmt + "</AMOUNT><BANKALLOCATIONS.LIST> </BANKALLOCATIONS.LIST>" +
"<BILLALLOCATIONS.LIST> </BILLALLOCATIONS.LIST><INTERESTCOLLECTION.LIST> </INTERESTCOLLECTION.LIST><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST>" +
"<ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST><AUDITENTRIES.LIST> </AUDITENTRIES.LIST><TAXBILLALLOCATIONS.LIST> </TAXBILLALLOCATIONS.LIST>" +
"<TAXOBJECTALLOCATIONS.LIST> </TAXOBJECTALLOCATIONS.LIST><TDSEXPENSEALLOCATIONS.LIST> </TDSEXPENSEALLOCATIONS.LIST><VATSTATUTORYDETAILS.LIST> </VATSTATUTORYDETAILS.LIST>" +
"<COSTTRACKALLOCATIONS.LIST> </COSTTRACKALLOCATIONS.LIST></ACCOUNTINGALLOCATIONS.LIST><TAXOBJECTALLOCATIONS.LIST> </TAXOBJECTALLOCATIONS.LIST>" +
"<EXCISEALLOCATIONS.LIST> </EXCISEALLOCATIONS.LIST><EXPENSEALLOCATIONS.LIST> </EXPENSEALLOCATIONS.LIST></ALLINVENTORYENTRIES.LIST>";
q++;
itemNetAmt = TruncateDecimal(itemNetAmt, 2);
decimal chkItemTax = con.ConsultantPrice - (itemNetAmt + itemTax);
if (chkItemTax <= 1 || chkItemTax >= -1)
{
itemTax = itemTax + chkItemTax;
}
PostedTransaction trans;
using (var db = new eXmlContext())
{
trans = db.Set<PostedTransaction>()
.Where(x => x.ItemCode == con.ItemCode)
.Where(x => x.OrderId == con.OrderId)
.Where(x => x.ConsultantCode == con.Consultant.ConsultantId)
.FirstOrDefault();
if (trans == null)
{
trans = new PostedTransaction
{
Company = model.Company,
UnitName = unit.Unit,
AssemblyName = assembly,
ConsultantName = uCon.Consultant,
ConsultantCode = uCon.ConsultantId,
PostDate = DateTime.Now,
Year = year,
Week = week,
InvoiceNo = VoucherNo,
GrossAmount = con.Amount,
ConsultantPrice = con.ConsultantPrice,
NetAmount = itemNetAmt,
VatAmount = itemTax ,
ItemCode = con.ItemCode,
ItemName = con.ItemName,
OrderId = con.OrderId,
OrderQty = con.OrdQty,
Status = con.Status,
PayStatus = enPaymentStatus.Pending,
InventoryStatus = enInventoryStatus.Pending,
PaymentAmount = 0,
PostType = enPostType.Invoice_12_5_WithAddress
};
db.PostedTransaction.Add(trans);
}
else
{
trans.ItemCode = con.ItemCode;
trans.GrossAmount = con.Amount;
trans.ConsultantPrice = con.ConsultantPrice;
trans.NetAmount = itemNetAmt;
trans.VatAmount = itemTax + (con.ConsultantPrice - (itemNetAmt + itemTax));
trans.ItemCode = con.ItemCode;
trans.ItemName = con.ItemName;
trans.OrderId = con.OrderId;
trans.OrderQty = con.OrdQty;
trans.Status = con.Status;
db.Entry(trans).State = System.Data.Entity.EntityState.Modified;
}
db.SaveChanges();
}
}
}
#region VAT_5%
if (uCon.ConsultantOrders.Count(x => x.ItemName.StartsWith("PPP")) > 0) // 5 % VAT Items
{
consultantAmount = uCon.ConsultantOrders.Where(x => x.ItemName.StartsWith("PPP")).Sum(x => x.ConsultantPrice * x.OrdQty);
netAmount = ((consultantAmount / Decimal.Parse("105") * Decimal.Parse("100")));
//tax = ((netAmount * Decimal.Parse("5")) / Decimal.Parse("100"));
netAmount = Math.Round(netAmount, 2);
tax = consultantAmount - netAmount;
//tax = TruncateDecimal(tax, 2);
chkAmt = (consultantAmount - (netAmount + tax));
//if (chkAmt <= 1 || chkAmt >= -1)
//{
// tax = tax + chkAmt;
//}
strData = strData + @"<LEDGERENTRIES.LIST><OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS></OLDAUDITENTRYIDS.LIST>" +
@"<BASICRATEOFINVOICETAX.LIST TYPE=""Number""><BASICRATEOFINVOICETAX> 5</BASICRATEOFINVOICETAX></BASICRATEOFINVOICETAX.LIST><TAXCLASSIFICATIONNAME>Output VAT @ 5%</TAXCLASSIFICATIONNAME>" +
"<ROUNDTYPE>Normal Rounding</ROUNDTYPE><LEDGERNAME>Output Vat @5%</LEDGERNAME><GSTCLASS/><ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE><LEDGERFROMITEM>No</LEDGERFROMITEM>" +
"<REMOVEZEROENTRIES>No</REMOVEZEROENTRIES><ISPARTYLEDGER>No</ISPARTYLEDGER><ISLASTDEEMEDPOSITIVE>No</ISLASTDEEMEDPOSITIVE><AMOUNT>" + tax + "</AMOUNT>" +
"<VATASSESSABLEVALUE>" + netAmount + "</VATASSESSABLEVALUE><BANKALLOCATIONS.LIST> </BANKALLOCATIONS.LIST><BILLALLOCATIONS.LIST> </BILLALLOCATIONS.LIST>" +
"<INTERESTCOLLECTION.LIST> </INTERESTCOLLECTION.LIST><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST><ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST>" +
"<AUDITENTRIES.LIST> </AUDITENTRIES.LIST><TAXBILLALLOCATIONS.LIST> </TAXBILLALLOCATIONS.LIST><TAXOBJECTALLOCATIONS.LIST><CATEGORY>Output VAT @ 5%</CATEGORY>" +
"<TAXTYPE>VAT</TAXTYPE><TAXNAME>" + VoucherNo + "</TAXNAME><PARTYLEDGER>" + consultant + "</PARTYLEDGER><REFTYPE>New Ref</REFTYPE><ISOPTIONAL>No</ISOPTIONAL>" +
"<ISPANVALID>No</ISPANVALID><ZERORATED>No</ZERORATED><EXEMPTED>No</EXEMPTED><ISSPECIALRATE>No</ISSPECIALRATE><ISDEDUCTNOW>No</ISDEDUCTNOW><ISPANNOTAVAILABLE>No</ISPANNOTAVAILABLE>" +
"<ISSUPPLEMENTARY>No</ISSUPPLEMENTARY><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST><ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST><AUDITENTRIES.LIST> </AUDITENTRIES.LIST>";
var PercentItems = uCon.ConsultantOrders.Where(x => x.ItemName.StartsWith("PPP")).ToList();
trackNetAmtTotal = 0;
count = PercentItems.Count();
q = 0;
foreach (var con in PercentItems)
{
decimal itemTotalVal = con.ConsultantPrice * con.OrdQty;
decimal itemRate = itemTotalVal / con.OrdQty;
decimal itemNetAmt = (con.ConsultantPrice / Decimal.Parse("105") * Decimal.Parse("100"));
itemNetAmt = Math.Round(itemNetAmt, 2);
//decimal itemTax = (itemNetAmt * Decimal.Parse("5") / Decimal.Parse("100"));
decimal itemTax = con.ConsultantPrice - itemNetAmt;
itemRate = itemRate - itemTax;
decimal itemsTotalAmt = itemRate * con.OrdQty;
decimal itemTotalTax = itemTax * con.OrdQty;
decimal itemTotalNetAmt = itemNetAmt * con.OrdQty;
//----------------------------------------------------
//INCLUDED TO TRACK NET AMT TOTALS AFFECTING ROUNDING
//-----------------------------------------------------
trackNetAmtTotal += itemTotalNetAmt;
if (q == count - 1) // last item in consultant orders
{
//check if net amount == tracknetamttotal
if (netAmount > trackNetAmtTotal || netAmount < trackNetAmtTotal)
{
checkedAmt = netAmount - trackNetAmtTotal;
itemsTotalAmt = itemsTotalAmt + checkedAmt;
itemTotalTax = itemTotalTax - checkedAmt;
}
}
strData = strData + "<SUBCATEGORYALLOCATION.LIST>" +
"<STOCKITEMNAME>" + con.ItemName + "-" + con.ItemCode + "</STOCKITEMNAME>" +
"<SUBCATEGORY>VAT</SUBCATEGORY>" +
"<DUTYLEDGER>12.5% Vat on Sales</DUTYLEDGER>" +
"<SUBCATZERORATED>No</SUBCATZERORATED>" +
"<SUBCATEXEMPTED>No</SUBCATEXEMPTED>" +
"<SUBCATISSPECIALRATE>No</SUBCATISSPECIALRATE>" +
"<TAXRATE> 12.50</TAXRATE>" +
"<ASSESSABLEAMOUNT>" + itemsTotalAmt + "</ASSESSABLEAMOUNT>" +
"<TAX>" + itemTotalTax + "</TAX>" +
"<BILLEDQTY> " + con.OrdQty + " NO</BILLEDQTY>" +
"</SUBCATEGORYALLOCATION.LIST>";
q++;
}
strData = strData + "</TAXOBJECTALLOCATIONS.LIST><TDSEXPENSEALLOCATIONS.LIST> </TDSEXPENSEALLOCATIONS.LIST>" +
"<VATSTATUTORYDETAILS.LIST> </VATSTATUTORYDETAILS.LIST><COSTTRACKALLOCATIONS.LIST> </COSTTRACKALLOCATIONS.LIST>" +
"</LEDGERENTRIES.LIST>";
q = 0;
trackNetAmtTotal = 0;
foreach (var con in PercentItems)
{
decimal itemTotalVal = con.ConsultantPrice * con.OrdQty;
decimal itemRate = itemTotalVal / con.OrdQty;
decimal itemNetAmt = (con.ConsultantPrice / Decimal.Parse("105") * Decimal.Parse("100"));
itemNetAmt = Math.Round(itemNetAmt, 2);
//decimal itemTax = (itemNetAmt * Decimal.Parse("5") / Decimal.Parse("100"));
decimal itemTax = con.ConsultantPrice - itemNetAmt;
itemRate = itemRate - itemTax;
decimal itemsTotalAmt = itemRate * con.OrdQty;
decimal itemTotalTax = itemTax * con.OrdQty;
decimal itemTotalNetAmt = itemNetAmt * con.OrdQty;
//itemNetAmt = Math.Round(itemNetAmt, 0);
//----------------------------------------------------
//INCLUDED TO TRACK NET AMT TOTALS AFFECTING ROUNDING
//-----------------------------------------------------
trackNetAmtTotal += itemTotalNetAmt;
if (q == count - 1) // last item in consultant orders
{
//check if net amount == tracknetamttotal
if (netAmount > trackNetAmtTotal || netAmount < trackNetAmtTotal)
{
checkedAmt = netAmount - trackNetAmtTotal;
itemsTotalAmt = itemsTotalAmt + checkedAmt;
itemTotalTax = itemTotalTax - checkedAmt;
itemRate = itemsTotalAmt / con.OrdQty;
}
}
strData = strData + "<ALLINVENTORYENTRIES.LIST><STOCKITEMNAME>" + con.ItemName + "-" + con.ItemCode + "</STOCKITEMNAME>" +
"<ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE><ISLASTDEEMEDPOSITIVE>No</ISLASTDEEMEDPOSITIVE><ISAUTONEGATE>No</ISAUTONEGATE>" +
"<ISCUSTOMSCLEARANCE>No</ISCUSTOMSCLEARANCE><ISTRACKCOMPONENT>No</ISTRACKCOMPONENT><ISTRACKPRODUCTION>No</ISTRACKPRODUCTION>" +
"<ISPRIMARYITEM>No</ISPRIMARYITEM><ISSCRAP>No</ISSCRAP><RATE>" + itemRate + "/NO</RATE><AMOUNT>" + itemsTotalAmt + "</AMOUNT>" + //<AMOUNT>" + itemNetAmt + "</AMOUNT>
"<ACTUALQTY> " + con.OrdQty + " NO</ACTUALQTY><BILLEDQTY> " + con.OrdQty + " NO</BILLEDQTY><BATCHALLOCATIONS.LIST>" +
"<GODOWNNAME>Main Location</GODOWNNAME><BATCHNAME>Primary Batch</BATCHNAME><INDENTNO/><ORDERNO/><TRACKINGNUMBER/>" +
"<DYNAMICCSTISCLEARED>No</DYNAMICCSTISCLEARED><AMOUNT>" + itemsTotalAmt + "</AMOUNT><ACTUALQTY> " + con.OrdQty + " NO</ACTUALQTY>" +
"<BILLEDQTY> " + con.OrdQty + " NO</BILLEDQTY><ADDITIONALDETAILS.LIST> </ADDITIONALDETAILS.LIST><VOUCHERCOMPONENTLIST.LIST> </VOUCHERCOMPONENTLIST.LIST>" +
@"</BATCHALLOCATIONS.LIST><ACCOUNTINGALLOCATIONS.LIST><OLDAUDITENTRYIDS.LIST TYPE=""Number""><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS>" +
"</OLDAUDITENTRYIDS.LIST><TAXCLASSIFICATIONNAME>Output VAT @ 5%</TAXCLASSIFICATIONNAME><LEDGERNAME>Sales @5%</LEDGERNAME><GSTCLASS/>" +
"<ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE><LEDGERFROMITEM>No</LEDGERFROMITEM><REMOVEZEROENTRIES>No</REMOVEZEROENTRIES><ISPARTYLEDGER>No</ISPARTYLEDGER>" +
"<ISLASTDEEMEDPOSITIVE>No</ISLASTDEEMEDPOSITIVE><AMOUNT>" + itemsTotalAmt + "</AMOUNT><BANKALLOCATIONS.LIST> </BANKALLOCATIONS.LIST>" +
"<BILLALLOCATIONS.LIST> </BILLALLOCATIONS.LIST><INTERESTCOLLECTION.LIST> </INTERESTCOLLECTION.LIST><OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST>" +
"<ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST><AUDITENTRIES.LIST> </AUDITENTRIES.LIST><TAXBILLALLOCATIONS.LIST> </TAXBILLALLOCATIONS.LIST>" +
"<TAXOBJECTALLOCATIONS.LIST> </TAXOBJECTALLOCATIONS.LIST><TDSEXPENSEALLOCATIONS.LIST> </TDSEXPENSEALLOCATIONS.LIST><VATSTATUTORYDETAILS.LIST> </VATSTATUTORYDETAILS.LIST>" +
"<COSTTRACKALLOCATIONS.LIST> </COSTTRACKALLOCATIONS.LIST></ACCOUNTINGALLOCATIONS.LIST><TAXOBJECTALLOCATIONS.LIST> </TAXOBJECTALLOCATIONS.LIST>" +
"<EXCISEALLOCATIONS.LIST> </EXCISEALLOCATIONS.LIST><EXPENSEALLOCATIONS.LIST> </EXPENSEALLOCATIONS.LIST></ALLINVENTORYENTRIES.LIST>";
q++;
itemNetAmt = TruncateDecimal(itemNetAmt, 2);
decimal chkItemTax = con.ConsultantPrice - (itemNetAmt + itemTax);
if (chkItemTax <= 1 || chkItemTax >= -1)
{
itemTax = itemTax + chkItemTax;
}
PostedTransaction trans;
using (var db = new eXmlContext())
{
trans = db.Set<PostedTransaction>()
//.Where(x => x.Company == model.Company)
// .Where(x => x.AssemblyName == assembly)
// .Where(x => x.UnitName == unit.Unit)
// .Where(x => x.Year == year)
// .Where(x => x.Week == week)
.Where(x => x.ItemCode == con.ItemCode)
.Where(x => x.OrderId == con.OrderId)
.Where(x => x.ConsultantCode == con.Consultant.ConsultantId)
.FirstOrDefault();
if (trans == null)
{
trans = new PostedTransaction
{
Company = model.Company,
UnitName = unit.Unit,
AssemblyName = assembly,
ConsultantName = uCon.Consultant,
ConsultantCode = uCon.ConsultantId,
PostDate = DateTime.Now,
Year = year,
Week = week,
InvoiceNo = VoucherNo,
GrossAmount = con.Amount,
ConsultantPrice = con.ConsultantPrice,
NetAmount = itemNetAmt,
VatAmount = itemTax,
ItemCode = con.ItemCode,
ItemName = con.ItemName,
OrderId = con.OrderId,
OrderQty = con.OrdQty,
Status = con.Status,
PayStatus = enPaymentStatus.Pending,
InventoryStatus = enInventoryStatus.Pending,
PaymentAmount = 0,
PostType = enPostType.Invoice_12_5_WithAddress
};
db.PostedTransaction.Add(trans);
}
else
{
trans.ItemCode = con.ItemCode;
trans.GrossAmount = con.Amount;
trans.NetAmount = itemNetAmt;
trans.VatAmount = itemTax;
trans.ItemCode = con.ItemCode;
trans.ItemName = con.ItemName;
trans.OrderId = con.OrderId;
trans.OrderQty = con.OrdQty;
trans.Status = con.Status;
db.Entry(trans).State = System.Data.Entity.EntityState.Modified;
}
db.SaveChanges();
}
}
}
#endregion VAT_5%
strData = strData + "<ATTDRECORDS.LIST> </ATTDRECORDS.LIST>" +
"</VOUCHER></TALLYMESSAGE>";
}
}
strData = strData + "</REQUESTDATA></IMPORTDATA></BODY></ENVELOPE>";
//strData = strData + @"<TALLYMESSAGE xmlns:UDF=""TallyUDF""><COMPANY><REMOTECMPINFO.LIST MERGE=""Yes""><NAME>1992944e-597f-4d18-bdde-35856a4fbddc</NAME>" +
//"<REMOTECMPNAME>" + model.Company + " </REMOTECMPNAME><REMOTECMPSTATE>Maharashtra</REMOTECMPSTATE></REMOTECMPINFO.LIST>" +
// "</COMPANY></TALLYMESSAGE></REQUESTDATA></IMPORTDATA></BODY></ENVELOPE>";
XmlDocument docum = new XmlDocument();
docum.LoadXml(strData);
if (!Directory.Exists(savePath))
{
Directory.CreateDirectory(savePath);
}
docum.Save(savePath + "//payment.xml");
}
//}
}