コード例 #1
0
 public static void PriceUpdate()
 {
     using (SDCManagementSystem_Test3Entities db = new SDCManagementSystem_Test3Entities())
     {
         try
         {
             db.spu_warehousePriceUpdate2();
             db.SaveChanges();
         }
         catch (Exception)
         {
             throw;
         }
     }
 }
コード例 #2
0
        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);
        }
コード例 #3
0
        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);
        }
コード例 #4
0
        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);
        }
コード例 #5
0
        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);
        }
コード例 #6
0
        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);
        }
コード例 #7
0
        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);
        }