public ActionResult UpdateUser(UserModel model)
{
if (ModelState.IsValid)
{
try
{
using (var db = new eXmlContext())
{
var user = db.Set<User>()
.SingleOrDefault(x => x.UserId == model.UserId);
user.Email = model.Email;
user.Password = model.Password;
user.IsLicensed = model.IsLicensed;
user.ExpiryDate = model.ExpiryDate;
db.Entry(user).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
}
catch (Exception e)
{
ViewData["EditError"] = e.Message;
}
}
else
{
ViewData["EditError"] = "Please correct all errors";
ViewData["User"] = model;
}
return PartialView("_GridListUsers", AdminServiceProvider.Users());
}
public static User Login(LoginModel model)
{
//bool IsSuccessful = false;
User user;
using (var db = new eXmlContext())
{
user = db.Set <User>().Where(x => x.Email == model.Email).FirstOrDefault();
if (user != null)
{
string strPwd = "";
string cryptoKey = ConfigurationManager.AppSettings["CryptoKey"].ToString();
Crypto.Key = cryptoKey;
Crypto.EncryptionAlgorithm = Crypto.Algorithm.DES;
Crypto.Content = user.Password;
if (Crypto.DecryptString())
{
strPwd = Crypto.Content;
}
if (strPwd == model.Password)
{
return(user);
}
else
{
user = null;
}
}
}
return(user);
}
public static IEnumerable <PostedTransaction> EditableTransactions(string assembly, string unit, DateTime?datefrom, DateTime?dateto)
{
IEnumerable <PostedTransaction> lstTrans = new List <PostedTransaction>();
using (var db = new eXmlContext())
{
lstTrans = db.Set <PostedTransaction>()
.Where(x => DbFunctions.TruncateTime(x.PostDate) >= DbFunctions.TruncateTime(datefrom) &&
DbFunctions.TruncateTime(x.PostDate) <= DbFunctions.TruncateTime(dateto))
.ToList();
if (!string.IsNullOrWhiteSpace(assembly))
{
if (assembly != "ALL")
{
lstTrans = lstTrans.Where(x => x.AssemblyName == assembly).ToList();
}
}
if (!string.IsNullOrWhiteSpace(unit))
{
if (unit != "ALL")
{
lstTrans = lstTrans.Where(x => x.UnitName == unit).ToList();
}
}
}
return(lstTrans);
}
public static List<ListPaymentReportModel> PaymentReportsTransactions(string assembly, string unit,
string consultant, string status, string type, DateTime? datefrom, DateTime? dateto, DateTime? datePfrom, DateTime? datePto)
{
var listPReprts = new List<ListPaymentReportModel>();
using (var db = new eXmlContext())
{
listPReprts = db.Set<ListPaymentReportModel>()
.Where(x => DbFunctions.TruncateTime(x.PostDate) >= DbFunctions.TruncateTime(datefrom)
&& DbFunctions.TruncateTime(x.PostDate) <= DbFunctions.TruncateTime(dateto))
.Where(x => DbFunctions.TruncateTime(x.PaymentDate) >= DbFunctions.TruncateTime(datePfrom)
&& DbFunctions.TruncateTime(x.PaymentDate) <= DbFunctions.TruncateTime(datePto))
.ToList();
if (!string.IsNullOrWhiteSpace(assembly))
if (assembly != "ALL") listPReprts = listPReprts.Where(x => x.AssemblyName == assembly).ToList();
if (!string.IsNullOrWhiteSpace(unit))
if (unit != "ALL") listPReprts = listPReprts.Where(x => x.UnitName == unit).ToList();
if (!string.IsNullOrWhiteSpace(consultant))
if (consultant != "ALL") listPReprts = listPReprts.Where(x => x.ConsultantName == consultant).ToList();
if (!string.IsNullOrWhiteSpace(status))
if (status != "ALL") listPReprts = listPReprts.Where(x => x.Status == status).ToList();
if (!string.IsNullOrWhiteSpace(type))
if (type != "ALL") listPReprts = listPReprts.Where(x => x.Type == type).ToList();
}
return listPReprts;
}
public static List <SelectListItem> LoadAssemblyUnits(string assembly)
{
List <SelectListItem> listAUnits = new List <SelectListItem>();
IList <string> units;
using (var db = new eXmlContext())
{
units = db.Set <PostedTransaction>()
.Where(x => x.AssemblyName == assembly)
.Select(x => x.UnitName)
.Distinct()
.ToList();
}
int i = 0;
foreach (var unit in units)
{
i++;
SelectListItem listAssUnit = new SelectListItem
{
Value = unit.ToString(),
Text = unit.ToString(),
Selected = (i == 1)
};
listAUnits.Add(listAssUnit);
}
return(listAUnits);
}
public static List <SelectListItem> LoadItems()
{
List <SelectListItem> listItems = new List <SelectListItem>();
List <string> items;
using (var db = new eXmlContext())
{
items = db.Set <PostedTransaction>()
.Select(x => x.ItemName)
.Distinct()
.ToList();
}
foreach (var itm in items)
{
SelectListItem listAItem = new SelectListItem
{
Value = itm.ToString(),
Text = itm.ToString(),
Selected = false
};
listItems.Add(listAItem);
}
return(listItems);
}
public static List <SelectListItem> LoadUnitConsultants(string unit)
{
List <SelectListItem> listUConsults = new List <SelectListItem>();
IList <string> consultants;
using (var db = new eXmlContext())
{
consultants = db.Set <PostedTransaction>()
.Where(x => x.UnitName == unit)
.Select(x => x.ConsultantName)
.Distinct()
.ToList();
}
int i = 0;
foreach (var consultant in consultants)
{
i++;
SelectListItem listConsltnt = new SelectListItem
{
Value = consultant.ToString(),
Text = consultant.ToString(),
Selected = (i == 1)
};
listUConsults.Add(listConsltnt);
}
return(listUConsults);
}
public static List <SelectListItem> LoadAssemblies()
{
List <SelectListItem> listAssemblies = new List <SelectListItem>();
List <string> assemblies;
using (var db = new eXmlContext())
{
assemblies = db.Set <PostedTransaction>()
.Select(x => x.AssemblyName)
.Distinct()
.ToList();
}
foreach (var asmbly in assemblies)
{
SelectListItem listAItem = new SelectListItem
{
Value = asmbly.ToString(),
Text = asmbly.ToString(),
Selected = false
};
listAssemblies.Add(listAItem);
}
return(listAssemblies);
}
public static List <ListInvoiceTransModel> InvoiceEditableTransactions(string assembly, string unit, DateTime?datefrom, DateTime?dateto)
{
var listInvTrans = new List <ListInvoiceTransModel>();
using (var db = new eXmlContext())
{
listInvTrans = db.Set <ListInvoiceTransModel>()
.Where(x => DbFunctions.TruncateTime(x.InvoiceDate) >= DbFunctions.TruncateTime(datefrom) &&
DbFunctions.TruncateTime(x.InvoiceDate) <= DbFunctions.TruncateTime(dateto))
.Distinct()
.ToList();
if (!string.IsNullOrWhiteSpace(assembly))
{
if (assembly != "ALL")
{
listInvTrans = listInvTrans.Where(x => x.AssemblyName == assembly).ToList();
}
}
if (!string.IsNullOrWhiteSpace(unit))
{
if (unit != "ALL")
{
listInvTrans = listInvTrans.Where(x => x.UnitName == unit).ToList();
}
}
}
return(listInvTrans);
}
public static User GetUser(int userId)
{
User user;
using (var db = new eXmlContext())
{
user = db.Set <User>
().FirstOrDefault(u => u.UserId == userId);
}
return(user);
}
public static IEnumerable <Role> ListRoles()
{
IEnumerable <Role> roles;
using (var db = new eXmlContext())
{
roles = db.Set <Role>()
.ToList();
}
return(roles);
}
public static List <ListInventoryReportModel> InventoryReportsTransactions(string assembly, string unit,
string consultant, string status, string item, DateTime?datefrom, DateTime?dateto)
{
var listInvReprts = new List <ListInventoryReportModel>();
using (var db = new eXmlContext())
{
listInvReprts = db.Set <ListInventoryReportModel>()
.Where(x => DbFunctions.TruncateTime(x.PostDate) >= DbFunctions.TruncateTime(datefrom) &&
DbFunctions.TruncateTime(x.PostDate) <= DbFunctions.TruncateTime(dateto))
.ToList();
if (!string.IsNullOrWhiteSpace(assembly))
{
if (assembly != "ALL")
{
listInvReprts = listInvReprts.Where(x => x.AssemblyName == assembly).ToList();
}
}
if (!string.IsNullOrWhiteSpace(unit))
{
if (unit != "ALL")
{
listInvReprts = listInvReprts.Where(x => x.UnitName == unit).ToList();
}
}
if (!string.IsNullOrWhiteSpace(consultant))
{
if (consultant != "ALL")
{
listInvReprts = listInvReprts.Where(x => x.ConsultantName == consultant).ToList();
}
}
if (!string.IsNullOrWhiteSpace(status))
{
if (status != "ALL")
{
listInvReprts = listInvReprts.Where(x => x.Status == status).ToList();
}
}
if (!string.IsNullOrWhiteSpace(item))
{
if (item != "ALL")
{
listInvReprts = listInvReprts.Where(x => x.ItemName == item).ToList();
}
}
}
return(listInvReprts);
}
public static List <PermissionModel> Permissions()
{
var perms = new List <PermissionModel>();
using (var db = new eXmlContext())
{
perms = db.Set <Permission>()
.Select(x => new PermissionModel
{
PermissionId = x.PermissionId,
PermissionName = x.PermissionName
}).ToList();
}
return(perms);
}
public static IEnumerable<PostedTransaction> EditableTransactions(string assembly, string unit,DateTime? datefrom, DateTime? dateto)
{
IEnumerable<PostedTransaction> lstTrans = new List<PostedTransaction>();
using (var db = new eXmlContext())
{
lstTrans = db.Set<PostedTransaction>()
.Where(x => DbFunctions.TruncateTime (x.PostDate) >= DbFunctions.TruncateTime(datefrom)
&& DbFunctions.TruncateTime( x.PostDate) <= DbFunctions.TruncateTime( dateto) )
.ToList();
if (!string.IsNullOrWhiteSpace(assembly))
if (assembly != "ALL") lstTrans = lstTrans.Where(x => x.AssemblyName == assembly).ToList();
if (!string.IsNullOrWhiteSpace(unit))
if (unit != "ALL") lstTrans = lstTrans.Where(x => x.UnitName == unit).ToList();
}
return lstTrans;
}
public static List <RoleModel> Roles()
{
var roles = new List <RoleModel>();
using (var db = new eXmlContext())
{
roles = db.Set <Role>()
.Select(x => new RoleModel
{
RoleId = x.RoleId,
RoleType = x.RoleType,
RoleName = x.RoleName
}).ToList();
}
return(roles);
}
public static List <UserModel> Users()
{
var users = new List <UserModel>();
using (var db = new eXmlContext())
{
users = db.Set <User>()
.Select(x => new UserModel
{
UserId = x.UserId,
Email = x.Email,
Password = x.Password,
IsLicensed = x.IsLicensed,
ExpiryDate = x.ExpiryDate
}).ToList();
}
return(users);
}
public ActionResult AddUser(UserModel model)
{
if (ModelState.IsValid)
{
string cryptoKey = ConfigurationManager.AppSettings["CryptoKey"].ToString();
Crypto.Key = cryptoKey;
Crypto.EncryptionAlgorithm = Crypto.Algorithm.DES;
try
{
string encryptPwd ="" ;
if (Crypto.EncryptString(model.Password))
{
encryptPwd = Crypto.Content;
}
User u = new User
{
Email = model.Email,
Password = encryptPwd,
IsLicensed = model.IsLicensed,
ExpiryDate = model.ExpiryDate
};
using (var db = new eXmlContext())
{
db.Users.Add(u);
db.SaveChanges();
}
}
catch (Exception e)
{
ViewData["EditError"] = e.Message;
}
}
else
{
ViewData["EditError"] = "Please correct all errors";
ViewData["User"] = model;
}
return PartialView("_GridListUsers",AdminServiceProvider.Users());
}
public static List<ListInvoiceTransModel> InvoiceEditableTransactions(string assembly, string unit, DateTime? datefrom, DateTime? dateto)
{
var listInvTrans = new List<ListInvoiceTransModel>();
using (var db = new eXmlContext())
{
listInvTrans = db.Set<ListInvoiceTransModel>()
.Where(x => DbFunctions.TruncateTime (x.InvoiceDate) >= DbFunctions.TruncateTime (datefrom )
&& DbFunctions.TruncateTime(x.InvoiceDate) <= DbFunctions.TruncateTime(dateto))
.Distinct()
.ToList();
if (!string.IsNullOrWhiteSpace(assembly))
if (assembly != "ALL") listInvTrans = listInvTrans.Where(x => x.AssemblyName == assembly).ToList();
if (!string.IsNullOrWhiteSpace(unit))
if (unit != "ALL") listInvTrans = listInvTrans.Where(x => x.UnitName == unit).ToList();
}
return listInvTrans;
}
public ActionResult PaymentBulk(string selectedIDsHF)
{
char[] array = new Char[1];
array[0] = Char.Parse(",");
string[] orderIds = selectedIDsHF.Split(array);
int response = 0;
DateTime invDate = DateTime.Now.AddMonths(-1);
DateTime invDateTo = DateTime.Now;
foreach (var orderId in orderIds)
{
using (var dbContext = new eXmlContext())
{
int id = int.Parse(orderId);
IEnumerable<PostedTransaction> pTrans = dbContext.Set<PostedTransaction>()
.Where(x => x.OrderId == id)
.ToList();
if (pTrans != null)
{
decimal totalAmtPayable;
foreach (var trans in pTrans)
{
totalAmtPayable = (trans.ConsultantPrice * trans.OrderQty) - trans.PaymentAmount;
trans.PaymentAmount = totalAmtPayable + trans.PaymentAmount;
trans.PaymentDate = DateTime.Now;
trans.PaymentType = enPaymentType.Cash;
trans.PayStatus = enPaymentStatus.Received;
dbContext.Entry(trans).State = System.Data.Entity.EntityState.Modified;
}
dbContext.SaveChanges();
response = 1;
}
}
}
ViewData["PayType"] = EnumHelper.ToList(typeof(enPaymentType));
ViewData["Assemblies"] = TransactionProvider.LoadAssemblies();
//return PartialView("_GridListInvoices", TransactionProvider.InvoiceEditableTransactions(null, null, null, null));
return Json(response);
}
public string UpdateUserMainMenuXml(int userId)
{
bool isAdmin = false;
string userFile;
using (var db = new eXmlContext())
{
User user = db.Set<User>().Where(x => x.UserId == userId).First();
foreach (var role in user.Roles)
{
//check if user has admin role
if (role.RoleType == enRoleType.Admin)
{
isAdmin = true;
}
}
string fileName = Path.Combine(Server.MapPath("~/App_Data"), "main.xml");
XmlTextReader reader = new XmlTextReader(fileName);
XmlDocument doc = new XmlDocument();
doc.Load(reader);
reader.Close();
XmlNode adminNode;
XmlElement root = doc.DocumentElement;
userFile = "newmenu.xml";
string userFilePath = Path.Combine(Server.MapPath("~/App_Data"), userFile);
if (isAdmin == false) // remove admin child node
{
adminNode = root.SelectSingleNode("/mainmenu/item[@Role='Admin']");
root.RemoveChild(adminNode);
doc.Save(userFilePath);
}
else doc.Save(userFilePath);
}
return userFile;
}
public ActionResult SetRole(RolesCheckBoxListEditModel model)
{
using (var db = new eXmlContext())
{
User user = db.Users.Find(model.Id);
user.Roles.UpdateRoleCollectionFromModel(db.Roles, model.RoleIds);
db.SaveChanges();
}
ViewData["Message"] = "Roles for this user have been set successfully!";
return RedirectToAction("SetRole", new { userId = model.Id });
}
public ActionResult SetRole(int userId)
{
var model = new RolesCheckBoxListEditModel();
using (var db = new eXmlContext())
{
User user;
user = db.Set<User>().FirstOrDefault(x => x.UserId == userId);
var allRoles = db.Set<Role>().ToList();
if (user != null)
{
IEnumerable<SelectListItem> listRoles = user.Roles.ToCheckBoxRolesListSource(allRoles);
for (var i = 0; i < allRoles.Count(); i++)
{
listRoles.ElementAt(i).Text = allRoles.ElementAt<Role>(i).RoleName;
}
model.Roles = listRoles;
model.Id = user.UserId;
}
}
return View(model);
}
public ActionResult UpdateRole(RoleModel model)
{
if (ModelState.IsValid)
{
try
{
using (var db = new eXmlContext())
{
var role = db.Set<Role>()
.SingleOrDefault(x => x.RoleId == model.RoleId);
role.RoleName = model.RoleName;
role.RoleType = model.RoleType;
db.Entry(role).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
}
catch (Exception e)
{
ViewData["EditError"] = e.Message;
}
}
else
{
ViewData["EditError"] = "Please correct all errors";
ViewData["Role"] = model;
}
return PartialView("_GridListRoles", AdminServiceProvider.Roles());
}
public ActionResult AddRole(RoleModel model)
{
if (ModelState.IsValid)
{
try
{
Role r = new Role
{
RoleName = model.RoleName,
RoleType = model.RoleType
};
using (var db = new eXmlContext())
{
db.Roles.Add(r);
db.SaveChanges();
}
}
catch (Exception e)
{
ViewData["EditError"] = e.Message;
}
}
else
{
ViewData["EditError"] = "Please correct all errors";
ViewData["Role"] = model;
}
return PartialView("_GridListRoles", AdminServiceProvider.Roles());
}
public ActionResult PaymentUpdate(ListInvoiceTransModel trans)
{
string assembly = "";
string unit = "";
DateTime invDate = DateTime.Now;
if (trans.Id > 0 && trans.PaymentType != null && trans.PaymentDate != null )
{
try
{
int Id = trans.Id;
using (var db = new eXmlContext())
{
IEnumerable<PostedTransaction> pTrans = db.Set<PostedTransaction>()
.Where(x => x.OrderId == Id)
.ToList();
if (pTrans != null)
{
assembly = pTrans.First().AssemblyName;
unit = pTrans.First().UnitName;
invDate = pTrans.First().PostDate;
//invDate = pTrans.First().InvoiceDate;
//get total net amount payable as per order id
decimal netAmtPayable = 0;
decimal amtPaid = 0;
foreach (var tr in pTrans)
{
netAmtPayable = netAmtPayable + tr.NetAmount + tr.VatAmount;
amtPaid = amtPaid + tr.PaymentAmount;
}
netAmtPayable = netAmtPayable - amtPaid;
if (trans.PaymentAmount <= netAmtPayable)
{
decimal balAfterDeduct = trans.PaymentAmount;
decimal totalAmtPayable;
foreach (var transaction in pTrans)
{
totalAmtPayable = (transaction.ConsultantPrice * transaction.OrderQty) - transaction.PaymentAmount;
transaction.PaymentDate = trans.PaymentDate;
transaction.PaymentType = trans.PaymentType;
transaction.BankName = trans.BankName;
transaction.ChequeNo = trans.ChequeNo;
if (balAfterDeduct >= totalAmtPayable && totalAmtPayable > 0)
{
transaction.PaymentAmount = transaction.PaymentAmount + totalAmtPayable;
transaction.PayStatus = enPaymentStatus.Received;
balAfterDeduct = balAfterDeduct - totalAmtPayable;
}
else if (balAfterDeduct > 0 && balAfterDeduct < totalAmtPayable)
{
transaction.PaymentAmount = transaction.PaymentAmount + balAfterDeduct;
transaction.PayStatus = enPaymentStatus.Partial;
balAfterDeduct = 0 ;
}
else
{
transaction.PaymentAmount = transaction.PaymentAmount;
transaction.PayStatus = enPaymentStatus.Pending;
}
db.Entry(transaction).State = System.Data.Entity.EntityState.Modified;
}
db.SaveChanges();
}
else
{
ViewData["EditError"] = "Payment amount is greater than net amount payable!";
}
}
else
{
ViewData["EditError"] = "Posted transaction object is null. Unable to obtain transaction from Db";
}
}
}
catch (Exception ex)
{
ViewData["EditError"] = ex.Message;
}
}
else
{
ViewData["PostedTrans"] = trans;
ViewData["EditError"] = "Payment type and payment date cannot contain null values! Enter values and update!";
}
return PartialView("_GridListInvoices", TransactionProvider.InvoiceEditableTransactions(assembly, unit, invDate, invDate));
}
public static List<SelectListItem> LoadAssemblyUnits(string assembly)
{
List<SelectListItem> listAUnits = new List<SelectListItem>();
IList<string> units;
using (var db = new eXmlContext())
{
units = db.Set<PostedTransaction>()
.Where(x => x.AssemblyName == assembly)
.Select(x => x.UnitName)
.Distinct()
.ToList();
}
int i = 0;
foreach (var unit in units)
{
i++;
SelectListItem listAssUnit = new SelectListItem
{
Value = unit.ToString(),
Text = unit.ToString(),
Selected = (i == 1)
};
listAUnits.Add(listAssUnit);
}
return listAUnits;
}
public static List<SelectListItem> LoadUnitConsultants(string unit)
{
List<SelectListItem> listUConsults = new List<SelectListItem>();
IList<string> consultants;
using (var db = new eXmlContext())
{
consultants = db.Set<PostedTransaction>()
.Where(x => x.UnitName == unit)
.Select(x => x.ConsultantName)
.Distinct()
.ToList();
}
int i = 0;
foreach (var consultant in consultants)
{
i++;
SelectListItem listConsltnt = new SelectListItem
{
Value = consultant.ToString(),
Text = consultant.ToString(),
Selected = (i == 1)
};
listUConsults.Add(listConsltnt);
}
return listUConsults;
}
public static List<SelectListItem> LoadAssemblies()
{
List<SelectListItem> listAssemblies = new List<SelectListItem>();
List<string> assemblies;
using (var db = new eXmlContext())
{
assemblies = db.Set<PostedTransaction>()
.Select(x => x.AssemblyName)
.Distinct()
.ToList();
}
foreach (var asmbly in assemblies)
{
SelectListItem listAItem = new SelectListItem
{
Value = asmbly.ToString(),
Text = asmbly.ToString(),
Selected = false
};
listAssemblies.Add(listAItem);
}
return listAssemblies;
}
public static List<SelectListItem> LoadItems()
{
List<SelectListItem> listItems = new List<SelectListItem>();
List<string> items;
using (var db = new eXmlContext())
{
items = db.Set<PostedTransaction>()
.Select(x => x.ItemName)
.Distinct()
.ToList();
}
foreach (var itm in items)
{
SelectListItem listAItem = new SelectListItem
{
Value = itm.ToString(),
Text = itm.ToString(),
Selected = false
};
listItems.Add(listAItem);
}
return listItems;
}
protected void CreateDbContext()
{
DbContext = new eXmlContext();
}
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");
}
//}
}
protected void CreateDbContext()
{
DbContext = new eXmlContext();
}
private void UpdatePurchaseRegister(Invoice invoice,string company, string dbShip,
DateTime fromDate, DateTime toDate )
{
try
{
using (var db = new eXmlContext())
{
foreach (var invItem in invoice.Items)
{
var purRecExists = db.Set<PurchaseRegister>()
.Where(x => x.Company == company && x.DbShip == dbShip)
.Where(x => x.InvoiceNo == invoice.InvoiceNo)
.Where(x => x.InvoiceType == invoice.InvoiceType)
.Where(x => x.ItemCode == invItem.ItemCode)
.Where(x => x.PaymentInstrument == invItem.PaymentInstrument)
.Where(x => x.OrdYearWk == invoice.OrdYearWk && x.InvYearWk == invoice.InvYearWk)
.FirstOrDefault();
if (purRecExists == null)
{
PurchaseRegister reg = new PurchaseRegister
{
Company = company,
DbShip = dbShip,
FromDate = fromDate,
ToDate = toDate,
ItemCode = invItem.ItemCode,
PaymentInstrument = invItem.PaymentInstrument,
ItemName = invItem.ItemName,
OrdYearWk = invoice.OrdYearWk,
InvoiceNo = invoice.InvoiceNo,
InvoiceType = invoice.InvoiceType,
InvoiceDate = invoice.InvoiceDate,
InvYearWk = invoice.InvYearWk,
Quantity = invItem.Quantity,
PriceWOTax = invItem.PriceWOTax,
VAT = invItem.VAT,
PriceInclVAT = invItem.PriceInclVAT
};
db.PurchaseRegister.Add(reg);
}
else
{
purRecExists.Company = company;
purRecExists.DbShip = dbShip;
purRecExists.FromDate = fromDate;
purRecExists.ToDate = toDate;
purRecExists.ItemCode = invItem.ItemCode;
purRecExists.PaymentInstrument = invItem.PaymentInstrument;
purRecExists.ItemName = invItem.ItemName;
purRecExists.OrdYearWk = invoice.OrdYearWk;
purRecExists.InvoiceNo = invoice.InvoiceNo;
purRecExists.InvoiceType = invoice.InvoiceType;
purRecExists.InvoiceDate = invoice.InvoiceDate;
purRecExists.InvYearWk = invoice.InvYearWk;
purRecExists.Quantity = invItem.Quantity;
purRecExists.PriceWOTax = invItem.PriceWOTax;
purRecExists.VAT = invItem.VAT;
purRecExists.PriceInclVAT = invItem.PriceInclVAT;
db.Entry(purRecExists).State = EntityState.Modified;
}
}
db.SaveChanges();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}