public static void PriceUpdate()
{
using (SDCManagementSystem_Test3Entities db = new SDCManagementSystem_Test3Entities())
{
try
{
db.spu_warehousePriceUpdate2();
db.SaveChanges();
}
catch (Exception)
{
throw;
}
}
}
public static List <string> ProcessFiles(string filePath)
{
int newRows = 0;
int updatedRow = 0;
List <string> result = new List <string>();
List <AORzipCode> listAOR = new List <AORzipCode>();
#region Process File
string file = filePath;
string fileLine = string.Empty;
if (File.Exists(file))
{
Application excel = new Application();
Workbook wb = excel.Workbooks.Open(file);
Worksheet excelSheet = wb.ActiveSheet;
Range xlRange = excelSheet.UsedRange;
AORzipCode vhsAOR = null;
for (int i = 1; i < xlRange.Columns[1].Rows.Count; i++)
{
if (excelSheet.Cells[i, 1].Value2 != null)
{
string _description = string.Empty;
string _code = string.Empty;
int _aorcode = 0;
if (excelSheet.Cells[i, 1].Value2 != null)
{
_code = excelSheet.Cells[i, 1].Value.ToString();
using (SDCManagementSystem_Test3Entities db = new SDCManagementSystem_Test3Entities())
{
AOR vhs = db.AORs.Where(
x => x.AORName.Contains(_code)).FirstOrDefault();
if (vhs != null)
{
_aorcode = vhs.AORId;
if (excelSheet.Cells[i, 4].Value2 != null)
{
_description = excelSheet.Cells[i, 4].Value.ToString();
}
if (excelSheet.Cells[i, 3].Value2 != null)
{
string val = excelSheet.Cells[i, 3].Value.ToString();
string[] zips = val.Split(' ');
foreach (string zip in zips)
{
vhsAOR = new AORzipCode();
vhsAOR.AORId = _aorcode;
vhsAOR.ZIPCode = zip;
vhsAOR.Description = _description;
listAOR.Add(vhsAOR);
}
}
}
}
}
}
}
object misValue = System.Reflection.Missing.Value;
wb.Close(false, misValue, misValue);
excel.Quit();
using (SDCManagementSystem_Test3Entities db = new SDCManagementSystem_Test3Entities())
{
foreach (AORzipCode dealer in listAOR)
{
if (dealer.AORId != 0)
{
AORzipCode vhs = db.AORzipCodes.Where(
x => x.AORId == dealer.AORId && x.ZIPCode == dealer.ZIPCode).FirstOrDefault();
if (vhs != null)
{ //modify record
vhs.ZIPCode = dealer.ZIPCode;
vhs.Description = dealer.Description;
updatedRow++;
}
else
{
db.AORzipCodes.Add(dealer);
newRows++;
}
}
else
{
db.AORzipCodes.Add(dealer);
newRows++;
}
}
try
{
db.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
} //End of Entity
result.Add("The " + filePath + " file was uploaded: New Row added: " + newRows.ToString() + " Row updated: " + updatedRow.ToString() + Environment.NewLine);
} // End If File>exits
#endregion
return(result);
}
public static List <string> ProcessFiles(string filePath)
{
int newRows = 0;
int updatedRow = 0;
List <string> result = new List <string>();
List <CrossSellZip> list = new List <CrossSellZip>();
#region Process File
string file = filePath;
string fileLine = string.Empty;
if (File.Exists(file))
{
Application excel = new Application();
Workbook wb = excel.Workbooks.Open(file);
int _sheets = excel.ActiveWorkbook.Sheets.Count;
for (int s = 1; s < _sheets + 1; s++)
{
int _dealerid = 0;
DateTime _SalesFrom = DateTime.MinValue;
DateTime _SalesThrough = DateTime.MinValue;
Worksheet excelSheet = wb.Sheets[s];
Range xlRange = excelSheet.UsedRange;
for (int i = 1; i < xlRange.Columns[1].Rows.Count; i++)
{
if (excelSheet.Cells[i, 1].Value2 != null)
{
if (excelSheet.Cells[i, 1].Value.ToString() != string.Empty)
{
string val = excelSheet.Cells[i, 1].Value.ToString();
if (val.Contains("Dealer"))
{
SDCManagementSystem_Test3Entities db = new SDCManagementSystem_Test3Entities();
DWT_Dealers d = db.DWT_Dealers.Where(x => x.Code == val.Substring(val.IndexOf(" ") + 1, 6)).FirstOrDefault();
if (d != null)
{
_dealerid = d.DealerId;
}
}
if (val.StartsWith("For"))
{
_SalesFrom = DateTime.Parse(val.Substring(15, 10));
_SalesThrough = DateTime.Parse(val.Substring(34, 10));
}
if (int.TryParse(val, out int saleszip))
{
CrossSellZip vhs = new CrossSellZip();
vhs.dealer_id = _dealerid;
vhs.SalesFrom = _SalesFrom;
vhs.SalesThrough = _SalesThrough;
if (excelSheet.Cells[i, 1].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 1].Value.ToString(), out int _SalesinZIP))
{
vhs.SalesinZIP = _SalesinZIP;
}
}
if (excelSheet.Cells[i, 2].Value2 != null)
{
if (decimal.TryParse(excelSheet.Cells[i, 2].Value.ToString(), out decimal _pctTotalSales))
{
vhs.pctTotalSales = _pctTotalSales;
}
}
if (excelSheet.Cells[i, 4].Value2 != null)
{
vhs.ZIP = excelSheet.Cells[i, 4].Value.ToString();
}
if (excelSheet.Cells[i, 6].Value2 != null)
{
vhs.ZIPName = excelSheet.Cells[i, 6].Value.ToString();
}
if (excelSheet.Cells[i, 9].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 9].Value.ToString(), out int _CurrentPopulation))
{
vhs.CurrentPopulation = _CurrentPopulation;
}
}
if (excelSheet.Cells[i, 10].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 10].Value.ToString(), out int _C5YrsProyectedPopulation))
{
vhs.C5YrsProyectedPopulation = _C5YrsProyectedPopulation;
}
}
if (excelSheet.Cells[i, 12].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 12].Value.ToString(), out int _CurrentHouseholds))
{
vhs.CurrentHouseholds = _CurrentHouseholds;
}
}
if (excelSheet.Cells[i, 13].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 13].Value.ToString(), out int _C5YrsProyectedHouseholds))
{
vhs.C5YrsProyectedHouseholds = _C5YrsProyectedHouseholds;
}
}
if (excelSheet.Cells[i, 15].Value2 != null)
{
if (decimal.TryParse(excelSheet.Cells[i, 15].Value.ToString(), out decimal _CurrentMediaHouseholdsIncome))
{
vhs.CurrentMediaHouseholdsIncome = _CurrentMediaHouseholdsIncome;
}
}
if (excelSheet.Cells[i, 15].Value2 != null)
{
if (decimal.TryParse(excelSheet.Cells[i, 16].Value.ToString(), out decimal _C5YrsMediaHouseholdsIncome))
{
vhs.C5YrsMediaHouseholdsIncome = _C5YrsMediaHouseholdsIncome;
}
}
if (excelSheet.Cells[i, 18].Value2 != null)
{
if (decimal.TryParse(excelSheet.Cells[i, 18].Value.ToString(), out decimal _CurrentAVGHouseholdsIncome))
{
vhs.CurrentAVGHouseholdsIncome = _CurrentAVGHouseholdsIncome;
}
}
if (excelSheet.Cells[i, 20].Value2 != null)
{
if (decimal.TryParse(excelSheet.Cells[i, 20].Value.ToString(), out decimal _C5YrsAVGHouseholdsIncome))
{
vhs.C5YrsAVGHouseholdsIncome = _C5YrsAVGHouseholdsIncome;
}
}
if (excelSheet.Cells[i, 22].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 22].Value.ToString(), out int _CurrentHouseholdsUnder50))
{
vhs.CurrentHouseholdsUnder50 = _CurrentHouseholdsUnder50;
}
}
if (excelSheet.Cells[i, 25].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 25].Value.ToString(), out int _C5YrsProyectedHouseholdsUnder50))
{
vhs.C5YrsProyectedHouseholdsUnder50 = _C5YrsProyectedHouseholdsUnder50;
}
}
if (excelSheet.Cells[i, 27].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 27].Value.ToString(), out int _CurrentHouseholds50to100))
{
vhs.CurrentHouseholds50to100 = _CurrentHouseholds50to100;
}
}
if (excelSheet.Cells[i, 29].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 29].Value.ToString(), out int _C5YrsProyectedHouseholds50to100))
{
vhs.C5YrsProyectedHouseholds50to100 = _C5YrsProyectedHouseholds50to100;
}
}
if (excelSheet.Cells[i, 31].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 31].Value.ToString(), out int _CurrentHouseholdsOver100))
{
vhs.CurrentHouseholdsOver100 = _CurrentHouseholdsOver100;
}
}
if (excelSheet.Cells[i, 33].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 33].Value.ToString(), out int _C5YrsProyectedHouseholdsOver100))
{
vhs.C5YrsProyectedHouseholdsOver100 = _C5YrsProyectedHouseholdsOver100;
}
}
list.Add(vhs);
}
}
}
}
}
object misValue = System.Reflection.Missing.Value;
wb.Close(false, misValue, misValue);
excel.Quit();
using (SDCManagementSystem_Test3Entities db = new SDCManagementSystem_Test3Entities())
{
foreach (CrossSellZip dealer in list)
{
if (dealer.dealer_id != 0)
{
CrossSellZip vhs = db.CrossSellZips.Where(
x => x.dealer_id == dealer.dealer_id && x.SalesFrom == dealer.SalesFrom && x.SalesThrough == dealer.SalesThrough).FirstOrDefault();
if (vhs != null)
{ //modify record
vhs.SalesinZIP = dealer.SalesinZIP;
vhs.pctTotalSales = dealer.pctTotalSales;
vhs.ZIP = dealer.ZIP;
vhs.ZIPName = dealer.ZIPName;
vhs.CurrentPopulation = dealer.CurrentPopulation;
vhs.C5YrsProyectedPopulation = dealer.C5YrsProyectedPopulation;
vhs.CurrentHouseholds = dealer.CurrentHouseholds;
vhs.C5YrsProyectedHouseholds = dealer.C5YrsProyectedHouseholds;
vhs.CurrentMediaHouseholdsIncome = dealer.CurrentMediaHouseholdsIncome;
vhs.C5YrsMediaHouseholdsIncome = dealer.C5YrsMediaHouseholdsIncome;
vhs.CurrentAVGHouseholdsIncome = dealer.CurrentAVGHouseholdsIncome;
vhs.C5YrsAVGHouseholdsIncome = dealer.C5YrsAVGHouseholdsIncome;
vhs.CurrentHouseholdsUnder50 = dealer.CurrentHouseholdsUnder50;
vhs.C5YrsProyectedHouseholdsUnder50 = dealer.C5YrsProyectedHouseholdsUnder50;
vhs.CurrentHouseholds50to100 = dealer.CurrentHouseholds50to100;
vhs.C5YrsProyectedHouseholds50to100 = dealer.C5YrsProyectedHouseholds50to100;
vhs.CurrentHouseholdsOver100 = dealer.CurrentHouseholdsOver100;
vhs.C5YrsProyectedHouseholdsOver100 = dealer.C5YrsProyectedHouseholdsOver100;
updatedRow++;
}
else
{
db.CrossSellZips.Add(dealer);
newRows++;
}
}
else
{
db.CrossSellZips.Add(dealer);
newRows++;
}
}
try
{
db.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
} //End of Entity
result.Add("The " + filePath + " file was uploaded: New Row added: " + newRows.ToString() + " Row updated: " + updatedRow.ToString() + Environment.NewLine);
} // End If File>exits
#endregion
return(result);
}
public static List <string> ProcessFiles(string filePath)
{
int newRows = 0;
int updatedRow = 0;
List <string> result = new List <string>();
#region Process File
string file = filePath;
string fileLine = string.Empty;
if (File.Exists(file))
{
Application excel = new Application();
Workbook wb = excel.Workbooks.Open(file);
Worksheet excelSheet = wb.ActiveSheet;
Range xlRange = excelSheet.UsedRange;
List <CrossSellZip> list = new List <CrossSellZip>();
if (excelSheet.Cells[1, 1].Value.ToString() == "DATE")
{
for (int i = 2; i < xlRange.Columns[1].Rows.Count; i++)
{
CrossSellZip vhs = new CrossSellZip();
if (excelSheet.Cells[i, 1].Value2 != null)
{
if (DateTime.TryParse(excelSheet.Cells[i, 1].Value.ToString(), out DateTime _DateFile))
{
vhs.DateFile = _DateFile;
}
vhs.Activity = excelSheet.Cells[i, 2].Value.ToString();
vhs.ItemNumber = excelSheet.Cells[i, 3].Value.ToString();
var isNumeric = int.TryParse(vhs.ItemNumber, out int n);
if (isNumeric)
{
if (vhs.ItemNumber.Length < 9)
{
vhs.ItemNumber = vhs.ItemNumber.PadLeft(9, '0');
}
}
if (int.TryParse(excelSheet.Cells[i, 4].Value.ToString(), out int _Quantity))
{
vhs.Quantity = _Quantity;
}
vhs.SDNNumber = excelSheet.Cells[i, 6].Value.ToString();
vhs.PONumber = excelSheet.Cells[i, 7].Value.ToString();
vhs.WH = excelSheet.Cells[i, 8].Value.ToString();
vhs.Picker = excelSheet.Cells[i, 9].Value.ToString();
list.Add(vhs);
}
}
}
object misValue = System.Reflection.Missing.Value;
wb.Close(false, misValue, misValue);
excel.Quit();
using (SDCManagementSystem_Test3Entities db = new SDCManagementSystem_Test3Entities())
{
foreach (CrossSellZip dealer in list)
{
if (dealer.dealer_id != 0)
{
CrossSellZip vhs = db.CrossSellZips.Where(
x => x.dealer_id == dealer.dealer_id && x.SalesFrom == dealer.SalesFrom && x.SalesThrough == dealer.SalesThrough).FirstOrDefault();
if (vhs != null)
{ //modify record
vhs.SalesinZIP = dealer.SalesinZIP;
vhs.pctTotalSales = dealer.pctTotalSales;
vhs.ZIP = dealer.ZIP;
vhs.ZIPName = dealer.ZIPName;
vhs.CurrentPopulation = dealer.CurrentPopulation;
vhs.C5YrsProyectedPopulation = dealer.C5YrsProyectedPopulation;
vhs.CurrentHouseholds = dealer.CurrentHouseholds;
vhs.C5YrsProyectedHouseholds = dealer.C5YrsProyectedHouseholds;
vhs.CurrentMediaHouseholdsIncome = dealer.CurrentMediaHouseholdsIncome;
vhs.C5YrsMediaHouseholdsIncome = dealer.C5YrsMediaHouseholdsIncome;
vhs.CurrentAVGHouseholdsIncome = dealer.CurrentAVGHouseholdsIncome;
vhs.C5YrsAVGHouseholdsIncome = dealer.C5YrsAVGHouseholdsIncome;
vhs.CurrentHouseholdsUnder50 = dealer.CurrentHouseholdsUnder50;
vhs.C5YrsProyectedHouseholdsUnder50 = dealer.C5YrsProyectedHouseholdsUnder50;
vhs.CurrentHouseholds50to100 = dealer.CurrentHouseholds50to100;
vhs.C5YrsProyectedHouseholds50to100 = dealer.C5YrsProyectedHouseholds50to100;
vhs.CurrentHouseholdsOver100 = dealer.CurrentHouseholdsOver100;
vhs.C5YrsProyectedHouseholdsOver100 = dealer.C5YrsProyectedHouseholdsOver100;
updatedRow++;
}
else
{
db.CrossSellZips.Add(dealer);
newRows++;
}
}
else
{
db.CrossSellZips.Add(dealer);
newRows++;
}
}
try
{
db.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
} //End of Entity
result.Add("The " + filePath + " file was uploaded: New Row added: " + newRows.ToString() + " Row updated: " + updatedRow.ToString() + Environment.NewLine);
} // End If File>exits
#endregion
return(result);
}
public static List <string> ProcessFiles(string filePath)
{
int newRows = 0;
int updatedRow = 0;
List <string> result = new List <string>();
#region Process File
string file = filePath;
string fileLine = string.Empty;
CheckURL();
if (File.Exists(file))
{
Application excel = new Application();
Workbook wb = excel.Workbooks.Open(file);
Worksheet excelSheet = wb.ActiveSheet;
Range xlRange = excelSheet.UsedRange;
List <WareHouseReceipt> list = new List <WareHouseReceipt>();
List <Pagination> listP = new List <Pagination>();
if (excelSheet.Cells[1, 1].Value.ToString() == "DATE")
{
for (int i = 2; i < xlRange.Columns[1].Rows.Count + 1; i++)
{
WareHouseReceipt vhs = new WareHouseReceipt();
if (excelSheet.Cells[i, 1].Value2 != null)
{
if (DateTime.TryParse(excelSheet.Cells[i, 1].Value.ToString(), out DateTime _DateFile))
{
vhs.DateFile = _DateFile;
}
vhs.Activity = excelSheet.Cells[i, 2].Value.ToString();
vhs.ItemNumber = excelSheet.Cells[i, 3].Value.ToString();
var isNumeric = int.TryParse(vhs.ItemNumber, out int n);
if (isNumeric)
{
if (vhs.ItemNumber.Length < 9)
{
vhs.ItemNumber = vhs.ItemNumber.PadLeft(9, '0');
}
}
if (int.TryParse(excelSheet.Cells[i, 4].Value.ToString(), out int _Quantity))
{
vhs.Quantity = _Quantity;
}
vhs.SDNNumber = excelSheet.Cells[i, 6].Value.ToString();
vhs.PONumber = excelSheet.Cells[i, 7].Value.ToString();
vhs.WH = excelSheet.Cells[i, 8].Value.ToString();
vhs.Picker = excelSheet.Cells[i, 9].Value.ToString();
list.Add(vhs);
if (excelSheet.Cells[i, 15].Value2 != null)
{
string var = excelSheet.Cells[i, 15].Value2;
if (!var.Contains("Page"))
{
Pagination p = new Pagination
{
PageNumber = excelSheet.Cells[i, 15].Value.ToString(),
};
int.TryParse(excelSheet.Cells[i, 16].Value.ToString(), out int _count);
p.Count = _count;
listP.Add(p);
}
}
}
}
}
object misValue = System.Reflection.Missing.Value;
wb.Close(false, misValue, misValue);
excel.Quit();
//Add PageNumber
int l = 0;
foreach (Pagination p in listP)
{
for (int i = 0; i < p.Count; i++)
{
list[l].PageNumber = p.PageNumber;
l++;
}
}
using (SDCManagementSystem_Test3Entities db = new SDCManagementSystem_Test3Entities())
{
foreach (WareHouseReceipt dealer in list)
{
if (dealer.Activity != string.Empty)
{
WareHouseReceipt vhs = db.WareHouseReceipts.Where(
x => x.DateFile == dealer.DateFile.Date && x.PONumber == dealer.PONumber && x.ItemNumber == dealer.ItemNumber && x.SDNNumber == dealer.SDNNumber && x.Quantity == dealer.Quantity).FirstOrDefault();
if (vhs != null)
{ //modify record
vhs.Activity = dealer.Activity;
vhs.Quantity = dealer.Quantity;
vhs.TotalPrice = dealer.TotalPrice;
vhs.WH = dealer.WH;
vhs.Picker = dealer.Picker;
vhs.PageNumber = dealer.PageNumber;
updatedRow++;
}
else
{
db.WareHouseReceipts.Add(dealer);
newRows++;
}
}
else
{
db.WareHouseReceipts.Add(dealer);
newRows++;
}
}
try
{
db.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
} //End of Entity
result.Add("The " + filePath + " file was uploaded: New Row added: " + newRows.ToString() + " Row updated: " + updatedRow.ToString() + Environment.NewLine);
} // End If File>exits
#endregion
return(result);
}
public static List <string> ProcessFiles(string filePath)
{
int newRows = 0;
int updatedRow = 0;
List <string> result = new List <string>();
List <RetailComparison> list = new List <RetailComparison>();
#region Process File
string file = filePath;
string fileLine = string.Empty;
if (File.Exists(file))
{
Application excel = new Application();
Workbook wb = excel.Workbooks.Open(file);
int _sheets = excel.ActiveWorkbook.Sheets.Count;
//for (int s = 1; s < _sheets + 1; s++)
for (int s = 1; s < 2; s++)
{
int _dealerid = 0;
int _Year = DateTime.Now.Year;
int _Month = DateTime.Now.Month;
Worksheet excelSheet = wb.Sheets[1];
Range xlRange = excelSheet.UsedRange;
for (int i = 1; i < xlRange.Columns[1].Rows.Count; i++)
{
if (excelSheet.Cells[i, 1].Value2 != null)
{
if (excelSheet.Cells[i, 1].Value.ToString() != string.Empty)
{
string val = "0" + excelSheet.Cells[i, 1].Value.ToString();
SDCManagementSystem_Test3Entities db = new SDCManagementSystem_Test3Entities();
DWT_Dealers d = db.DWT_Dealers.Where(x => x.Code == val).FirstOrDefault();
if (d != null)
{
_dealerid = d.DealerId;
RetailComparison vhs = new RetailComparison();
vhs.Dealer_id = _dealerid;
vhs.Year = _Year;
vhs.Month = _Month - 1;
if (excelSheet.Cells[i, 3].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 3].Value.ToString(), out int _district))
{
vhs.district = _district;
}
}
if (excelSheet.Cells[i, 4].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 4].Value.ToString(), out int _Whlse_currentMY))
{
vhs.Whlse_currentMY = _Whlse_currentMY;
}
}
if (excelSheet.Cells[i, 5].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 5].Value.ToString(), out int _Whlse_passMY))
{
vhs.Whlse_passMY = _Whlse_passMY;
}
}
if (excelSheet.Cells[i, 6].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 6].Value.ToString(), out int _Whlse_ChangeYOY))
{
vhs.Whlse_ChangeYOY = _Whlse_ChangeYOY;
}
}
if (excelSheet.Cells[i, 7].Value2 != null)
{
if (decimal.TryParse(excelSheet.Cells[i, 7].Value.ToString(), out decimal _Pct_WhlseYOY))
{
vhs.Pct_WhlseYOY = _Pct_WhlseYOY * 100;
}
}
if (excelSheet.Cells[i, 8].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 8].Value.ToString(), out int _Whlse_CurrenMonth))
{
vhs.Whlse_CurrenMonth = _Whlse_CurrenMonth;
}
}
if (excelSheet.Cells[i, 9].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 9].Value.ToString(), out int _RtlSls_currentMY))
{
vhs.RtlSls_currentMY = _RtlSls_currentMY;
}
}
if (excelSheet.Cells[i, 10].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 10].Value.ToString(), out int _RtlSls_passMY))
{
vhs.RtlSls_passMY = _RtlSls_passMY;
}
}
if (excelSheet.Cells[i, 11].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 11].Value.ToString(), out int _RtlSls_ChangeYOY))
{
vhs.RtlSls_ChangeYOY = _RtlSls_ChangeYOY;
}
}
if (excelSheet.Cells[i, 12].Value2 != null)
{
if (decimal.TryParse(excelSheet.Cells[i, 12].Value.ToString(), out decimal _Pct_RtlSlsYOY))
{
vhs.Pct_RtlSlsYOY = _Pct_RtlSlsYOY * 100;
}
}
if (excelSheet.Cells[i, 13].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 13].Value.ToString(), out int _RtlSls_CurrenMonth))
{
vhs.RtlSls_CurrenMonth = _RtlSls_CurrenMonth;
}
}
list.Add(vhs);
}
}
}
}
}
object misValue = System.Reflection.Missing.Value;
wb.Close(false, misValue, misValue);
excel.Quit();
using (SDCManagementSystem_Test3Entities db = new SDCManagementSystem_Test3Entities())
{
foreach (RetailComparison dealer in list)
{
if (dealer.Dealer_id != 0)
{
RetailComparison vhs = db.RetailComparisons.Where(
x => x.Dealer_id == dealer.Dealer_id && x.Year == dealer.Year && x.Month == dealer.Month).FirstOrDefault();
if (vhs != null)
{ //modify record
vhs.Whlse_currentMY = dealer.Whlse_currentMY;
vhs.Whlse_passMY = dealer.Whlse_passMY;
vhs.Whlse_ChangeYOY = dealer.Whlse_ChangeYOY;
vhs.Pct_WhlseYOY = dealer.Pct_WhlseYOY;
vhs.Whlse_CurrenMonth = dealer.Whlse_CurrenMonth;
vhs.RtlSls_currentMY = dealer.RtlSls_currentMY;
vhs.RtlSls_passMY = dealer.RtlSls_passMY;
vhs.RtlSls_ChangeYOY = dealer.RtlSls_ChangeYOY;
vhs.Pct_RtlSlsYOY = dealer.Pct_RtlSlsYOY;
vhs.RtlSls_CurrenMonth = dealer.RtlSls_CurrenMonth;
updatedRow++;
}
else
{
db.RetailComparisons.Add(dealer);
newRows++;
}
}
else
{
db.RetailComparisons.Add(dealer);
newRows++;
}
}
try
{
db.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
} //End of Entity
result.Add("The " + filePath + " file was uploaded: New Row added: " + newRows.ToString() + " Row updated: " + updatedRow.ToString() + Environment.NewLine);
} // End If File>exits
#endregion
return(result);
}
public static List <string> ProcessFiles(string filePath)
{
int newRows = 0;
int updatedRow = 0;
List <string> result = new List <string>();
List <AOR> listAOR = new List <AOR>();
List <AORDealerHistory> listAORH = new List <AORDealerHistory>();
List <CrossSellDealerAOR> listAORCrossSale = new List <CrossSellDealerAOR>();
string valLineBefore = string.Empty;
#region Process File
string file = filePath;
string fileLine = string.Empty;
if (File.Exists(file))
{
Application excel = new Application();
Workbook wb = excel.Workbooks.Open(file);
int _dealerid = 0;
DateTime _SalesFrom = DateTime.MinValue;
DateTime _SalesThrough = DateTime.MinValue;
int _ObjTotalSales = 0;
Worksheet excelSheet = wb.ActiveSheet;
Range xlRange = excelSheet.UsedRange;
AOR vhsAOR = null;
CrossSellDealerAOR vhsCrossSellDealerAOR = null;
AORDealerHistory vhsAORDealerHistory = new AORDealerHistory();
for (int i = 1; i < xlRange.Columns[1].Rows.Count; i++)
{
if (excelSheet.Cells[i, 1].Value2 != null)
{
if (excelSheet.Cells[i, 1].Value.ToString() != string.Empty)
{
string val = excelSheet.Cells[i, 1].Value.ToString();
if (val != string.Empty)
{
if (val.StartsWith("AOR"))
{
vhsAOR = new AOR();
vhsAOR.AORName = excelSheet.Cells[i, 1].Value.ToString();
string valResponsable = excelSheet.Cells[i + 1, 1].Value.ToString();
SDCManagementSystem_Test3Entities db = new SDCManagementSystem_Test3Entities();
DWT_Dealers d = db.DWT_Dealers.Where(x => x.Code == valResponsable.Substring(0, 6)).FirstOrDefault();
if (d != null)
{
int _dealerResponsable = d.DealerId;
vhsAOR.Dealer_IdResponsable = _dealerResponsable;
}
}
if (valLineBefore == "Dealer Code and Name" && !val.StartsWith("AOR"))
{
vhsCrossSellDealerAOR = new CrossSellDealerAOR();
SDCManagementSystem_Test3Entities db = new SDCManagementSystem_Test3Entities();
DWT_Dealers d = db.DWT_Dealers.Where(x => x.Code == val.Substring(0, 6)).FirstOrDefault();
if (d != null)
{
_dealerid = d.DealerId;
vhsCrossSellDealerAOR.Dealer_id = _dealerid;
}
vhsCrossSellDealerAOR.DealerCode = val.Substring(0, 6);
int t = val.Length;
vhsCrossSellDealerAOR.DealerName = val.Substring(7);
vhsCrossSellDealerAOR.SalesFrom = _SalesFrom;
vhsCrossSellDealerAOR.SalesThrough = _SalesThrough;
vhsCrossSellDealerAOR.TotalSales = _ObjTotalSales;
vhsCrossSellDealerAOR.AORName = vhsAOR.AORName;
if (excelSheet.Cells[i, 2].Value2 != null)
{
vhsCrossSellDealerAOR.DealerCity = excelSheet.Cells[i, 2].Value.ToString();
}
if (excelSheet.Cells[i, 3].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 3].Value.ToString(), out int _DealerSaler))
{
vhsCrossSellDealerAOR.DealerSaler = _DealerSaler;
}
}
if (excelSheet.Cells[i, 4].Value2 != null)
{
if (decimal.TryParse(excelSheet.Cells[i, 4].Value.ToString(), out decimal _pctAORSales))
{
vhsCrossSellDealerAOR.pctAORSales = _pctAORSales * 100;
}
}
}
if (val.StartsWith("For"))
{
//vhsCrossSellDealerAOR.SalesFrom = DateTime.Parse(val.Substring(15, 10));
//vhsCrossSellDealerAOR.SalesThrough = DateTime.Parse(val.Substring(34, 10));
_SalesFrom = DateTime.Parse(val.Substring(15, 10));
_SalesThrough = DateTime.Parse(val.Substring(34, 10));
if (excelSheet.Cells[i, 5].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 5].Value.ToString(), out int _TotalSales))
{
//vhsCrossSellDealerAOR.TotalSales = _TotalSales;
_ObjTotalSales = _TotalSales;
}
}
}
if (val.StartsWith("Current Population"))
{
if (excelSheet.Cells[i, 2].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 2].Value.ToString(), out int _CurrentPopulation))
{
vhsAOR.CurrentPopulation = _CurrentPopulation;
}
}
if (excelSheet.Cells[i, 4].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 4].Value.ToString(), out int _C5YrsProyectedPopulation))
{
vhsAOR.C5YrsProyectedPopulation = _C5YrsProyectedPopulation;
}
}
}
if (val.StartsWith("Current Households"))
{
if (excelSheet.Cells[i, 2].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 2].Value.ToString(), out int _CurrentHouseholds))
{
vhsAOR.CurrentHouseholds = _CurrentHouseholds;
}
}
if (excelSheet.Cells[i, 4].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 4].Value.ToString(), out int _C5YrsProyectedHouseholds))
{
vhsAOR.C5YrsProyectedHouseholds = _C5YrsProyectedHouseholds;
}
}
}
if (val.StartsWith("Current HH Under $50,000"))
{
if (excelSheet.Cells[i, 2].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 2].Value.ToString(), out int _CurrentHouseholdsUnder50))
{
vhsAOR.CurrentHouseholdsUnder50 = _CurrentHouseholdsUnder50;
}
}
if (excelSheet.Cells[i, 4].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 4].Value.ToString(), out int C5YrsProyectedHouseholdsUnder50))
{
vhsAOR.C5YrsProyectedHouseholdsUnder50 = C5YrsProyectedHouseholdsUnder50;
}
}
}
if (val.StartsWith("Current HH $50,000 - $100,000"))
{
if (excelSheet.Cells[i, 2].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 2].Value.ToString(), out int _CurrentHouseholds50to100))
{
vhsAOR.CurrentHouseholds50to100 = _CurrentHouseholds50to100;
}
}
if (excelSheet.Cells[i, 4].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 4].Value.ToString(), out int _C5YrsProyectedHouseholds50to100))
{
vhsAOR.C5YrsProyectedHouseholds50to100 = _C5YrsProyectedHouseholds50to100;
}
}
}
if (val.StartsWith("Current HH Over $100,000"))
{
if (excelSheet.Cells[i, 2].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 2].Value.ToString(), out int _CurrentHouseholdsOver100))
{
vhsAOR.CurrentHouseholdsOver100 = _CurrentHouseholdsOver100;
}
}
if (excelSheet.Cells[i, 4].Value2 != null)
{
if (int.TryParse(excelSheet.Cells[i, 4].Value.ToString(), out int _C5YrsProyectedHouseholdsOver100))
{
vhsAOR.C5YrsProyectedHouseholdsOver100 = _C5YrsProyectedHouseholdsOver100;
}
}
}
if (valLineBefore.Contains("DlrNum") && !val.StartsWith("Current Population"))
{
vhsAORDealerHistory = new AORDealerHistory();
vhsAORDealerHistory.AORName = vhsAOR.AORName;
vhsAORDealerHistory.DealerCode = "0" + excelSheet.Cells[i, 1].Value.ToString();
vhsAORDealerHistory.DealerName = excelSheet.Cells[i, 2].Value.ToString();
vhsAORDealerHistory.DealerCity = excelSheet.Cells[i, 3].Value.ToString();
if (excelSheet.Cells[i, 4].Value2 != null)
{
if (DateTime.TryParse(excelSheet.Cells[i, 4].Value.ToString(), out DateTime _ApptDate))
{
vhsAORDealerHistory.ApptDate = _ApptDate;
}
}
if (excelSheet.Cells[i, 5].Value2 != null)
{
if (DateTime.TryParse(excelSheet.Cells[i, 5].Value.ToString(), out DateTime _TermDate))
{
vhsAORDealerHistory.TermDate = _TermDate;
}
}
}
}
if (val.StartsWith("DlrNum"))
{
valLineBefore = val;
}
if (valLineBefore.StartsWith("DlrNum") && val.StartsWith("Current Population"))
{
valLineBefore = string.Empty;
}
if (valLineBefore == string.Empty && val.StartsWith("Dealer Code and Name"))
{
valLineBefore = "Dealer Code and Name";
}
if (valLineBefore == "Dealer Code and Name" && val.StartsWith("AOR"))
{
valLineBefore = string.Empty;
}
if (vhsAOR != null)
{
listAOR.Add(vhsAOR);
}
if (vhsAORDealerHistory != null)
{
listAORH.Add(vhsAORDealerHistory);
vhsAORDealerHistory = null;
}
if (vhsCrossSellDealerAOR != null)
{
listAORCrossSale.Add(vhsCrossSellDealerAOR);
}
}
}
}
object misValue = System.Reflection.Missing.Value;
wb.Close(false, misValue, misValue);
excel.Quit();
using (SDCManagementSystem_Test3Entities db = new SDCManagementSystem_Test3Entities())
{
foreach (AOR dealer in listAOR)
{
if (dealer.AORName != string.Empty)
{
AOR vhs = db.AORs.Where(
x => x.AORName == dealer.AORName).FirstOrDefault();
if (vhs != null)
{ //modify record
vhs.CurrentPopulation = dealer.CurrentPopulation;
vhs.C5YrsProyectedPopulation = dealer.C5YrsProyectedPopulation;
vhs.CurrentHouseholds = dealer.CurrentHouseholds;
vhs.C5YrsProyectedHouseholds = dealer.C5YrsProyectedHouseholds;
vhs.CurrentHouseholdsUnder50 = dealer.CurrentHouseholdsUnder50;
vhs.C5YrsProyectedHouseholdsUnder50 = dealer.C5YrsProyectedHouseholdsUnder50;
vhs.CurrentHouseholds50to100 = dealer.CurrentHouseholds50to100;
vhs.C5YrsProyectedHouseholds50to100 = dealer.C5YrsProyectedHouseholds50to100;
vhs.CurrentHouseholdsOver100 = dealer.CurrentHouseholdsOver100;
vhs.C5YrsProyectedHouseholdsOver100 = dealer.C5YrsProyectedHouseholdsOver100;
updatedRow++;
}
else
{
db.AORs.Add(dealer);
newRows++;
}
}
else
{
db.AORs.Add(dealer);
newRows++;
}
}
try
{
db.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
foreach (AORDealerHistory dealer in listAORH)
{
if (dealer.AORName != null && dealer.AORName != string.Empty)
{
AORDealerHistory vhs = db.AORDealerHistories.Where(
x => x.AORName == dealer.AORName).FirstOrDefault();
if (vhs != null)
{ //modify record
vhs.DealerCode = dealer.DealerCode;
vhs.DealerName = dealer.DealerName;
vhs.DealerCity = dealer.DealerCity;
vhs.ApptDate = dealer.ApptDate;
vhs.TermDate = dealer.TermDate;
updatedRow++;
}
else
{
db.AORDealerHistories.Add(dealer);
newRows++;
}
}
}
try
{
db.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
foreach (CrossSellDealerAOR dealer in listAORCrossSale)
{
if (dealer.Dealer_id != 0)
{
CrossSellDealerAOR vhs = db.CrossSellDealerAORs.Where(
x => x.Dealer_id == dealer.Dealer_id && x.SalesFrom == dealer.SalesFrom && x.SalesThrough == dealer.SalesThrough).FirstOrDefault();
if (vhs != null)
{ //modify record
vhs.TotalSales = dealer.TotalSales;
vhs.DealerCode = dealer.DealerCode;
vhs.DealerName = dealer.DealerName;
vhs.DealerCity = dealer.DealerCity;
vhs.DealerSaler = dealer.DealerSaler;
vhs.pctAORSales = dealer.pctAORSales;
updatedRow++;
}
else
{
db.CrossSellDealerAORs.Add(dealer);
newRows++;
}
}
else
{
db.CrossSellDealerAORs.Add(dealer);
newRows++;
}
}
try
{
db.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
} //End of Entity
result.Add("The " + filePath + " file was uploaded: New Row added: " + newRows.ToString() + " Row updated: " + updatedRow.ToString() + Environment.NewLine);
} // End If File>exits
#endregion
return(result);
}