private void InitialControl() { var biz = new SlmScr040Biz(); cmbMonth.DataSource = biz.GetMonths(); cmbMonth.DataTextField = "TextField"; cmbMonth.DataValueField = "ValueField"; cmbMonth.DataBind(); cmbMonth.Items.Insert(0, new ListItem("", "")); cmbMonth.SelectedIndex = cmbMonth.Items.IndexOf(cmbMonth.Items.FindByValue(DateTime.Now.Month.ToString("00"))); int curr_year = DateTime.Now.Year; cmbYear.Items.Insert(0, new ListItem("", "")); cmbYear.Items.Insert(1, new ListItem((curr_year - 1).ToString(), (curr_year - 1).ToString())); cmbYear.Items.Insert(2, new ListItem(curr_year.ToString(), curr_year.ToString())); cmbYear.Items.Insert(3, new ListItem((curr_year + 1).ToString(), (curr_year + 1).ToString())); cmbYear.SelectedIndex = cmbYear.Items.IndexOf(cmbYear.Items.FindByValue(curr_year.ToString())); }
protected void btnUpload_Click(object sender, EventArgs e) { if (fuData.HasFile) { var ext = Path.GetExtension(fuData.FileName)?.ToLower(); if (ext != ".xls") { lblUploadError.Text = "กรุณาระบุไฟล์ให้ถูกต้อง (.xls)"; return; } try { string filename = Path.GetTempFileName(); fuData.SaveAs(filename); lblFilename.Text = fuData.FileName; fuData.ClearAllFilesFromPersistedStore(); pnlError.Visible = false; tbResult.Visible = false; using (OleDbConnection conn = new OleDbConnection()) { DataTable dt = new DataTable(); conn.ConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'", filename); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT * FROM [kk$]"; using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd)) { adp.Fill(dt); } List <ControlListData> errLst = new List <ControlListData>(); int maxRow = SLMConstant.ExcelMaxRowNotifyPremium; if (cmbImportType.SelectedItem.Value == "2" && dt.Rows.Count > maxRow) { errLst.Add(new ControlListData { TextField = $"ไม่สามารถนำเข้าข้อมูลแจ้งเบี้ยเกิน {maxRow.ToString()} rows ได้", ValueField = "File" }); dt.Clear(); } #region overall format validation if (dt.Columns.Count != Enum.GetNames(typeof(ExcelColumnName)).Length) { errLst.Add(new ControlListData { TextField = "จำนวนคอลัมน์ไม่ถูกต้อง กรุณาตรวจสอบไฟล์ฟอร์แมต", ValueField = "File" }); dt.Clear(); } #region validate order of seq : unwanted feature /* * int[] lstRunNo = dt.AsEnumerable() * .Where(row => row[(int) ExcelColumnName.RunNo - A1] != DBNull.Value) * .Select(row => row.Field<double>((int) ExcelColumnName.RunNo - A1)) * .Select(Convert.ToInt32) * .OrderBy(x => x) * .ToArray(); * int[] missingRunNo = lstRunNo.Length > 0 ? Enumerable.Range(lstRunNo.First(), lstRunNo.Last()).Except(lstRunNo).ToArray() : new int[] { }; * if ( * lstRunNo.Length > 0 && * ( * missingRunNo.Length > 0 || * lstRunNo.First() != 1 || * lstRunNo.Last() != lstRunNo.Max() || * lstRunNo.Length != dt.Rows.Count || * false * ) * ) * { * Debug.WriteLine($"RunNo missing: {string.Join(",", missingRunNo)}"); * errLst.Add(new ControlListData {TextField = "เลขลำดับไม่ถูกต้อง กรุณาตรวจสอบเลขลำดับ", ValueField = "File"}); * dt.Clear(); * } */ #endregion #endregion var biz = new SlmScr040Biz(); var lstTitle = biz.GetTitleList(); var lstComp = biz.GetCompanyNameList(); var lstRptype = biz.GetRepairTypeNameList(); var lstCoveragetype = biz.GetCoverageTypeNameList(); // clean and squash duplicate VIN, prepare policy information for error message (if needed) Dictionary <string, string[]> dictCleanedVIN = dt.AsEnumerable() .Select(row => new { Key = Clean(row.Field <string>((int)ExcelColumnName.CarVIN - A1) ?? ""), Value = new[] { row.Field <string>((int)ExcelColumnName.InsuranceCompanyName - SLM.Application.SLM_SCR_040.A1) ?? "", $"เบี้ยประกันรวมภาษี {row.Field<double?>((int) ExcelColumnName.GrossPremium - SLM.Application.SLM_SCR_040.A1)?.ToString("#,##0.00") ?? ""}", $"เบี้ยประกันรวมภาษี (พรบ.) {row.Field<double?>((int) ExcelColumnName.ActGrossPremium - SLM.Application.SLM_SCR_040.A1)?.ToString("#,##0.00") ?? ""}" } }) .GroupBy(x => x.Key, StringComparer.InvariantCultureIgnoreCase) .ToDictionary(x => x.Key, x => x.Select(y => string.Join(", ", y.Value)).ToArray()); List <SlmScr040Biz.notifypremiumdata> prLst = new List <SlmScr040Biz.notifypremiumdata>(); int total = 0; for (int i = 0; i < dt.Rows.Count; i++) { int j = i; Func <ExcelColumnName, string> row = delegate(ExcelColumnName colName) { object o = dt.Rows[j][(int)colName - A1]; if (o is DateTime) { return(((DateTime)o).ToString("dd/MM/yyyy", CultureInfo.InvariantCulture)); } return(o.ToString().Trim()); }; var clmer = new ErrorListString(); var company = row(ExcelColumnName.InsuranceCompanyName) == "" ? null : lstComp.FirstOrDefault(c => c.Name.Contains(row(ExcelColumnName.InsuranceCompanyName))); var coverage = lstCoveragetype.FirstOrDefault(c => c.CoverageTypeName.Equals(row(ExcelColumnName.InsuranceTypeName), StringComparison.InvariantCultureIgnoreCase)); // verify data if (dt.Rows[i].ItemArray.All(x => string.IsNullOrWhiteSpace(x.ToString()))) { continue; } total++; #region general empty check if (row(ExcelColumnName.InsuranceCompanyName) == "") { clmer.AddErrorDesc(ExcelColumnName.InsuranceCompanyName, "กรุณาระบุ ชื่อบริษัทประกัน"); } if (row(ExcelColumnName.InsuranceCompanyName) != "" && company == null) { clmer.AddErrorDesc(ExcelColumnName.InsuranceCompanyName, "ไม่พบข้อมูลชื่อบริษัทประกันในระบบ"); } if (row(ExcelColumnName.InsuranceCompanyName) != "" && company != null && company.IsDeleted) { clmer.AddErrorDesc(ExcelColumnName.InsuranceCompanyName, "ไม่สามารถ Upload ได้เนื่องบริษัทประกันภัยมีสถานะเป็นไม่ใช้งาน"); } if (row(ExcelColumnName.PolicyNo) == "") { clmer.AddErrorDesc(ExcelColumnName.PolicyNo, "กรุณาระบุ เลขที่กธ."); } if (row(ExcelColumnName.CustomerTitle) == "") { clmer.AddErrorDesc(ExcelColumnName.CustomerTitle, "กรุณาระบุ คำนำหน้าชื่อ"); } if (row(ExcelColumnName.CustomerName) == "") { clmer.AddErrorDesc(ExcelColumnName.CustomerName, "กรุณาระบุ ชื่อผู้เอาประกัน"); } // if (row(ExcelColumnName.CustomerLastName) == "") clmer.AddErrorDesc(ExcelColumnName.CustomerLastName, "กรุณาระบุ นามสกุลผู้เอาประกัน"); if (row(ExcelColumnName.InsuranceExpireDate) == "") { clmer.AddErrorDesc(ExcelColumnName.InsuranceExpireDate, "กรุณาระบุ วันที่หมดอายุกรมธรรม์"); } if (row(ExcelColumnName.InsuranceExpireDate) != "" && !CheckDate(row(ExcelColumnName.InsuranceExpireDate))) { clmer.AddErrorDesc(ExcelColumnName.InsuranceExpireDate, "รูปแบบวันที่ไม่ถูกต้อง (DD/MM/YYYY)"); } //if (row(ExcelColumnName.CarLicenseNo) == "") clmer.AddErrorDesc(ExcelColumnName.CarLicenseNo, "กรุณาระบุ ทะเบียน"); // if (row(ExcelColumnName.CarBrand) == "") clmer.AddErrorDesc(ExcelColumnName.CarBrand, "กรุณาระบุ ยี่ห้อ"); // if (row(ExcelColumnName.CarModel) == "") clmer.AddErrorDesc(ExcelColumnName.CarModel, "กรุณาระบุ รุ่น"); if (row(ExcelColumnName.CarVIN) == "") { clmer.AddErrorDesc(ExcelColumnName.CarVIN, "กรุณาระบุ VIN/Chassis Number"); } if (row(ExcelColumnName.InsuranceTypeName) == "") { clmer.AddErrorDesc(ExcelColumnName.InsuranceTypeName, "กรุณาระบุ ประเภทประกัน"); } if (row(ExcelColumnName.InsuranceTypeName) != "" && coverage == null) { clmer.AddErrorDesc(ExcelColumnName.InsuranceTypeName, "ไม่พบข้อมูลประเภทประกันในระบบ"); } if (row(ExcelColumnName.InsuranceTypeName) != "" && coverage != null && coverage.IsDeleted) { clmer.AddErrorDesc(ExcelColumnName.InsuranceTypeName, "ประเภทประกันมีสถานะไม่ใช้งาน"); } if (row(ExcelColumnName.SumInsured) == "") { clmer.AddErrorDesc(ExcelColumnName.SumInsured, "กรุณาระบุ ทุนประกันใหม่"); } if (row(ExcelColumnName.DiscountPercent) == "") { clmer.AddErrorDesc(ExcelColumnName.DiscountPercent, "กรุณาระบุ ส่วนลดประวัติ(%)"); } if (row(ExcelColumnName.DiscountAmount) == "") { clmer.AddErrorDesc(ExcelColumnName.DiscountAmount, "กรุณาระบุ ส่วนลดประวัติ(บาท)"); } if (row(ExcelColumnName.NetPremium) == "") { clmer.AddErrorDesc(ExcelColumnName.NetPremium, "กรุณาระบุ เบี้ยสุทธิ"); } if (row(ExcelColumnName.StampAmount) == "") { clmer.AddErrorDesc(ExcelColumnName.StampAmount, "กรุณาระบุ อากร"); } if (row(ExcelColumnName.VatAmount) == "") { clmer.AddErrorDesc(ExcelColumnName.VatAmount, "กรุณาระบุ ภาษี"); } if (row(ExcelColumnName.GrossPremium) == "") { clmer.AddErrorDesc(ExcelColumnName.GrossPremium, "กรุณาระบุ เบี้ยประกันรวมภาษี"); } //if (row(ExcelColumnName.ActNetPremium) == "") clmer.AddErrorDesc(ExcelColumnName.ActNetPremium, "กรุณาระบุ เบี้ยสุทธิ (พรบ.)"); //if (row(ExcelColumnName.ActStampAmount) == "") clmer.AddErrorDesc(ExcelColumnName.ActStampAmount, "กรุณาระบุ อากร (พรบ.)"); //if (row(ExcelColumnName.ActVatAmount) == "") clmer.AddErrorDesc(ExcelColumnName.ActVatAmount, "กรุณาระบุ ภาษี (พรบ.)"); if (row(ExcelColumnName.ActGrossPremium) == "") { clmer.AddErrorDesc(ExcelColumnName.ActGrossPremium, "กรุณาระบุ เบี้ยประกันรวมภาษี (พรบ.)"); } if (row(ExcelColumnName.DriverFlag) == "") { clmer.AddErrorDesc(ExcelColumnName.DriverFlag, "กรุณาระบุ ระบุชื่อ"); } if (row(ExcelColumnName.DriverFlag) != "" && !new[] { "Y", "N" }.Contains(row(ExcelColumnName.DriverFlag))) { clmer.AddErrorDesc(ExcelColumnName.DriverFlag, "กรุณากำหนด ระบุชื่อ ระหว่าง Y หรือ N เท่านั้น"); } #endregion #region valid repair type int rpTypeID = 0; var claims = new[] { row(ExcelColumnName.ClaimCenter), row(ExcelColumnName.ClaimGarage) }; if (claims.SequenceEqual(new[] { "", "" })) { clmer.AddErrorDesc(ExcelColumnName.ClaimCenter, ExcelColumnName.ClaimGarage, "กรุณาระบุ ซ่อมห้าง / ซ่อมอู่"); } else if (claims.SequenceEqual(new[] { "Y", "Y" })) { clmer.AddErrorDesc(ExcelColumnName.ClaimCenter, ExcelColumnName.ClaimGarage, "กรุณาเลือกระหว่าง ซ่อมห้าง / ซ่อมอู่"); } else if (claims.SequenceEqual(new[] { "Y", "" })) { rpTypeID = lstRptype.Where(x => x.RepairTypeName.Contains("ห้าง")).Select(x => x.RepairTypeID).FirstOrDefault(); if (rpTypeID == 0) { clmer.AddErrorDesc(ExcelColumnName.ClaimCenter, ExcelColumnName.ClaimGarage, "ไม่พบประเภทการซ่อมในระบบ"); } } else if (claims.SequenceEqual(new[] { "", "Y" })) { rpTypeID = lstRptype.Where(x => x.RepairTypeName.Contains("อู่")).Select(x => x.RepairTypeID).FirstOrDefault(); if (rpTypeID == 0) { clmer.AddErrorDesc(ExcelColumnName.ClaimCenter, ExcelColumnName.ClaimGarage, "ไม่พบประเภทการซ่อมในระบบ"); } } else { clmer.AddErrorDesc(ExcelColumnName.ClaimCenter, ExcelColumnName.ClaimGarage, "กรุณากำหนด ซ่อมห้าง / ซ่อมอู่ ระหว่าง Y หรือ ปล่อยว่าง เท่านั้น"); } #endregion #region VIN/Chassis duplicate check string cleanedVIN = Clean(row(ExcelColumnName.CarVIN)); if (dictCleanedVIN.Keys.Contains(cleanedVIN)) { string[] lstDupVIN = dictCleanedVIN[cleanedVIN]; if (lstDupVIN.Length > 1) { clmer.AddErrorDesc(ExcelColumnName.CarVIN, $"VIN/Chassis Number : {row(ExcelColumnName.CarVIN)} ซ้ำกันในไฟล์ดังนี้"); clmer.AddRange(lstDupVIN); } } #endregion #region valid period string month = ""; string year = ""; if (row(ExcelColumnName.Period) == "") { clmer.AddErrorDesc(ExcelColumnName.Period, "กรุณาระบุ Period"); } else if (row(ExcelColumnName.Period).Length != 6) { clmer.AddErrorDesc(ExcelColumnName.Period, "รูปแบบ Period ไม่ถูกต้อง (YYYYMM)"); } else { year = row(ExcelColumnName.Period).Substring(0, 4); month = row(ExcelColumnName.Period).Substring(4, 2); if (year != cmbYear.SelectedItem.Value || month != cmbMonth.SelectedItem.Value) { clmer.AddErrorDesc(ExcelColumnName.Period, "Period ไม่ตรงกับที่ระบุบนหน้าจอ"); } } #endregion if (clmer.Count > 0) { errLst.Add(new ControlListData { TextField = string.Join(",<br/>", clmer), ValueField = (i + 2).ToString() }); } else { // get title var tt = lstTitle.FirstOrDefault(t => t.Name == row(ExcelColumnName.CustomerTitle)); var np = new SlmScr040Biz.notifypremiumdata(); //np.slm_Contract_Number = row["B"].ToString(); Modified by Pom 21/03/2016 (Structure Changed) np.slm_RunNo = row(ExcelColumnName.RunNo); np.slm_PolicyNo = row(ExcelColumnName.PolicyNo); np.cell0 = "unused ?"; np.slm_Title_Id = tt?.ID ?? 0; np.slm_Name = row(ExcelColumnName.CustomerName); np.slm_LastName = row(ExcelColumnName.CustomerLastName); np.slm_Claim_Center = row(ExcelColumnName.ClaimCenter); np.slm_Claim_Garage = row(ExcelColumnName.ClaimGarage); np.slm_RepairTypeId = rpTypeID; np.slm_InsExpireDate = GetDateValue(row(ExcelColumnName.InsuranceExpireDate)); np.slm_CarLicenseNo = row(ExcelColumnName.CarLicenseNo); np.slm_Brand = row(ExcelColumnName.CarBrand); np.slm_Model = row(ExcelColumnName.CarModel); np.slm_VIN = row(ExcelColumnName.CarVIN); np.slm_InsuranceCarTypeId = coverage?.CoverageTypeID; np.slm_Sum_Insure = AppUtil.SafeDecimal(row(ExcelColumnName.SumInsured).Replace(",", "")); np.slm_Discount_Percent = AppUtil.SafeInt(row(ExcelColumnName.DiscountPercent).Replace(",", "")); np.slm_Discount_Amount = AppUtil.SafeDecimal(row(ExcelColumnName.DiscountAmount).Replace(",", "")); np.slm_NetPremium = AppUtil.SafeDecimal(row(ExcelColumnName.NetPremium).Replace(",", "")); np.slm_Stamp = AppUtil.SafeDecimal(row(ExcelColumnName.StampAmount).Replace(",", "")); np.slm_Vat_Amount = AppUtil.SafeDecimal(row(ExcelColumnName.VatAmount).Replace(",", "")); np.slm_GrossPremium = AppUtil.SafeDecimal(row(ExcelColumnName.GrossPremium).Replace(",", "")); np.slm_Remark = row(ExcelColumnName.Remark); np.slm_InsuranceComId = company?.ID; np.slm_PeriodMonth = month; np.slm_PeriodYear = year; np.slm_ActNetPremium = AppUtil.SafeDecimal(row(ExcelColumnName.ActNetPremium).Replace(",", "")); np.slm_ActStamp = AppUtil.SafeDecimal(row(ExcelColumnName.ActStampAmount).Replace(",", "")); np.slm_ActVat_Amount = AppUtil.SafeDecimal(row(ExcelColumnName.ActVatAmount).Replace(",", "")); np.slm_ActGrossPremium = AppUtil.SafeDecimal(row(ExcelColumnName.ActGrossPremium).Replace(",", "")); np.slm_DriverFlag = row(ExcelColumnName.DriverFlag); prLst.Add(np); } } if (errLst.Count <= 0) { bool ret; if (cmbImportType.SelectedItem.Value == "1") { ret = biz.InsertNotifyPremiumToTemp(prLst, System.Web.HttpContext.Current.User.Identity.Name, lblFilename.Text.ToLowerInvariant().Trim()); } else { ret = biz.InsertNotifyPremiumData(prLst, System.Web.HttpContext.Current.User.Identity.Name); } if (!ret) { throw new Exception(biz.ErrorMessage); } else { AppUtil.ClientAlert(Page, "นำเข้าข้อมูลเรียบร้อย"); } } else { // show error gvError.DataSource = errLst; gvError.DataBind(); pnlError.Visible = true; AppUtil.ClientAlert(Page, "ข้อมูลไม่ถูกนำเข้า กรุณาตรวจสอบข้อผิดพลาด"); } lblTotal.Text = total.ToString("#,##0"); lblSucc.Text = prLst.Count.ToString("#,##0"); lblFail.Text = (total - prLst.Count).ToString("#,##0"); tbResult.Visible = true; } } catch (Exception ex) { lblTotal.Text = lblSucc.Text = lblFail.Text = "0"; tbResult.Visible = true; var err = ex.InnerException == null ? ex.Message : ex.InnerException.Message; if (err.Contains("kk") && err.Contains("is not a valid")) { err = "ไม่พบข้อมูล Sheet kk"; } AppUtil.ClientAlert(Page, err); } } }