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]); }