public static DataTable GetTableFromExcelRecords(string ExcelFilePath, string StoreName, string UserName)
{
DataTable OriginalFile = ImportExceltoDatatable(ExcelFilePath);
string SQLString = "SELECT TOP 0 [PAYOUTsalesTemp].* FROM [PAYOUTsalesTemp] WHERE 1 = 2;";
DataTable salesTempColumnOrderedFile = Queries.GetResultsFromQueryString(SQLString);
SQLString = "SELECT TOP 0 [PAYOUTscheduleTemp].* FROM [PAYOUTscheduleTemp] WHERE 1 = 2;";
DataTable scheduleTempColumnOrderedFile = Queries.GetResultsFromQueryString(SQLString);
DataTable dt;
foreach (DataColumn dc in OriginalFile.Columns)
{
dc.ColumnName = dc.ColumnName.Trim();
}
//DataColumn dc = new DataColumn()
//ColumnOrderedFile.Columns.Add
switch (StoreName)
{
case "CanadianTire":
foreach (DataRow dr in OriginalFile.Rows)
{
DataRow drOrdered = salesTempColumnOrderedFile.NewRow();
drOrdered["SalesDate"] = dr["Date"];
drOrdered["StoreName"] = StoreName;
drOrdered["StoreNumber"] = dr["Store"];
drOrdered["ItemNumber"] = dr["Item"];
drOrdered["ItemName"] = dr["Product"];
drOrdered["Qty"] = dr["Qty"];
drOrdered["UnitCost"] = dr["Unit PP"];
drOrdered["ExtendedCost"] = dr["Total Sales"];
salesTempColumnOrderedFile.Rows.Add(drOrdered);
}
break;
case "WinnDixie":
foreach (DataRow dr in OriginalFile.Rows)
{
DataRow drOrdered = salesTempColumnOrderedFile.NewRow();
drOrdered["SalesDate"] = dr["Division"];
drOrdered["StoreName"] = StoreName;
drOrdered["StoreNumber"] = dr["Store Number"];
drOrdered["ItemNumber"] = dr["Item Number"];
drOrdered["ItemName"] = dr["Description"];
drOrdered["Qty"] = dr["QS - Gross Sales"];
drOrdered["UnitCost"] = dr["Cost"];
drOrdered["ExtendedCost"] = dr["Extended Cost"];
salesTempColumnOrderedFile.Rows.Add(drOrdered);
}
break;
case "Meijer":
foreach (DataRow dr in OriginalFile.Rows)
{
DataRow drOrdered = salesTempColumnOrderedFile.NewRow();
drOrdered["SalesDate"] = dr["Date"];
drOrdered["StoreName"] = StoreName;
drOrdered["StoreNumber"] = dr["Store"];
drOrdered["ItemNumber"] = dr["Item"];
drOrdered["ItemName"] = dr["Product"];
drOrdered["Qty"] = dr["Qty"];
drOrdered["UnitCost"] = dr["Total Sales"];
drOrdered["ExtendedCost"] = dr["Unit PP"];
salesTempColumnOrderedFile.Rows.Add(drOrdered);
}
break;
case "SamCreditCard":
foreach (DataRow dr in OriginalFile.Rows)
{
DataRow drOrdered = salesTempColumnOrderedFile.NewRow();
drOrdered["SalesDate"] = dr["Date"];
drOrdered["StoreName"] = StoreName;
drOrdered["StoreNumber"] = dr["Store"];
drOrdered["ItemNumber"] = dr["Item"];
drOrdered["ItemName"] = dr["Product"];
drOrdered["Qty"] = dr["Qty"];
drOrdered["UnitCost"] = dr["Total Sales"];
drOrdered["ExtendedCost"] = dr["Unit PP"];
salesTempColumnOrderedFile.Rows.Add(drOrdered);
}
break;
case "SamCypressCreek":
foreach (DataRow dr in OriginalFile.Rows)
{
DataRow drOrdered = salesTempColumnOrderedFile.NewRow();
drOrdered["SalesDate"] = dr["Date"];
drOrdered["StoreName"] = StoreName;
drOrdered["StoreNumber"] = dr["Store"];
drOrdered["ItemNumber"] = dr["Item"];
drOrdered["ItemName"] = dr["Product"];
drOrdered["Qty"] = dr["Qty"];
drOrdered["UnitCost"] = dr["Total Sales"];
drOrdered["ExtendedCost"] = dr["Unit PP"];
salesTempColumnOrderedFile.Rows.Add(drOrdered);
}
break;
case "SamZeroEnergy":
foreach (DataRow dr in OriginalFile.Rows)
{
DataRow drOrdered = salesTempColumnOrderedFile.NewRow();
drOrdered["SalesDate"] = dr["Date"];
drOrdered["StoreName"] = StoreName;
drOrdered["StoreNumber"] = dr["Store"];
drOrdered["ItemNumber"] = dr["Item"];
drOrdered["ItemName"] = dr["Product"];
drOrdered["Qty"] = dr["Qty"];
drOrdered["UnitCost"] = dr["Total Sales"];
drOrdered["ExtendedCost"] = dr["Unit PP"];
salesTempColumnOrderedFile.Rows.Add(drOrdered);
}
break;
case "HEB":
foreach (DataRow dr in OriginalFile.Rows)
{
DataRow drOrdered = salesTempColumnOrderedFile.NewRow();
drOrdered["SalesDate"] = dr["Receipt date"];
drOrdered["StoreName"] = StoreName;
drOrdered["StoreNumber"] = dr["Location ID"];
drOrdered["ItemNumber"] = dr["UPC"];
drOrdered["ItemName"] = dr["Description"];
drOrdered["Qty"] = dr["Final quantity paid"];
drOrdered["UnitCost"] = dr["HEB cost"];
drOrdered["ExtendedCost"] = dr["Final extended"];
salesTempColumnOrderedFile.Rows.Add(drOrdered);
}
break;
case "Costco":
foreach (DataRow dr in OriginalFile.Rows)
{
DataRow drOrdered = salesTempColumnOrderedFile.NewRow();
drOrdered["SalesDate"] = dr["sales-date"];
drOrdered["StoreName"] = StoreName;
drOrdered["StoreNumber"] = dr["warehouse"];
drOrdered["ItemNumber"] = dr["item"];
drOrdered["ItemName"] = dr["item-description"];
drOrdered["Qty"] = dr["quanitity"];
drOrdered["UnitCost"] = dr["unit-cost"];
drOrdered["ExtendedCost"] = dr["extended-cost"];
salesTempColumnOrderedFile.Rows.Add(drOrdered);
}
break;
case "Kroger":
dt = Queries.GetResultsFromQueryString("SELECT RegionId, Region FROM PAYOUTkrogerRegions");
foreach (DataRow dr in OriginalFile.Rows)
{
//Get kroger Region Id
string result = Regex.Match(dr["RPT_SHORT_DESC"].ToString(), @"\d+").ToString();
//Find region from table krogerRegions by Id
result = dt.AsEnumerable()
.Where(f => f.Field <string>("RegionId") == result)
.Select(f => f.Field <string>("Region")).FirstOrDefault();
DataRow drOrdered = salesTempColumnOrderedFile.NewRow();
drOrdered["SalesDate"] = dr["DATE_KEY"];
drOrdered["StoreName"] = string.Format("{0} - {1}", StoreName, result);
drOrdered["StoreNumber"] = dr["RE_STO_NUM"];
drOrdered["ItemNumber"] = dr["ITM_SCN_CD"];
drOrdered["ItemName"] = dr["ITEM_DESCRIPTION"];
drOrdered["Qty"] = dr["SCAN_UNITS"];
drOrdered["UnitCost"] = dr["AVE_UNIT_PRC"];
drOrdered["ExtendedCost"] = dr["SCAN_DOLLARS"];
salesTempColumnOrderedFile.Rows.Add(drOrdered);
}
break;
case "KrogerC":
dt = Queries.GetResultsFromQueryString("SELECT RegionId, Region FROM PAYOUTkrogerRegions");
foreach (DataRow dr in OriginalFile.Rows)
{
//Find region from table krogerRegions by Id
string result = dt.AsEnumerable()
.Where(f => f.Field <string>("RegionId") == dr["Div"].ToString())
.Select(f => f.Field <string>("Region")).FirstOrDefault();
DataRow drOrdered = salesTempColumnOrderedFile.NewRow();
drOrdered["SalesDate"] = dr["Date"];
drOrdered["StoreName"] = string.Format("{0} - {1}", StoreName, result);
drOrdered["StoreNumber"] = dr["Store"];
drOrdered["ItemNumber"] = dr["UPC"];
drOrdered["ItemName"] = dr["UPC Sell Unit Desc"];
drOrdered["Qty"] = dr["Qty Sold"];
drOrdered["UnitCost"] = "0";
drOrdered["ExtendedCost"] = dr["Sales"];
salesTempColumnOrderedFile.Rows.Add(drOrdered);
}
break;
case "Schedule":
foreach (DataRow dr in OriginalFile.Rows)
{
DataRow drOrdered = salesTempColumnOrderedFile.NewRow();
drOrdered["Program"] = dr["Programs"];
drOrdered["StartDate"] = dr["Start Date"];
drOrdered["EndDate"] = dr["End Date"];
drOrdered["StoreName"] = dr["Retailer"];
drOrdered["StoreNumber"] = dr["Club #"];
drOrdered["City"] = dr["City"];
drOrdered["State"] = dr["State"];
drOrdered["OwnerFirstname"] = dr["Payout Owner First Name"];
drOrdered["OwnerLastname"] = dr["Payout Owner Last Name"];
drOrdered["HubFirstname"] = dr["Payout HUB First Name"];
drOrdered["HubLastname"] = dr["Payout HUB Last Name"];
drOrdered["eventID"] = dr["eventID"];
drOrdered["ImportedOn"] = DateTime.Now.ToString();
drOrdered["Archive"] = "0";
scheduleTempColumnOrderedFile.Rows.Add(drOrdered);
}
for (int i = 0; i < scheduleTempColumnOrderedFile.Rows.Count; i++)
{
scheduleTempColumnOrderedFile.Rows[i]["ImportedBy"] = UserName;
}
break;
case "BJs":
foreach (DataRow dr in OriginalFile.Rows)
{
DataRow drOrdered = salesTempColumnOrderedFile.NewRow();
drOrdered["SalesDate"] = dr["Date"];
drOrdered["StoreName"] = StoreName;
drOrdered["StoreNumber"] = dr["Club"];
drOrdered["ItemNumber"] = dr["Article"];
drOrdered["ItemName"] = dr["Article Description"];
drOrdered["Qty"] = dr["Sales Qty"];
drOrdered["UnitCost"] = "0";
drOrdered["ExtendedCost"] = dr["Sales $"];
salesTempColumnOrderedFile.Rows.Add(drOrdered);
}
break;
case "Sams":
List <saleSamsWalmart> lstSalesS = SamsWalmartUnpivotSales(OriginalFile, true);
foreach (saleSamsWalmart objSales in lstSalesS)
{
DataRow drOrdered = salesTempColumnOrderedFile.NewRow();
drOrdered["SalesDate"] = objSales.salesDate;
drOrdered["StoreName"] = StoreName;
drOrdered["StoreNumber"] = objSales.warehouse;
drOrdered["ItemNumber"] = objSales.item;
drOrdered["ItemName"] = objSales.itemDescription;
drOrdered["Qty"] = objSales.quantity;
drOrdered["UnitCost"] = "0";
drOrdered["ExtendedCost"] = objSales.extendedCost;
salesTempColumnOrderedFile.Rows.Add(drOrdered);
}
break;
case "WalMart":
List <saleSamsWalmart> lstSalesW = SamsWalmartUnpivotSales(OriginalFile, true);
foreach (saleSamsWalmart objSales in lstSalesW)
{
DataRow drOrdered = salesTempColumnOrderedFile.NewRow();
drOrdered["SalesDate"] = objSales.salesDate;
drOrdered["StoreName"] = StoreName;
drOrdered["StoreNumber"] = objSales.warehouse;
drOrdered["ItemNumber"] = objSales.item;
drOrdered["ItemName"] = objSales.itemDescription;
drOrdered["Qty"] = objSales.quantity;
drOrdered["UnitCost"] = "0";
drOrdered["ExtendedCost"] = objSales.extendedCost;
salesTempColumnOrderedFile.Rows.Add(drOrdered);
}
break;
}
for (int i = 0; i < salesTempColumnOrderedFile.Rows.Count; i++)
{
salesTempColumnOrderedFile.Rows[i]["ImportedBy"] = UserName;
}
return(salesTempColumnOrderedFile);
}
private DataTable GetTable(ref Dictionary <string, string> Params)
{
Params["Action"] = "BULKSELECTTABLE";
return(Queries.GetResultsFromStoreProcedure("spx_PAYOUTsales", ref Params).Tables[0]);
}
