protected void PageSearchChange(object sender, EventArgs e)
{
try
{
var pageControl = (SLM.Application.Shared.GridviewPageController)sender;
if (!string.IsNullOrEmpty(hdfUploadLeadId.Value))
{
UploadLeadBiz biz = new UploadLeadBiz();
var allData = biz.GetUploadListById(int.Parse(hdfUploadLeadId.Value));
if (allData != null)
{
BindGridview(pageControl, allData.LeadDataList.ToArray(), pageControl.SelectedPageIndex);
}
else
{
AppUtil.ClientAlertAndRedirect(Page, string.Format("ไม่พบข้อมูลการ Upload Lead, UploadId={0}", hdfUploadLeadId.Value), SearchPage);
return;
}
}
else
{
AppUtil.ClientAlertAndRedirect(Page, "UploadId not found", SearchPage);
return;
}
}
catch (Exception ex)
{
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
_log.Error(message);
AppUtil.ClientAlert(Page, message);
}
}
private bool LoadLead(string uploadLeadId)
{
try
{
UploadLeadBiz biz = new UploadLeadBiz();
var allData = biz.GetUploadListById(int.Parse(uploadLeadId));
if (allData != null)
{
hdfExistingFilename.Value = allData.UploadFileName;
txtFileName.Text = allData.UploadFileName;
cmbChannel.SelectedIndex = cmbChannel.Items.IndexOf(cmbChannel.Items.FindByValue(allData.ChannelId));
cmbCampaign.SelectedIndex = cmbCampaign.Items.IndexOf(cmbCampaign.Items.FindByValue(allData.CampaignId));
DoBindGridview(allData.LeadDataList, 0);
SetStateControlButton(true, true, false);
return(true);
}
else
{
ErrorMessage = biz.ErrorMessage;
RedirectToSearch = biz.RedirectToSearch;
return(false);
}
}
catch
{
throw;
}
}
private void DoSearchRanking(string uploadleadid)
{
UploadLeadData uploadLead = UploadLeadBiz.SearchUploadLeadData(uploadleadid);
hidUploadLeadId.Value = uploadLead.slm_UploadLeadId.ToString();
hidLeadCount.Value = uploadLead.slm_LeadCount.ToString();
txtFileName.Text = uploadLead.slm_FileName.ToString();
hidFileName.Value = uploadLead.slm_FileName.ToString();
cmbCampaign.SelectedValue = uploadLead.slm_CampaignCode;
}
protected override void OnInit(EventArgs e)
{
base.OnInit(e);
((Label)Page.Master.FindControl("lblTopic")).Text = "ค้นหา Upload Lead";
var statuss = UploadLeadBiz.GetStatusList();
cmbStatus.DataSource = statuss;
cmbStatus.DataValueField = "ValueField";
cmbStatus.DataTextField = "TextField";
cmbStatus.DataBind();
cmbStatus.Items.Insert(0, new ListItem("", "0"));
}
private void DoSearchData(int pageIndex)
{
try
{
UploadLeadBiz biz = new UploadLeadBiz();
var result = biz.SearchData(txtFilename.Text.Trim(), cmbFileStatus.SelectedItem.Value);
BindGridview(pcTop, result.ToArray(), pageIndex);
}
catch
{
throw;
}
}
private void InitialControl()
{
UploadLeadBiz biz = new UploadLeadBiz();
cmbCampaign.DataSource = biz.GetSaleAndBothCampaignList();
cmbCampaign.DataTextField = "TextField";
cmbCampaign.DataValueField = "ValueField";
cmbCampaign.DataBind();
cmbCampaign.Items.Insert(0, new ListItem("", ""));
cmbChannel.DataSource = biz.GetChannelList();
cmbChannel.DataTextField = "TextField";
cmbChannel.DataValueField = "ValueField";
cmbChannel.DataBind();
cmbChannel.Items.Insert(0, new ListItem("", ""));
}
/// <summary>
/// ปุ่ม Download ทำงานในกรณี Edit เท่านั้น โดย download ข้อมูลที่อยู่ใน DB แล้ว Export ออกเป็น excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnDownload_Click(object sender, EventArgs e)
{
try
{
if (!string.IsNullOrEmpty(hdfUploadLeadId.Value))
{
UploadLeadBiz biz = new UploadLeadBiz();
var allData = biz.GetUploadListById(int.Parse(hdfUploadLeadId.Value));
if (allData != null && allData.LeadDataList != null)
{
//ExportExcelOpenXml(allData);
ExportExcel(allData);
return;
}
else
{
WriteLogFile(hdfUploadLeadId.Value, biz.ErrorMessage);
if (biz.RedirectToSearch)
{
PrepareRedirectPage();
AppUtil.ClientAlertAndRedirect(Page, biz.ErrorMessage, SearchPage);
return;
}
else
{
AppUtil.ClientAlert(Page, biz.ErrorMessage);
return;
}
}
}
else
{
PrepareRedirectPage();
WriteLogFile("None", "Upload Id not found");
AppUtil.ClientAlertAndRedirect(Page, "Upload Id not found", SearchPage);
return;
}
}
catch (Exception ex)
{
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
_log.Error(message);
AppUtil.ClientAlert(Page, message);
}
}
private void DoSearchUploadLead(int pageIndex)
{
try
{
SearchUploadLeadCondition search = new SearchUploadLeadCondition();
search.slm_FileName = txtFileName.Text.Trim();
search.slm_Status = cmbStatus.SelectedItem.Value.Trim();
search.UploadedDateForm = tdmFormDatePopup.DateValue.Year.ToString() + tdmFormDatePopup.DateValue.ToString("-MM-dd");
search.UploadedDateTo = tdmToDatePopup.DateValue.Year.ToString() + tdmToDatePopup.DateValue.ToString("-MM-dd");
List <SearchUploadLeadResult> list = UploadLeadBiz.SearchUploadLeadData(search);
BindGridview(pcTop, list.ToArray(), pageIndex);
//upResult.Update();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// ปุ่มลบทำงานในกรณี Edit เท่านั้น โดยลบข้อมูลที่อยู่ใน DB แล้ว redirect กลับไปสู่หน้าค้นหา
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnDelete_Click(object sender, EventArgs e)
{
try
{
if (!string.IsNullOrEmpty(hdfUploadLeadId.Value))
{
UploadLeadBiz biz = new UploadLeadBiz();
var ret = biz.DeleteUpload(int.Parse(hdfUploadLeadId.Value), HttpContext.Current.User.Identity.Name);
if (!ret)
{
if (biz.RedirectToView)
{
PrepareRedirectPage();
WriteLogFile(hdfUploadLeadId.Value, biz.ErrorMessage);
AppUtil.ClientAlertAndRedirect(Page, biz.ErrorMessage, GetViewPageUrl(hdfUploadLeadId.Value));
return;
}
else if (biz.RedirectToSearch)
{
PrepareRedirectPage();
WriteLogFile(hdfUploadLeadId.Value, biz.ErrorMessage);
AppUtil.ClientAlertAndRedirect(Page, biz.ErrorMessage, SearchPage);
return;
}
else
{
WriteLogFile(hdfUploadLeadId.Value, biz.ErrorMessage);
AppUtil.ClientAlert(Page, biz.ErrorMessage);
return;
}
}
PrepareRedirectPage();
AppUtil.ClientAlertAndRedirect(Page, "ลบข้อมูลเรียบร้อย", SearchPage);
}
}
catch (Exception ex)
{
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
_log.Error(message);
AppUtil.ClientAlert(Page, message);
}
}
private void InitialControl()
{
pcTop.SetVisible = false;
SetStateControlButton(false, false, false);
Session.Remove(SessionUploadLead);
UploadLeadBiz biz = new UploadLeadBiz();
cmbCampaign.DataSource = biz.GetSaleAndBothCampaignList();
cmbCampaign.DataTextField = "TextField";
cmbCampaign.DataValueField = "ValueField";
cmbCampaign.DataBind();
cmbCampaign.Items.Insert(0, new ListItem("", ""));
cmbChannel.DataSource = biz.GetChannelList();
cmbChannel.DataTextField = "TextField";
cmbChannel.DataValueField = "ValueField";
cmbChannel.DataBind();
cmbChannel.Items.Insert(0, new ListItem("", ""));
}
private void DoSearchDetail(int pageIndex)
{
try
{
//UploadLeadBiz biz = new UploadLeadBiz();
List <UploadLeadDetailData> list = UploadLeadBiz.GetUploadLeadDetailList(hidUploadLeadId.Value.Trim());
ViewState["Result"] = list;
gvError.Visible = false;
gvResult.Visible = true;
pcTopError.Visible = false;
pcTop.Visible = true;
BindGridview(gvResult, pcTop, list.ToArray(), 0);
}
catch (Exception ex)
{
throw ex;
}
}
protected void PageSearchChange(object sender, EventArgs e)
{
try
{
var pageControl = (SLM.Application.Shared.GridviewPageController)sender;
if (Session[SessionUploadLead] != null)
{
//มี Session เกิดจากการ Browse
System.Threading.Thread.Sleep(400);
DoBindGridview((List <UploadLeadData>)Session[SessionUploadLead], pageControl.SelectedPageIndex);
}
else
{
if (!string.IsNullOrEmpty(hdfUploadLeadId.Value)) //Edit
{
UploadLeadBiz biz = new UploadLeadBiz();
var allData = biz.GetUploadListById(int.Parse(hdfUploadLeadId.Value));
if (allData != null)
{
BindGridview(pageControl, allData.LeadDataList.ToArray(), pageControl.SelectedPageIndex);
}
else
{
AppUtil.ClientAlertAndRedirect(Page, string.Format("ไม่พบข้อมูลการ Upload Lead, UploadId={0}", hdfUploadLeadId.Value), SearchPage);
return;
}
}
else
{
AppUtil.ClientAlertAndRedirect(Page, "Session has expired", SearchPage);
return;
}
}
}
catch (Exception ex)
{
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
_log.Error(message);
AppUtil.ClientAlert(Page, message);
}
}
protected void btnDeleteAll_Click(object sender, EventArgs e)
{
try
{
//if (UploadLeadBiz.CheckDeleteUploadLead(hidUploadLeadId.Value))
//{
UploadLeadBiz.DeleteUploadLead(AppUtil.SafeInt(hidUploadLeadId.Value), HttpContext.Current.User.Identity.Name);
AppUtil.ClientAlertAndRedirect(Page, "บันทึกข้อมูล UploadLead สำเร็จ", "SLM_SCR_101.aspx");
//}
//else
//{
// AppUtil.ClientAlert(Page, "ข้อมูลที่เลือกไม่สามารถลบได้");
//}
}
catch (Exception ex)
{
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
_log.Debug(message);
AppUtil.ClientAlert(Page, message);
}
}
private bool CheckPrivilege(string uploadLeadId)
{
try
{
bool ret = true;
UploadLeadBiz biz = new UploadLeadBiz();
if (!biz.CanEdit(int.Parse(uploadLeadId)))
{
ErrorMessage = biz.ErrorMessage;
RedirectToSearch = biz.RedirectToSearch;
RedirectToView = biz.RedirectToView;
ret = false;
}
return(ret);
}
catch
{
throw;
}
}
protected void btnSaveAll_Click(object sender, EventArgs e)
{
try
{
if (ValidateData())
{
UploadLeadData data = new UploadLeadData();
data.slm_UploadLeadId = AppUtil.SafeInt(hidUploadLeadId.Value);
data.slm_LeadCount = AppUtil.SafeInt(hidLeadCount.Value);
data.slm_Status = "Submit";
//data.FileName = txtFileName.Text;
data.slm_FileName = hidFileName.Value;
data.slm_CampaignCode = cmbCampaign.SelectedItem.Value;
if (hidUploadLeadId.Value == "")
{
UploadLeadBiz.AddUploadLead(data, (List <UploadLeadDetailData>)ViewState["Result"], HttpContext.Current.User.Identity.Name);
}
else
{
UploadLeadBiz.EditUploadLead(data, (List <UploadLeadDetailData>)ViewState["Result"], HttpContext.Current.User.Identity.Name);
}
AppUtil.ClientAlertAndRedirect(Page, "บันทึกข้อมูล UploadLead สำเร็จ", "SLM_SCR_101.aspx");
}
else
{
AppUtil.ClientAlert(Page, "กรุณาระบุข้อมูลให้ครบถ้วน");
}
}
catch (Exception ex)
{
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
_log.Debug(message);
AppUtil.ClientAlert(Page, message);
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
try
{
if (Session[SessionUploadLead] != null)
{
var dataList = Session[SessionUploadLead] as List <UploadLeadData>;
UploadAllData allData = new UploadAllData
{
ChannelId = cmbChannel.SelectedItem.Value,
CampaignId = cmbCampaign.SelectedItem.Value,
UploadFileName = hdfFilename.Value,
LeadDataList = dataList,
CreateByUsername = HttpContext.Current.User.Identity.Name
};
UploadLeadBiz biz = new UploadLeadBiz();
int uploadLeadId = 0;
if (string.IsNullOrEmpty(hdfUploadLeadId.Value))
{
uploadLeadId = biz.SaveNewUpload(allData);
}
else
{
uploadLeadId = int.Parse(hdfUploadLeadId.Value);
var ret = biz.SaveUpdateUpload(uploadLeadId, allData);
if (!ret)
{
if (biz.RedirectToView)
{
PrepareRedirectPage();
WriteLogFile(uploadLeadId.ToString(), biz.ErrorMessage);
AppUtil.ClientAlertAndRedirect(Page, biz.ErrorMessage, GetViewPageUrl(hdfUploadLeadId.Value));
return;
}
else if (biz.RedirectToSearch)
{
PrepareRedirectPage();
WriteLogFile(uploadLeadId.ToString(), biz.ErrorMessage);
AppUtil.ClientAlertAndRedirect(Page, biz.ErrorMessage, SearchPage);
return;
}
else
{
WriteLogFile(uploadLeadId.ToString(), biz.ErrorMessage);
AppUtil.ClientAlert(Page, biz.ErrorMessage);
return;
}
}
}
PrepareRedirectPage();
AppUtil.ClientAlertAndRedirect(Page, "บันทึกข้อมูลเรียบร้อย", string.Format("SLM_SCR_056.aspx?uploadleadid={0}", uploadLeadId.ToString()));
}
else
{
PrepareRedirectPage();
WriteLogFile("None", "Session Expired, Page will be reloaded");
AppUtil.ClientAlertAndRedirect(Page, "Session Expired, Page will be reloaded", Request.Url.AbsoluteUri);
}
}
catch (Exception ex)
{
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
_log.Error(message);
AppUtil.ClientAlert(Page, message);
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
try
{
//if (cmbCampaign.SelectedValue == "0") throw new Exception("กรุณา Campaign");
if (fuLead.HasFile)
{
var ext = Path.GetExtension(fuLead.FileName).ToLower();
if (ext != ".xls")
{
lblUploadError.Text = "กรุณาระบุไฟล์ให้ถูกต้อง (.xls)";
txtFileName.Text = hidFileName.Value;
return;
}
using (OleDbConnection conn = new OleDbConnection())
{
DataTable dt = new DataTable();
string filename = Path.GetFileName(fuLead.FileName);
string fullname = Server.MapPath(WebConfigurationManager.AppSettings["UploadLeadPath"] + filename);
if (hidUploadLeadId.Value != "")
{
if (filename != hidFileName.Value)
{
txtFileName.Text = hidFileName.Value;
AppUtil.ClientAlert(this, "ชื่อไฟล์ต้องตรงกับชื่อไฟล์เดิม");
return;
}
}
else if (UploadLeadBiz.CheckFileExist(filename, AppUtil.SafeInt(hidUploadLeadId.Value)))
{
//vtxtName.Text = "ชื่อลำดับที่นี้มีอยู่แล้วในระบบแล้ว";
AppUtil.ClientAlert(this, "ไฟล์นี้มีอยู่แล้วในระบบแล้ว");
txtFileName.Text = hidFileName.Value;
return;
}
CreateFolderIfNeeded(Server.MapPath(WebConfigurationManager.AppSettings["UploadLeadPath"]));
fuLead.SaveAs(fullname);
conn.ConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No;IMEX=1;'", fullname);
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM [Sheet1$]";
OleDbDataAdapter adp = new OleDbDataAdapter(cmd);
adp.Fill(dt);
List <ControlListData> errLst = new List <ControlListData>();
// check error
List <UploadLeadDetailData> UploadLeadDetailDatas = new List <UploadLeadDetailData>();
if ((dt.Rows.Count <= 3001))
{
for (int i = 1; i < dt.Rows.Count; i++)
{
var row = dt.Rows[i];
string clmer = "";
try
{
// check item
if (row[0].ToString().Trim() == "")
{
clmer += (clmer == "" ? "" : ",<br/>") + "Column A: กรุณาระบุ สาขาของสัญญา ";
}
if (row[1].ToString().Trim() == "")
{
clmer += (clmer == "" ? "" : ",<br/>") + "Column B: กรุณาระบุ Owner lead";
}
if (row[2].ToString().Trim() == "")
{
clmer += (clmer == "" ? "" : ",<br/>") + "Column C: กรุณาระบุ ชื่อลูกค้า";
}
if (row[3].ToString().Trim() == "")
{
clmer += (clmer == "" ? "" : ",<br/>") + "Column D: กรุณาระบุ นามสกุลลูกค้า";
}
if (row[4].ToString().Trim() == "")
{
clmer += (clmer == "" ? "" : ",<br/>") + "Column E: กรุณาระบุ ประเภทลูกค้า";
}
if (row[5].ToString().Trim() == "")
{
clmer += (clmer == "" ? "" : ",<br/>") + "Column F: กรุณาระบุ เลขที่บัตร";
}
if (row[5].ToString().Trim().Length > 13)
{
clmer += (clmer == "" ? "" : ",<br/>") + "Column F: เลขที่บัตรประชาชน/นิติบุคคล ไม่ถูกต้อง";
}
if (row[6].ToString().Trim() == "")
{
clmer += (clmer == "" ? "" : ",<br/>") + "Column G: หมายเลขโทรศัพท์ 1(มือถือ)";
}
if (row[7].ToString().Trim() == "")
{
clmer += (clmer == "" ? "" : ",<br/>") + "Column H: หมายเลขโทรศัพท์ 2";
}
if (row[8].ToString().Trim() == "")
{
clmer += (clmer == "" ? "" : ",<br/>") + "Column I: หมายเลขโทรศัพท์ 3";
}
if (row[9].ToString().Trim() == "")
{
clmer += (clmer == "" ? "" : ",<br/>") + "Column J: ยี่ห้อรถ";
}
if (row[10].ToString().Trim() == "")
{
clmer += (clmer == "" ? "" : ",<br/>") + "Column K: รุ่นรถ";
}
if (row[11].ToString().Trim() == "")
{
clmer += (clmer == "" ? "" : ",<br/>") + "Column L: ปีรถ";
}
if (row[12].ToString().Trim() == "")
{
clmer += (clmer == "" ? "" : ",<br/>") + "Column M: B-SCORE";
}
if (row[13].ToString().Trim() == "")
{
clmer += (clmer == "" ? "" : ",<br/>") + "Column N: Remark";
}
if (row[14].ToString().Trim() == "")
{
clmer += (clmer == "" ? "" : ",<br/>") + "Column O: Ticket ID";
}
}
catch (Exception ex)
{
errLst.Add(new ControlListData()
{
TextField = ex.Message, ValueField = (i + 1).ToString()
});
}
if (clmer != "")
{
errLst.Add(new ControlListData()
{
TextField = clmer, ValueField = (i + 1).ToString()
});
}
else
{
UploadLeadDetailData uploadleaddetaildata = new UploadLeadDetailData();
uploadleaddetaildata.slm_ContractBranchName = row[0].ToString().Trim();
uploadleaddetaildata.slm_OwnerLead = row[1].ToString().Trim();
uploadleaddetaildata.slm_ThaiFirstName = row[2].ToString().Trim();
uploadleaddetaildata.slm_ThaiLastName = row[3].ToString().Trim();
uploadleaddetaildata.slm_CardIdType = row[4].ToString().Trim();
uploadleaddetaildata.slm_CardId = row[5].ToString().Trim();
uploadleaddetaildata.slm_CustTelephoneMobile = row[6].ToString().Trim();
uploadleaddetaildata.slm_CustTelephoneHome = row[7].ToString().Trim();
uploadleaddetaildata.slm_CustTelephoneOther = row[8].ToString().Trim();
uploadleaddetaildata.slm_BrandName = row[9].ToString().Trim();
uploadleaddetaildata.slm_ModelName = row[10].ToString().Trim();
uploadleaddetaildata.slm_ModelYear = row[11].ToString().Trim();
uploadleaddetaildata.slm_HpBscodeXsell = row[12].ToString().Trim();
uploadleaddetaildata.slm_Remark = row[13].ToString().Trim();
uploadleaddetaildata.slm_TicketID = row[14].ToString().Trim();
if (UploadLeadDetailDatas.Where(u => u.slm_CardId == row[5].ToString().Trim()).Count() == 0)
{
UploadLeadDetailDatas.Add(uploadleaddetaildata);
}
else
{
errLst.Add(new ControlListData()
{
TextField = "เลขที่บัตรประชาชน/นิติบุคคล " + row[5].ToString().Trim() + " ซ้ำ", ValueField = (i + 1).ToString()
});
}
}
}
if (errLst.Count > 0)
{
// show error
//gvError.DataSource = errLst;
//gvError.DataBind();
BindGridview(gvError, pcTopError, errLst.ToArray(), 0);
ViewState["Error"] = errLst;
AppUtil.ClientAlert(this, "upload ไม่สำเร็จ");
gvError.Visible = true;
gvResult.Visible = false;
pcTopError.Visible = true;
pcTop.Visible = false;
}
else
{
if (UploadLeadDetailDatas.Count == 0)
{
AppUtil.ClientAlert(this, "ไม่พบข้อมูลในไฟล์");
}
else
{
// save data
gvError.Visible = false;
gvResult.Visible = true;
pcTopError.Visible = false;
pcTop.Visible = true;
ViewState["Result"] = UploadLeadDetailDatas;
hidLeadCount.Value = UploadLeadDetailDatas.Count().ToString();
BindGridview(gvResult, pcTop, UploadLeadDetailDatas.ToArray(), 0);
txtFileName.Text = filename;
hidFileName.Value = filename;
AppUtil.ClientAlert(this, "Upload Complete");
}
}
}
else
{
AppUtil.ClientAlert(this, "upload ไม่สำเร็จ ข้อมูลต้องไม่มากกว่า 3000 Record");
}
}
}
}
catch (Exception ex)
{
AppUtil.ClientAlert(this, ex.Message);
}
}
private bool ReadExcel(string fileName, out List <UploadLeadData> dataList, out List <UploadLeadDataError> errorList)
{
IExcelDataReader excelReader = null;
try
{
dataList = new List <UploadLeadData>();
errorList = new List <UploadLeadDataError>();
string ext = System.IO.Path.GetExtension(fileName);
excelReader = ext == ".xls" ? ExcelReaderFactory.CreateBinaryReader(fuLead.FileContent) : ExcelReaderFactory.CreateOpenXmlReader(fuLead.FileContent);
excelReader.IsFirstRowAsColumnNames = true;
int rowNum = 0;
int maxRow = SLMConstant.UploadLead.UploadLeadMaxRow;
StringBuilder sb = new StringBuilder();
//Regex regxSpecialChr = new Regex(@"[`]"); //@"[~`!@#$%^&*()+=|\\{}':;.,<>/?[\]""_-]"
Regex regxEng = new Regex(@"[a-zA-Z]"); //@"([\-ก-๙0-9()., ]+)"
UploadLeadBiz biz = new UploadLeadBiz();
var cardTypeList = biz.GetCardTypeList();
bool isHeader = true;
while (excelReader.Read())
{
if (isHeader)
{
isHeader = false;
continue;
}
//errText = "";
sb.Clear();
rowNum += 1;
if (rowNum > maxRow)
{
ErrorMessage = string.Format("Excel file มีข้อมูลเกิน {0} rows", maxRow);
return(false);
}
string nameThai = excelReader.GetString((int)ExcelColumnName.Firstname);
string lastnameThai = excelReader.GetString((int)ExcelColumnName.Lastname);
string cardtypeDesc = excelReader.GetString((int)ExcelColumnName.CardTypeDesc);
string citizenId = excelReader.GetString((int)ExcelColumnName.CitizenId);
string ownerEmpCode = excelReader.GetString((int)ExcelColumnName.OwnerEmpCode);
string delegateEmpCode = excelReader.GetString((int)ExcelColumnName.DelegateEmpCode);
string telNo1 = excelReader.GetString((int)ExcelColumnName.TelNo1);
string telNo2 = excelReader.GetString((int)ExcelColumnName.TelNo2);
string detail = excelReader.GetString((int)ExcelColumnName.Detail);
nameThai = string.IsNullOrWhiteSpace(nameThai) ? "" : nameThai.Trim();
lastnameThai = string.IsNullOrWhiteSpace(lastnameThai) ? "" : lastnameThai.Trim();
cardtypeDesc = string.IsNullOrWhiteSpace(cardtypeDesc) ? "" : cardtypeDesc.Trim();
citizenId = string.IsNullOrWhiteSpace(citizenId) ? "" : citizenId.Trim();
ownerEmpCode = string.IsNullOrWhiteSpace(ownerEmpCode) ? "" : ownerEmpCode.Trim();
delegateEmpCode = string.IsNullOrWhiteSpace(delegateEmpCode) ? "" : delegateEmpCode.Trim();
telNo1 = string.IsNullOrWhiteSpace(telNo1) ? "" : telNo1.Trim();
telNo2 = string.IsNullOrWhiteSpace(telNo2) ? "" : telNo2.Trim();
detail = string.IsNullOrWhiteSpace(detail) ? "" : detail.Trim();
if (string.IsNullOrWhiteSpace(nameThai) && string.IsNullOrWhiteSpace(lastnameThai) && string.IsNullOrWhiteSpace(cardtypeDesc) &&
string.IsNullOrWhiteSpace(citizenId) && string.IsNullOrWhiteSpace(ownerEmpCode) && string.IsNullOrWhiteSpace(delegateEmpCode) &&
string.IsNullOrWhiteSpace(telNo1) && string.IsNullOrWhiteSpace(telNo2) && string.IsNullOrWhiteSpace(detail))
{
//row ว่าง, skip ไป row ถัดไป
continue;
}
//ชื่อลูกค้า
if (string.IsNullOrWhiteSpace(nameThai))
{
sb.Append("Column A : กรุณาระบุชื่อลูกค้า<br />");
}
else
{
if (regxEng.IsMatch(nameThai))
{
sb.Append("Column A : กรุณาระบุชื่อลูกค้าเป็นภาษาไทย<br />");
}
else if (nameThai.Trim().Length > SLMConstant.UploadLead.FieldFirstNameSize)
{
sb.AppendFormat("Column A : ชื่อลูกค้ายาวเกิน {0} ตัวอักษร<br />", SLMConstant.UploadLead.FieldFirstNameSize.ToString());
}
}
//นามสกุลลูกค้า
if (!string.IsNullOrWhiteSpace(lastnameThai))
{
if (regxEng.IsMatch(lastnameThai))
{
sb.Append("Column B : กรุณาระบุนามสกุลลูกค้าเป็นภาษาไทย<br />");
}
else if (lastnameThai.Trim().Length > SLMConstant.UploadLead.FieldLastNameSize)
{
sb.AppendFormat("Column B : นามสกุลลูกค้ายาวเกิน {0} ตัวอักษร<br />", SLMConstant.UploadLead.FieldLastNameSize.ToString());
}
}
//ประเภทลูกค้า
if (!string.IsNullOrWhiteSpace(cardtypeDesc) && !cardTypeList.Any(p => p.TextField == cardtypeDesc))
{
sb.Append("Column C : ประเภทลูกค้าไม่ถูกต้อง<br />");
}
//เลขที่บัตร
if (!string.IsNullOrWhiteSpace(citizenId))
{
var cardtype_id = cardTypeList.Where(p => p.TextField == cardtypeDesc).Select(p => p.ValueField).FirstOrDefault();
if (cardtype_id == AppConstant.CardType.Person)
{
if (!AppUtil.VerifyCitizenId(citizenId))
{
sb.Append("Column D : เลขบัตรประชาชนไม่ถูกต้อง<br />");
}
}
else
{
if (citizenId.Trim().Length > SLMConstant.UploadLead.FieldCardIdSize)
{
sb.AppendFormat("Column D : เลขนิติบุคคล/บุคคลต่างชาติ ยาวเกิน {0} ตัวอักษร<br />", SLMConstant.UploadLead.FieldCardIdSize.ToString());
}
}
}
//Validate ประเภทลูกค้า, เลขที่บัตร
if (!string.IsNullOrWhiteSpace(cardtypeDesc) && string.IsNullOrWhiteSpace(citizenId))
{
sb.Append("Column D : กรุณระบุบัตรประชาชน/นิติบุคคล<br />");
}
else if (string.IsNullOrWhiteSpace(cardtypeDesc) && !string.IsNullOrWhiteSpace(citizenId))
{
sb.Append("Column C : กรุณาระบุประเภทลูกค้า<br />");
}
//OwnerLead
if (string.IsNullOrWhiteSpace(ownerEmpCode))
{
sb.Append("Column E : กรุณระบุ Owner Lead ID<br />");
}
//DelegateLead No validate
//TelNo1
if (string.IsNullOrWhiteSpace(telNo1))
{
sb.Append("Column G : กรุณาระบุเบอร์โทร 1<br />");
}
else
{
//Validate TelNo1 with CardTypeDesc
string cardTypeId = cardTypeList.Where(p => p.TextField == cardtypeDesc).Select(p => p.ValueField).FirstOrDefault();
cardTypeId = string.IsNullOrEmpty(cardTypeId) ? "" : cardTypeId;
if (!AppUtil.ValidateTelNo1(cardTypeId, telNo1))
{
sb.Append("Column G : เบอร์โทร 1 ไม่ถูกต้อง<br />");
}
}
//TelNo2
if (!string.IsNullOrWhiteSpace(telNo2) && !AppUtil.ValidateTelNo2(telNo2))
{
sb.Append("Column H : เบอร์โทร 2 ไม่ถูกต้อง<br />");
}
//Detail
if (!string.IsNullOrWhiteSpace(detail))
{
if (detail.Length > SLMConstant.UploadLead.FieldDetailSize)
{
sb.AppendFormat("Column I : รายละเอียดยาวเกิน {0} ตัวอักษร<br />", SLMConstant.UploadLead.FieldDetailSize.ToString());
}
else
{
var ret = detail.ToCharArray().Any(c => (c >= 128 && c <= 3584));
if (ret)
{
sb.Append("Column I : ข้อมูลรายละเอียดมีอักขระพิเศษที่ไม่อนุญาตให้ใช้งาน<br />");
}
}
}
if (sb.Length > 0)
{
UploadLeadDataError err = new UploadLeadDataError
{
Row = (rowNum + 1).ToString(),
ErrorDetail = sb.ToString()
};
errorList.Add(err);
}
else
{
UploadLeadData data = new UploadLeadData
{
Firstname = nameThai,
Lastname = lastnameThai,
CardTypeDesc = cardtypeDesc,
CitizenId = citizenId,
OwnerEmpCode = ownerEmpCode,
DelegateEmpCode = delegateEmpCode,
TelNo1 = telNo1,
TelNo2 = telNo2,
Detail = detail,
StatusDesc = "-"
};
dataList.Add(data);
}
}
return(true);
}
catch
{
throw;
}
finally
{
if (excelReader != null)
{
excelReader.Close();
}
}
}