private int CreateSaleTax(VoyagerContext db, ImportSaleItemWise item)
{
// Always GST and with Local Sale
//Calulate Rate
decimal rate = 0;
rate = ((item.SGST + item.CGST) * 100) / item.BasicRate;
int taxId = 1;
try
{
taxId = db.SaleTaxTypes.Where(c => c.CompositeRate == rate).FirstOrDefault().SaleTaxTypeId;
return(taxId);
}
catch (Exception)
{
SaleTaxType taxType = new SaleTaxType {
CompositeRate = rate, TaxName = "OutPut Tax GST(CGST+SGST) @" + rate, TaxType = TaxType.GST
};
db.SaleTaxTypes.Add(taxType);
db.SaveChanges();
return(taxType.SaleTaxTypeId);
}
}
private SaleInvoice CreateSaleInvoice(VoyagerContext db, ImportSaleItemWise item, SaleInvoice invoice)
{
if (invoice != null)
{
if (invoice.InvoiceNo == item.InvoiceNo)
{
// invoice.InvoiceNo = item.InvoiceNo;
//invoice.OnDate = item.InvoiceDate;
invoice.TotalDiscountAmount += item.Discount;
invoice.TotalBillAmount += item.LineTotal;
invoice.TotalItems += 1;//TODO: Check for count
invoice.TotalQty += item.Quantity;
invoice.RoundOffAmount += item.RoundOff;
invoice.TotalTaxAmount += item.SGST; //TODO: Check
invoice.PaymentDetail = CreatePaymentDetails(db, item);
invoice.CustomerId = GetCustomerId(db, item);
}
else
{
db.SaleInvoices.Add(invoice);
db.SaveChanges();
invoice = new SaleInvoice
{
InvoiceNo = item.InvoiceNo,
OnDate = item.InvoiceDate,
TotalDiscountAmount = item.Discount,
TotalBillAmount = item.LineTotal,
TotalItems = 1,//TODO: Check for count
TotalQty = item.Quantity,
RoundOffAmount = item.RoundOff,
TotalTaxAmount = item.SGST, //TODO: Check
PaymentDetail = CreatePaymentDetails(db, item),
CustomerId = GetCustomerId(db, item),
SaleItems = new List <SaleItem>()
};
}
}
else
{
invoice = new SaleInvoice
{
InvoiceNo = item.InvoiceNo,
OnDate = item.InvoiceDate,
TotalDiscountAmount = item.Discount,
TotalBillAmount = item.LineTotal,
TotalItems = 1,//TODO: Check for count
TotalQty = item.Quantity,
RoundOffAmount = item.RoundOff,
TotalTaxAmount = item.SGST, //TODO: Check
PaymentDetail = CreatePaymentDetails(db, item),
CustomerId = GetCustomerId(db, item),
SaleItems = new List <SaleItem>()
};
}
return(invoice);
}
public ActionResult DeleteConfirmed(int id)
{
ImportSaleItemWise importSaleItemWise = db.ImportSaleItemWises.Find(id);
db.ImportSaleItemWises.Remove(importSaleItemWise);
db.SaveChanges();
return(RedirectToAction("Index"));
}
public ActionResult Edit([Bind(Include = "ImportSaleItemWiseId,InvoiceDate,InvoiceNo,InvoiceType,BrandName,ProductName,ItemDesc,HSNCode,Barcode,StyleCode,Quantity,MRP,Discount,BasicRate,Tax,SGST,CGST,LineTotal,RoundOff,BillAmnt,PaymentType,Saleman,IsDataConsumed,ImportTime")] ImportSaleItemWise importSaleItemWise)
{
if (ModelState.IsValid)
{
db.Entry(importSaleItemWise).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(importSaleItemWise));
}
public async Task <IActionResult> Create([Bind("ImportSaleItemWiseId,InvoiceDate,InvoiceNo,InvoiceType,BrandName,ProductName,ItemDesc,HSNCode,Barcode,StyleCode,Quantity,MRP,Discount,BasicRate,Tax,SGST,CGST,LineTotal,RoundOff,BillAmnt,PaymentType,Saleman,IsDataConsumed,ImportTime")] ImportSaleItemWise importSaleItemWise)
{
if (ModelState.IsValid)
{
_context.Add(importSaleItemWise);
await _context.SaveChangesAsync();
return(RedirectToAction(nameof(Index)));
}
return(View(importSaleItemWise));
}
public int GetCustomerId(VoyagerContext db, ImportSaleItemWise item)
{
if (item.PaymentType == "CAS") //TODO: Check For Actual Data.
{
return(1);
}
else
{
return(2);
}
//return 1;
}
private SalePaymentDetail CreatePaymentDetails(VoyagerContext db, ImportSaleItemWise item)
{
if (item.PaymentType == null || item.PaymentType == "")
{
return(null);
}
else if (item.PaymentType == "CAS")
{
//cash Payment
SalePaymentDetail payment = new SalePaymentDetail
{
CashAmount = item.BillAmnt,
PayMode = SalePayMode.Cash
};
return(payment);
}
else if (item.PaymentType == "CRD")
{
SalePaymentDetail payment = new SalePaymentDetail
{
CardAmount = item.BillAmnt,
PayMode = SalePayMode.Card
};
//Mix Payment
return(payment);
}
else if (item.PaymentType == "MIX")
{
SalePaymentDetail payment = new SalePaymentDetail
{
MixAmount = item.BillAmnt,
PayMode = SalePayMode.Mix
};
//CASH
return(payment);
}
else if (item.PaymentType == "SR")
{
SalePaymentDetail payment = new SalePaymentDetail
{
CashAmount = item.BillAmnt,
PayMode = SalePayMode.SR
};
return(payment);
}
else
{
return(null);
}
}
private RegularPaymentDetail CreatePaymentDetails(VoyagerContext db, ImportSaleItemWise item)
{
RegularPaymentDetail payment = null;
if (string.IsNullOrEmpty(item.PaymentType) /*== null || item.PaymentType == "" */)
{
return(null);
}
else if (item.PaymentType == "CAS")
{
//cash Payment
payment = new RegularPaymentDetail
{
CashAmount = item.BillAmnt,
PayMode = SalePayMode.Cash
};
//return payment;
}
else if (item.PaymentType == "CRD")
{
payment = new RegularPaymentDetail
{
CardAmount = item.BillAmnt,
PayMode = SalePayMode.Card
};
//Mix Payment
//return payment;
}
else if (item.PaymentType == "MIX")
{
payment = new RegularPaymentDetail
{
MixAmount = item.BillAmnt,
PayMode = SalePayMode.Mix
};
//CASH
//return payment;
}
else if (item.PaymentType == "SR")
{
payment = new RegularPaymentDetail
{
CashAmount = item.BillAmnt,
PayMode = SalePayMode.SR
};
// return payment;
}
//else
return(payment);
}
// GET: ImportSaleItemWises/Delete/5
public ActionResult Delete(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
ImportSaleItemWise importSaleItemWise = db.ImportSaleItemWises.Find(id);
if (importSaleItemWise == null)
{
return(HttpNotFound());
}
return(View(importSaleItemWise));
}
//Invoice No Invoice Date Invoice Type
//Brand Name Product Name Item Desc
//HSN Code BAR CODE Style Code Quantity
//MRP Discount Amt Basic Amt Tax Amt SGST Amt CGST Amt Line Total Round Off
//Bill Amt Payment Mode SalesMan Name
private int StockItem(VoyagerContext db, ImportSaleItemWise item, out Units unit)
{
ProductItem pItem = new ProductItem
{
Barcode = item.Barcode,
Cost = -999,
MRP = item.MRP,
StyleCode = item.StyleCode,
ProductName = item.ProductName,
ItemDesc = item.ItemDesc,
BrandId = GetBrand(db, item.StyleCode),
};
//spliting ProductName
string [] PN = item.ProductName.Split('/');
// Apparel / Work / Blazers
if (PN [0] == "Apparel")
{
pItem.Units = Units.Pcs;
pItem.Categorys = ProductCategorys.ReadyMade;
}
else if (PN [0] == "Suiting" || PN [0] == "Shirting")
{
pItem.Units = Units.Meters;
pItem.Categorys = ProductCategorys.Fabric;
}
else if (PN [0] == "Tailoring")
{
pItem.Units = Units.Nos;
pItem.Categorys = ProductCategorys.Tailoring;
}
else
{
pItem.Units = Units.Nos;
pItem.Categorys = ProductCategorys.Others; //TODO: For time being
}
List <Category> catIds = GetCategory(db, PN [0], PN [1], PN [2]);
pItem.MainCategory = catIds [0];
pItem.ProductCategory = catIds [1];
pItem.ProductType = catIds [2];
db.ProductItems.Add(pItem);
db.SaveChanges();
unit = pItem.Units;
return(pItem.ProductItemId);
}
private int CreateSaleTax(VoyagerContext db, ImportSaleItemWise item, bool isIGST = false)
{
if (item.Tax != 0 && item.SGST != 0)
{
//GST Bill
}
else if (item.Tax == 0 && item.SGST == 0)
{
//TODO: Tax implementation
}
else
{
}
return(1);
}
private SaleItem CreateSaleItem(VoyagerContext db, ImportSaleItemWise item)
{
var pi = db.ProductItems.Where(c => c.Barcode == item.Barcode).Select(c => new { c.ProductItemId, c.Units }).FirstOrDefault();
SaleItem saleItem = new SaleItem
{
BarCode = item.Barcode,
MRP = item.MRP,
BasicAmount = item.BasicRate,
Discount = item.Discount,
Qty = item.Quantity,
TaxAmount = item.SGST,
BillAmount = item.LineTotal,
Units = pi.Units,
ProductItemId = pi.ProductItemId,
SalesmanId = GetSalesmanId(db, item.Saleman),
SaleTaxTypeId = CreateSaleTax(db, item)
};
SalePurchaseManager.UpDateStock(db, pi.ProductItemId, item.Quantity, false);// TODO: Check for this working
return(saleItem);
}
private SaleItem CreateSaleItem(VoyagerContext db, ImportSaleItemWise item)
{
var pi = db.ProductItems.Where(c => c.Barcode == item.Barcode).Select(c => new { c.ProductItemId, c.Units }).FirstOrDefault();
if (pi == null)
{
//TODO: Handle for ProductItem Doesnt Exsist.
//create item and stock
int id = StockItem(db, item, out Units UNTS);
pi = new { ProductItemId = id, Units = UNTS };
}
SaleItem saleItem = new SaleItem
{
BarCode = item.Barcode,
MRP = item.MRP,
BasicAmount = item.BasicRate,
Discount = item.Discount,
Qty = item.Quantity,
TaxAmount = item.SGST + item.CGST,
BillAmount = item.LineTotal,
Units = pi.Units,
ProductItemId = pi.ProductItemId,
SalesmanId = GetSalesmanId(db, item.Saleman),
SaleTaxTypeId = CreateSaleTax(db, item)
};
if (!SalePurchaseManager.UpDateStock(db, pi.ProductItemId, item.Quantity, false))
{
//TODO: Create Stock and update
CreateStockItem(db, saleItem.Qty, saleItem.ProductItemId, saleItem.Units);
}
item.IsDataConsumed = true;
db.Entry(item).State = EntityState.Modified;
return(saleItem);
}
[Authorize(Roles = "Admin,PowerUser")] public async Task <IActionResult> Edit(int id, [Bind("ImportSaleItemWiseId,InvoiceDate,InvoiceNo,InvoiceType,BrandName,ProductName,ItemDesc,HSNCode,Barcode,StyleCode,Quantity,MRP,Discount,BasicRate,Tax,SGST,CGST,LineTotal,RoundOff,BillAmnt,PaymentType,Saleman,IsDataConsumed,ImportTime")] ImportSaleItemWise importSaleItemWise)
{
if (id != importSaleItemWise.ImportSaleItemWiseId)
{
return(NotFound());
}
if (ModelState.IsValid)
{
try
{
_context.Update(importSaleItemWise);
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!ImportSaleItemWiseExists(importSaleItemWise.ImportSaleItemWiseId))
{
return(NotFound());
}
else
{
throw;
}
}
return(RedirectToAction(nameof(Index)));
}
return(View(importSaleItemWise));
}
private RegularInvoice CreateSaleInvoice(VoyagerContext db, AprajitaRetailsContext arDB, ImportSaleItemWise item, RegularInvoice invoice)
{
if (invoice != null)
{
if (invoice.InvoiceNo == item.InvoiceNo)
{
//invoice.InvoiceNo = item.InvoiceNo;
//invoice.OnDate = item.InvoiceDate;
invoice.TotalDiscountAmount += item.Discount;
invoice.TotalBillAmount += item.LineTotal; // Check for if it is empty
invoice.TotalItems += 1; //TODO: Check for count
invoice.TotalQty += item.Quantity;
invoice.RoundOffAmount += item.RoundOff;
invoice.TotalTaxAmount += (item.SGST + item.CGST); //TODO: Check Future make it tax
invoice.StoreId = item.StoreId;
if (invoice.PaymentDetail == null)
{
invoice.PaymentDetail = CreatePaymentDetails(db, item); //Create Payment details
}
invoice.CustomerId = GetCustomerId(db, item); // CustomerId
}
else
{
db.RegularInvoices.Add(invoice);
db.SaveChanges();
CreateDailySale(arDB, db, invoice);
invoice = new RegularInvoice
{
InvoiceNo = item.InvoiceNo,
OnDate = item.InvoiceDate,
TotalDiscountAmount = item.Discount,
TotalBillAmount = item.LineTotal,
TotalItems = 1,//TODO: Check for count
TotalQty = item.Quantity,
RoundOffAmount = item.RoundOff,
TotalTaxAmount = (item.SGST + item.CGST), //TODO: Check
PaymentDetail = CreatePaymentDetails(db, item),
CustomerId = GetCustomerId(db, item),
StoreId = item.StoreId,
SaleItems = new List <RegularSaleItem> ()
};
}
}
else
{
invoice = new RegularInvoice
{
InvoiceNo = item.InvoiceNo,
OnDate = item.InvoiceDate,
TotalDiscountAmount = item.Discount,
TotalBillAmount = item.LineTotal,
TotalItems = 1,//TODO: Check for count
TotalQty = item.Quantity,
RoundOffAmount = item.RoundOff,
TotalTaxAmount = (item.SGST + item.CGST), //TODO: Check
PaymentDetail = CreatePaymentDetails(db, item),
CustomerId = GetCustomerId(db, item),
StoreId = item.StoreId,
SaleItems = new List <RegularSaleItem> ()
};
}
return(invoice);
}
}//end of function
private int ImportSaleItemWise(VoyagerContext db, string fileName, string StoreCode, bool IsVat, bool IsLocal)
{
//string rootFolder = IHostingEnvironment.WebRootPath;
//string fileName = @"ImportCustomers.xlsx";
FileInfo file = new FileInfo(fileName);
using ExcelPackage package = new ExcelPackage(file);
ExcelWorksheet workSheet = package.Workbook.Worksheets ["Sheet1"];
int totalRows = workSheet.Dimension.Rows;
int StoreID = 1;//Default
StoreID = db.Stores.Where(c => c.StoreCode == StoreCode).Select(c => c.StoreId).FirstOrDefault();
if (StoreID < 1)
{
StoreID = 1;
}
List <ImportSaleItemWise> saleList = new List <ImportSaleItemWise> ();
//GRNNo GRNDate Invoice No Invoice Date Supplier Name Barcode Product Name Style Code Item Desc Quantity MRP MRP Value Cost Cost Value TaxAmt ExmillCost Excise1 Excise2 Excise3
int xo = 0;
for (int i = 2; i <= totalRows; i++)
{
//Invoice No 1 Invoice Date 2 Invoice Type 3
//Brand Name 4 Product Name 5 Item Desc 6 HSN Code 7 BAR CODE 8 //
//Style Code 9 Quantity 10 MRP 11 Discount Amt 12 Basic Amt 13 Tax Amt 14 SGST Amt 15
//CGST Amt 16 CESS Amt 17 Line Total 18 Round Off 19 Bill Amt 20 Payment Mode 21 SalesMan Name 22 //
//Coupon % Coupon Amt SUB TYPE Bill Discount LP Flag Inst Order CD TAILORING FLAG
try
{
ImportSaleItemWise p = new ImportSaleItemWise
{
InvoiceNo = (workSheet.Cells [i, 1].Value ?? string.Empty).ToString(),
InvoiceDate = (DateTime)workSheet.Cells [i, 2].GetValue <DateTime> (),
InvoiceType = (workSheet.Cells [i, 3].Value ?? string.Empty).ToString(),
BrandName = (workSheet.Cells [i, 4].Value ?? string.Empty).ToString(),
ProductName = (workSheet.Cells [i, 5].Value ?? string.Empty).ToString(),
ItemDesc = (workSheet.Cells [i, 6].Value ?? string.Empty).ToString(),
Barcode = (workSheet.Cells [i, 8].Value ?? string.Empty).ToString(),
StyleCode = (workSheet.Cells [i, 9].Value ?? string.Empty).ToString(),
PaymentType = (workSheet.Cells [i, 21].Value ?? string.Empty).ToString(),
Saleman = (workSheet.Cells [i, 22].Value ?? string.Empty).ToString(),
IsDataConsumed = false,
//ImportTime = DateTime.Today,
IsLocal = IsLocal,
IsVatBill = IsVat,
StoreId = StoreID
};
p.HSNCode = (workSheet.Cells [i, 7].Value ?? string.Empty).ToString();
p.Quantity = (double)workSheet.Cells [i, 10].GetValue <double> ();
p.MRP = (decimal)workSheet.Cells [i, 11].GetValue <decimal> ();
p.Discount = (decimal)workSheet.Cells [i, 12].GetValue <decimal> ();
p.BasicRate = (decimal)workSheet.Cells [i, 13].GetValue <decimal> ();
p.Tax = (decimal)workSheet.Cells [i, 14].GetValue <decimal> ();
p.SGST = (decimal)workSheet.Cells [i, 15].GetValue <decimal> ();
p.CGST = (decimal)workSheet.Cells [i, 16].GetValue <decimal> ();
//p.CESS = (decimal)workSheet.Cells[i, 17].GetValue<decimal>();
p.LineTotal = (decimal)workSheet.Cells [i, 18].GetValue <decimal> ();
p.RoundOff = (decimal)workSheet.Cells [i, 19].GetValue <decimal> ();
p.BillAmnt = (decimal)workSheet.Cells [i, 20].GetValue <decimal> ();
saleList.Add(p);
xo++;
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message);
// return UploadReturns.Error;
throw;
}
}
db.ImportSaleItemWises.AddRange(saleList);
return(db.SaveChanges());
//return purchaseList;
}
public int GetCustomerId(VoyagerContext db, ImportSaleItemWise item)
{
return(1);
}