protected void Page_Load(object sender, EventArgs e)
{
try
{
if (!IsPostBack)
{
ScreenPrivilegeData priData = RoleBiz.GetScreenPrivilege(HttpContext.Current.User.Identity.Name, "SLM_SCR_034");
if (priData == null || priData.IsView != 1)
{
AppUtil.ClientAlertAndRedirect(Page, "คุณไม่มีสิทธิ์เข้าใช้หน้าจอนี้", "SLM_SCR_003.aspx");
return;
}
((Label)Page.Master.FindControl("lblTopic")).Text = "นำเข้างานติดปัญหา";
Page.Form.Attributes.Add("enctype", "multipart/form-data");
Page.Form.DefaultButton = btnSearch.UniqueID;
cmbInsComImport.DataSource = SlmScr034Biz.GetImportInsComListData();
cmbInsComImport.DataBind();
cmbInsComSearch.DataSource = SlmScr034Biz.GetSearchInsComListData();
cmbInsComSearch.DataBind();
cmbFileNameSearch.DataSource = SlmScr034Biz.GetSearchFileNameListData();
cmbFileNameSearch.DataBind();
}
}
catch (Exception ex)
{
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
_log.Error(message);
AppUtil.ClientAlert(Page, message);
}
}
private void BuildCombo()
{
AppUtil.BuildCombo(cmbBranch, SlmScr010Biz.GetBranchData());
AppUtil.BuildCombo(cmbCardTypeId, CardTypeBiz.GetCardTypeList());
AppUtil.BuildCombo(cmbMaritalStatus, SlmScr046Biz.GetMaritalDataList());
AppUtil.BuildCombo(cmbOccupation, SlmScr010Biz.GetOccupationData(false));
var complst = SlmScr034Biz.GetImportInsComCodeListData();
//complst.RemoveAt(0);
AppUtil.BuildCombo(cmbCompulCompCode, complst);
AppUtil.BuildCombo(cmbVolCompanyCode, complst);
AppUtil.BuildCombo(cmbVolTypeKey, SlmScr046Biz.GetCoverageType());
var titlelist = SlmScr046Biz.GetTitleDataList();
AppUtil.BuildCombo(cmbTitleId, titlelist);
AppUtil.BuildCombo(cmbBenTitleId, titlelist);
AppUtil.BuildCombo(cmbVolMktTitle, titlelist);
AppUtil.BuildCombo(cmbDrv1TitleId, titlelist);
AppUtil.BuildCombo(cmbDrv2TitleId, titlelist);
AppUtil.BuildCombo(cmbBenTitleId, titlelist);
AppUtil.BuildCombo(cmbGuar1Title, titlelist);
AppUtil.BuildCombo(cmbGuar2Title, titlelist);
AppUtil.BuildCombo(cmbGuar3Title, titlelist);
AppUtil.BuildCombo(cmbBrandCode, SlmScr010Biz.GetBrandDataNew());
AppUtil.BuildCombo(cmbProvinceRegis, SlmScr046Biz.GetProvinceDataId());
AppUtil.BuildCombo(cmbCarByGovId, SlmScr046Biz.GetInsuranceTypeData());
var relate = SlmScr046Biz.GetRelationData();
AppUtil.BuildCombo(cmbGuar1Relation, relate);
AppUtil.BuildCombo(cmbGuar2Relation, relate);
AppUtil.BuildCombo(cmbGuar3Relation, relate);
}
protected void btnConfirmImport_Click(object sender, EventArgs e)
{
try
{
DataTable dt = null;
decimal insComId = -1;
string fileName = "";
if (Session["ImportTable"] != null)
{
dt = Session["ImportTable"] as DataTable;
}
if (Session["insComId"] != null)
{
decimal.TryParse(Session["insComId"].ToString(), out insComId);
}
if (Session["fileName"] != null)
{
fileName = Session["fileName"].ToString();
}
if (fileName != "" && insComId > 0)
{
SlmScr034Biz.DeleteImportedProblem(insComId, fileName, DateTime.Today);
if (DoImport(dt, insComId, fileName))
{
// AppUtil.ClientAlert(Page, "นำเข้าข้อมูลงานติดปัญหาเรียบร้อย");
btnSearch_Click(null, null);
cmbFileNameSearch.DataSource = SlmScr034Biz.GetSearchFileNameListData();
cmbFileNameSearch.DataBind();
AppUtil.ClientAlert(this, "นำเข้าข้อมูลเรียบร้อย");
}
else
{
AppUtil.ClientAlert(this, "ข้อมูลไม่ถูกนำเข้า กรุณาตรวจสอบข้อผิดพลาด");
}
}
mpePopupConfirm.Hide();
upnPopupImport.Update();
mpePopupImport.Show();
//clearSession();
}
catch (Exception ex)
{
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
_log.Error(message);
AppUtil.ClientAlert(Page, message);
}
finally
{
fuData.ClearAllFilesFromPersistedStore();
}
}
protected void btnSearch_Click(object sender, EventArgs e)
{
try
{
var result = SlmScr034Biz.GetSearchResult(cmbInsComSearch.SelectedValue, cmbFileNameSearch.SelectedValue);
BindGridview(pcTop, result.ToArray(), 0);
}
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)
{
tableUploadResult.Visible = false;
gvUploadError.Visible = false;
gvUploadErrorHeader.Visible = false;
if (cmbInsComImport.SelectedIndex == 0)
{
lblCompError.Text = "กรุณาเลือกบริษัท";
fuData.ClearAllFilesFromPersistedStore();
mpePopupImport.Show();
return;
}
if (fuData.HasFile)
{
var ext = Path.GetExtension(fuData.FileName).ToLower();
if (ext != ".xls")
{
lblUploadError.Text = "กรุณาระบุไฟล์ให้ถูก format (xls)";
fuData.ClearAllFilesFromPersistedStore();
mpePopupImport.Show();
return;
}
try
{
using (OleDbConnection conn = new OleDbConnection())
{
DataTable dt = new DataTable();
string filename = Path.GetTempFileName();
lblFilename.Text = fuData.FileName;
fuData.SaveAs(filename);
//fuData.ClearAllFilesFromPersistedStore();
conn.ConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;IMEX=0;'", filename);
conn.Open();
DataTable dbSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string firstSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
//cmd.CommandText = "SELECT * FROM [Sheet1$]";
cmd.CommandText = "SELECT * FROM [" + firstSheetName + "]";
OleDbDataAdapter adp = new OleDbDataAdapter(cmd);
adp.Fill(dt);
adp.Dispose();
cmd.Dispose();
decimal insComId = cmbInsComImport.SelectedValue == "" ? -1 : decimal.Parse(cmbInsComImport.SelectedValue);
// check duplicate
bool hasDup = SlmScr034Biz.HasDuplicate(insComId, fuData.FileName, DateTime.Today);
if (hasDup)
{
Session["ImportTable"] = dt;
Session["insComId"] = insComId;
Session["fileName"] = fuData.FileName;
mpePopupImport.Show();
mpePopupConfirm.Show();
}
else
{
if (DoImport(dt, insComId, fuData.FileName))
{
// AppUtil.ClientAlert(Page, "นำเข้าข้อมูลงานติดปัญหาเรียบร้อย"); -- change to display result on popup instead
btnSearch_Click(null, null);
cmbFileNameSearch.DataSource = SlmScr034Biz.GetSearchFileNameListData();
cmbFileNameSearch.DataBind();
AppUtil.ClientAlert(this, "นำเข้าข้อมูลเรียบร้อย");
}
else
{
AppUtil.ClientAlert(this, "ข้อมูลไม่ถูกนำเข้า กรุณาตรวจสอบข้อผิดพลาด");
}
}
}
}
catch (Exception ex)
{
// if (ex.Message.Contains("Ticket Id มากกว่า 1 รายการ "))
string message = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
_log.Error(message);
AppUtil.ClientAlert(Page, message);
}
finally
{
fuData.ClearAllFilesFromPersistedStore();
}
}
else
{
lblUploadError.Text = "กรุณาระบุไฟล์ที่ต้องการนำเข้า";
}
// lblUploadError.Text = "after upload";
mpePopupImport.Show();
}
protected void PageSearchChange(object sender, EventArgs e)
{
var result = SlmScr034Biz.GetSearchResult(cmbInsComSearch.SelectedValue, cmbFileNameSearch.SelectedValue);
BindGridview(pcTop, result.ToArray(), pcTop.SelectedPageIndex);
}
private bool DoImport(DataTable dt, decimal insComId, string fileName)
{
SlmProblemData data = new SlmProblemData();
try
{
data.ProblemDetails = SlmScr034Biz.ValidateProblemDetails(dt, cmbInsComImport.SelectedItem.Text);
if (data.ProblemDetails.Count > 0 &&
data.ProblemDetails.Where(p => p.hasError) != null &&
data.ProblemDetails.Where(p => p.hasError).Count() > 0)
{
gvUploadError.DataSource = data.ProblemDetails.Where(p => p.hasError).ToList();
gvUploadError.DataBind();
//lblFilename.Text = fileName;
lblFail.Text = data.ProblemDetails.Where(p => p.hasError).Count().ToString("#,##0");
lblSuccess.Text = "0";
lblTotal.Text = data.ProblemDetails.Count.ToString("#,##0");// lblFail.Text;
tableUploadResult.Visible = true;
gvUploadErrorHeader.Visible = true;
gvUploadError.Visible = true;
return(false);
}
else
{
data.Ins_Com_Id = insComId;
data.FileName = fileName;
data.Deleted = false;
data.UpdatedBy = HttpContext.Current.User.Identity.Name;
data.UpdatedDate = DateTime.Now;
SlmScr034Biz.SaveProblemData(data);
// บันทึกลง CAR System
// CreateCASActivityLog(data);
var staff = StaffBiz.GetStaff(HttpContext.Current.User.Identity.Name);
data.ProblemDetails.Where(ticket => ticket.needCARprocess)
.Select(t => new
{
TicketId = t.TicketId
,
RenewInsureId = t.RenewInsureId
,
IsPolicyPurchase = t.IsPolicyPurchase
,
IsActPurchase = t.IsActPurchase
,
PolicyRecAmt = t.PolicyRecAmt
,
ActRecAmt = t.ActRecAmt
,
PolicyLastRecAmt = (decimal?)null
,
ActLastRecAmt = (decimal?)null
})
.Distinct()
.ToList()
.ForEach(ticket =>
{
AppUtil.CreateCASActivityLog(this, ticket.TicketId, ticket.RenewInsureId, ticket.IsPolicyPurchase, ticket.IsActPurchase, ticket.PolicyLastRecAmt, ticket.ActLastRecAmt, ticket.PolicyRecAmt, ticket.ActRecAmt, staff);
});
//lblFilename.Text = fileName;
lblFail.Text = "0";
lblSuccess.Text = data.ProblemDetails.Count.ToString("#,##0");
lblTotal.Text = lblSuccess.Text;
tableUploadResult.Visible = true;
gvUploadErrorHeader.Visible = false;
gvUploadError.Visible = true;
gvUploadError.DataSource = null;
gvUploadError.DataBind();
return(true);
}
}
catch (Exception ex)
{
if (ex.Message.Contains("Ticket Id มากกว่า 1 รายการ "))
{
_log.Error(ex.Message);
AppUtil.ClientAlert(Page, ex.Message);
}
else
{
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
_log.Error(message);
AppUtil.ClientAlert(Page, message);
}
return(false);
}
}
