private void BindGrid() { gvDenoChange.Columns[0].Visible = true; ProjectDenominators Pbusiness = new ProjectDenominators(); var list = Pbusiness.Find(); string month = GeneralUtility.ConvertMonthYearStringFormat(txtMonth.Text.Trim()); if (ddlPROJECT.SelectedValue != "All") { list = list.Where(x => x.DenoMonth == month && x.PROJECT == ddlPROJECT.SelectedValue).ToList(); } else { list = list.Where(x => x.DenoMonth == month).ToList(); } var reslist = from data in list select new { data.ID, data.PROJECT, data.Probes, data.Pricingprobes, data.Masks, data.Repricing, data.SceneRecog, data.ProbesperScene, data.Expert, DenoMonth = GeneralUtility.ConvertDisplayMonthStringFormat(data.DenoMonth), CreatedDate = GeneralUtility.ConvertDisplayDateStringFormat(data.CreatedDate), data.Createdby }; gvDenoChange.DataSource = reslist.ToList(); gvDenoChange.DataBind(); gvDenoChange.Columns[0].Visible = false; gvDenoChange.Columns[10].Visible = false; gvDenoChange.Columns[11].Visible = false; }
protected void btnQATImport_Click(object sender, EventArgs e) { var QATlist = new List <QATEntity>(); // NEED 1 (first) if (txtImportDate.Text != "" && txtImportDate.Text != null) { var center = ddlCenterName.SelectedValue.ToString(); var projectQAT = new QAT().FindByImportedDate(GeneralUtility.ConvertMonthYearStringFormat(txtImportDate.Text.Trim()), center); if (projectQAT.Count() == 0) { if (FileUpload1.HasFile) { if (Path.GetExtension(FileUpload1.FileName) == ".xlsx") { ExcelPackage package = new ExcelPackage(FileUpload1.FileContent); // NEED 2 (first) ExcelWorksheet workSheet = package.Workbook.Worksheets.First(); // NEED 2 (first) // workSheet.DeleteRow(1); // NEED 3 (first) QAT_BindBusiness(QATlist, workSheet, center); } } #region Save Probes (first) QAT itemBusiness = new QAT(); using (TransactionScope Scope = new TransactionScope()) { try { foreach (var v in QATlist) { itemBusiness.Entity = v; itemBusiness.Save(); } itemBusiness.ReplaceQAT(center); Scope.Complete(); MessageBox.MessageShow(this.GetType(), "QAT Import Successfully!", ClientScript); } catch (Exception ex) { Response.Redirect("error.aspx"); throw ex; } } #endregion } else { MessageBox.MessageShow(this.GetType(), "This Excel File has already been Imported!", ClientScript); } } else { MessageBox.MessageShow(this.GetType(), "Please Choose Import Date!.", ClientScript); } }
private void QAT_BindBusiness(List <QATEntity> QATlist, ExcelWorksheet ws, string center) { var userEntity = (UserEntity)Session["ID"]; for (var rowNumber = 1; rowNumber <= ws.Dimension.End.Row; rowNumber++) { var qatEntity = new QATEntity(); qatEntity.ID = GeneralUtility.GeneratedKey; #region Populate Excel data for (var colNumber = 1; colNumber <= ws.Dimension.End.Column; colNumber++) { // var columnName1 = ws.Cells[1, colNumber].Value.ToString().Trim(); try { if (true) { switch (colNumber) { #region case 1: qatEntity.QAT = Convert.ToString(ws.Cells[rowNumber, colNumber].Value); break; case 2: qatEntity.Name = Convert.ToString(ws.Cells[rowNumber, colNumber].Value); break; default: break; #endregion } } } catch (Exception ex) { Response.Redirect("error.aspx"); throw ex; } } qatEntity.CreatedDate = GeneralUtility.ConvertSystemDateStringFormat(txtImportDate.Text.Trim()); qatEntity.Month = GeneralUtility.ConvertMonthYearStringFormat(txtImportDate.Text); qatEntity.Center = ddlCenterName.SelectedValue.ToString(); qatEntity.Createdby = userEntity.ID; QATlist.Add(qatEntity); #endregion } }
protected void btnDenoImport_Click(object sender, EventArgs e) { var denolist = new List <ProjectDenominatorsEntity>(); // NEED 1 (first) if (txtImportDate.Text != "" && txtImportDate.Text != null) { var projectDeno = new ProjectDenominators().FindByImportedDate(GeneralUtility.ConvertMonthYearStringFormat(txtImportDate.Text.Trim())); if (projectDeno.Count() == 0) { if (FileUpload1.HasFile) { if (Path.GetExtension(FileUpload1.FileName) == ".xlsx") { ExcelPackage package = new ExcelPackage(FileUpload1.FileContent); // NEED 2 (first) ExcelWorksheet workSheet = package.Workbook.Worksheets.First(); // NEED 2 (first) workSheet.DeleteRow(1); // NEED 3 (first) Deno_BindBusiness(denolist, workSheet); // NEED 4 (first) } } #region Save Probes (first) ProjectDenominators itemBusiness = new ProjectDenominators(); using (TransactionScope Scope = new TransactionScope()) { try { foreach (var v in denolist) { itemBusiness.Entity = v; itemBusiness.Save(); } Scope.Complete(); MessageBox.MessageShow(this.GetType(), "Project Denominators Import Successfully!.", ClientScript); } catch (Exception ex) { Response.Redirect("error.aspx"); throw ex; } } #endregion } else { MessageBox.MessageShow(this.GetType(), "This Excel File has already been Imported!", ClientScript); } } else { MessageBox.MessageShow(this.GetType(), "Please Choose Import Date!.", ClientScript); } }
private void ProjectName_BindBusiness(List <ProjectNamesEntity> projectnamelist, ExcelWorksheet ws) { for (var rowNumber = 1; rowNumber <= 1; rowNumber++) { #region Populate Excel data for (var colNumber = 1; colNumber <= ws.Dimension.End.Column; colNumber++) { var projectnameEntity = new ProjectNamesEntity(); var columnName1 = ws.Cells[1, colNumber].Value.ToString().Trim(); // var columnName2 = string.Empty; try { if (true) { projectnameEntity.ID = GeneralUtility.GeneratedKey; projectnameEntity.PROJECT = columnName1; projectnameEntity.Sheet = ddlJobName.SelectedValue; projectnameEntity.Status = ddlStatus.SelectedValue; projectnameEntity.CreatedDate = GeneralUtility.ConvertSystemDateStringFormat(txtImportDate.Text.Trim());//GeneralUtility.ConvertSystemDateStringFormat(System.DateTime.Now); projectnameEntity.ImportMonth = GeneralUtility.ConvertMonthYearStringFormat(txtImportDate.Text); projectnameEntity.Createdby = "admin"; projectnamelist.Add(projectnameEntity); } } catch (Exception ex) { Response.Redirect("error.aspx"); throw ex; } } //if (rowNumber== 1) //{ // projectnameEntity.CreatedDate = GeneralUtility.ConvertSystemDateStringFormat(txtImportDate.Text.Trim());//GeneralUtility.ConvertSystemDateStringFormat(System.DateTime.Now); // projectnameEntity.ImportMonth = GeneralUtility.ConvertMonthYearStringFormat(txtImportDate.Text); // projectnameEntity.Createdby = "admin"; // projectnamelist.Add(projectnameEntity); //} #endregion } }
private void BindGrid() { gvRecheckAcc.Columns[0].Visible = true; AccuracyPercentage Pbusiness = new AccuracyPercentage(); var list = Pbusiness.Finds(); string month = GeneralUtility.ConvertMonthYearStringFormat(txtMonth.Text.Trim()); //string currentMonth=month.Substring(0, 6); list = list.Where(x => x.AccMonth == month && x.QAT == ddlQAT.SelectedValue).ToList(); var reslist = from data in list select new { data.ID, data.QAT, data.AccuracyPercent, data.Center, AccMonth = GeneralUtility.ConvertDisplayMonthStringFormat(data.AccMonth), CreatedDate = GeneralUtility.ConvertDisplayDateStringFormat(data.CreatedDate), data.Createdby }; gvRecheckAcc.DataSource = reslist.ToList(); gvRecheckAcc.DataBind(); gvRecheckAcc.Columns[0].Visible = false; //gvRecheckAcc.Columns[4].Visible = false; gvRecheckAcc.Columns[5].Visible = false; gvRecheckAcc.Columns[6].Visible = false; }
protected void btnSubmit_Click(object sender, EventArgs e) { if (!ValidateForm()) { BindGrid(); return; } using (TransactionScope scope = new TransactionScope()) { if (btnSubmit.Text != "Search") { try { var userEntity = (UserEntity)Session["ID"]; AccuracyPercentage Pbusiness = new AccuracyPercentage(); var entity = Pbusiness.FindDataByID(hdID.Value); if (entity != null) { new AccuracyPercentage { Entity = new AccuracyEntity { ID = hdID.Value, QAT = hdQAT.Value, Center = hdCenter.Value, AccuracyPercent = Convert.ToDecimal(txtAccuracy.Text), AccMonth = GeneralUtility.ConvertMonthYearStringFormat(txtMonth.Text.Trim()), Createdby = userEntity.ID, } }.Update(); } MessageBox.MessageShow(this.GetType(), "Successfully Updated.", ClientScript); btnSubmit.Text = "Search"; divAccuracy.Attributes.Add("style", "display:none"); BindGrid(); scope.Complete(); } catch (Exception ex) { throw ex; } } else { try { BindGrid(); } catch (Exception ex) { throw ex; } } } ddlQAT.Enabled = true; ddlCenterName.Enabled = true; ddlQAT.SelectedValue = "Select"; ddlQAT.SelectedIndex = 0; txtAccuracy.Text = ""; txtMonth.Text = ""; }
protected void btnExport_Click(object sender, EventArgs e) { if (txtMonth.Text != "" && txtMonth.Text != null) { #region "For Under 96%+5K" var branchcode = string.Empty; if (ddlCenterName.SelectedItem.Value != "All") { branchcode = ddlCenterName.SelectedValue.ToString(); } fromDate = GeneralUtility.ConvertSystemDateStringFormat(txtFromDate.Text); toDate = GeneralUtility.ConvertSystemDateStringFormat(txtToDate.Text); string fromYear = fromDate.Substring(0, 4); string fromMonth = fromDate.Substring(4, 2); string toYear = toDate.Substring(0, 4); string toMonth = toDate.Substring(4, 2); if (fromYear == toYear && fromMonth == toMonth) { Month1 = fromYear + fromMonth; Month2 = string.Empty; FromDate2 = string.Empty; ToDate2 = string.Empty; } else { //string comparedate = new AccuracyPercentage().FindMonthAndYear(fromDate); //string comparemonth = comparedate.Substring(3, 2); //string compareyear = comparedate.Substring(6, 4); string comparedate = new AccuracyPercentage().FindMonthAndYear(fromDate); DateTime fromtime = DateTime.Parse(comparedate); var fdate = GeneralUtility.ConvertSystemDateStringFormat(fromtime); string comparemonth = fdate.Substring(4, 2); string compareyear = fdate.Substring(0, 4); if (compareyear != toYear || comparemonth != toMonth) { MessageBox.MessageShow(this.GetType(), "Please Check FromDate and ToDate!.", ClientScript); return; } Month1 = fromYear + fromMonth; Month2 = compareyear + comparemonth; FromDate2 = Month2 + "01"; ToDate2 = toDate; toDate = new AccuracyPercentage().FindLastDayOfMonth(fromDate); } int probesQty = 7500; var probesqtyInfoEntity = new ProbesQty().FindQty(); if (probesqtyInfoEntity != null) { probesQty = probesqtyInfoEntity.Qty; } var finalAccuracyList = new AccuracyPercentage().FindByMonthForUnder96P(branchcode, Month1, probesQty, 96, fromDate, toDate, Month2, FromDate2, ToDate2); DataTable attTbl = new DataTable(); attTbl.Clear(); attTbl.Columns.Clear(); var result = (from dd in finalAccuracyList orderby dd.QAT select dd).ToList(); // Convert to DataTable. DataTable table = ConvertToDataTable(result); table.Columns.Remove("RQuality"); //table.Columns.Remove("AmountforProbes"); //table.Columns.Remove("AmountforAccuracy"); //table.Columns.Remove("PPPA"); //table.Columns.Remove("Center"); table.Columns.Remove("Month"); //int sum = finalProbesdt.AsEnumerable().Sum(s => s.Field<int>("Total Probes")); var yrm = GeneralUtility.ConvertMonthYearStringFormat(txtMonth.Text.Trim()); int yr = Convert.ToInt32(yrm.Substring(0, 4).ToString()); int mth = Convert.ToInt32(yrm.Substring(4, 2).ToString()); DateTime date = new DateTime(yr, mth, 1); var mm = date.ToString("MMMM"); var yy = date.ToString("yy"); if (result.Count().Equals(0)) { MessageBox.MessageShow(GetType(), "No Export Data.!", ClientScript); } else { var fileName = "AccuracyUnder96% " + mm + "'" + yy + ".xlsx"; int count = 0; Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; //Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("Probes_List_Export.xlsx", System.Text.Encoding.UTF8)); this.Response.AddHeader( "content-disposition", string.Format("attachment; filename={0}", fileName)); ExcelPackage pkg = new ExcelPackage(); using (pkg) { ExcelWorksheet ws = pkg.Workbook.Worksheets.Add("Under 96%"); ws.Cells["A1"].LoadFromDataTable(table, true); #region "No need region" // using (ExcelRange rng = ws.Cells["A1:W1"]) // using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count]) using (ExcelRange rng = ws.Cells[1, 1, 1, table.Columns.Count]) { rng.Style.Font.Bold = true; //Set Pattern for the background to Solid rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set color to dark blue rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189)); // rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(122,160,205)); rng.Style.Font.Color.SetColor(System.Drawing.Color.White); } //string PersentageCellFormat = "#0.00%"; //string PersentageCellFormat = "#0.00%"; //using (ExcelRange Rng = ws.Cells["D2"]) //{ // Rng.Style.Numberformat.Format = PersentageCellFormat; // // Rng.Value = Convert.ToDecimal(39.9); //} //ws.Cells[2, 4].Style.Numberformat.Format = "0.00\\%"; // ws.Cells[2, 4].Style.Numberformat.Format = "#0.00%"; // ws.Cells[2, 4].Style.Numberformat.Format = "#0\\.00%"; //ws.Cells["A1:MT"].Style.Font.Bold = true; ////ws.Cells["A1"].Style.Font.Bold = true; //using (ExcelRange rng = ws.Cells["A1:U" + (colcount - 1)]) //{ // rng.Style.Font.Bold = true; //} //var headerCell = ws.Cells["A5:MT"]; //headerCell.Style.Fill.PatternType = ExcelFillStyle.Solid; //headerCell.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.BurlyWood); //var headerFont = headerCell.Style.Font; //headerFont.Bold = true; //ws.Cells[1, 30].Style.Fill.PatternType = ExcelFillStyle.Solid; //ws.Cells[1, 30].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightBlue); //ws.Cells[1, 30].Style.VerticalAlignment = ExcelVerticalAlignment.Center; //ws.Cells[1, 30].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; #endregion if (result.Count() > 0) { count = result.Count() + 2; // pkg.Workbook.Worksheets.FirstOrDefault().Cells[count, 3].Value = sum;//result.Sum(x => x.TotalProbes); // pkg.Workbook.Worksheets.FirstOrDefault().Cells[count, 3].Style.Font.Bold = true; // pkg.Workbook.Worksheets.FirstOrDefault().Cells[count, 3].Style.Font.UnderLine = true; // pkg.Workbook.Worksheets.FirstOrDefault().Cells[count, 3].Style.Font.Color.SetColor(System.Drawing.Color.Blue); // pkg.Workbook.Worksheets.FirstOrDefault().Cells[count, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin); } pkg.Workbook.Worksheets.FirstOrDefault().DefaultColWidth = 20; pkg.Workbook.Worksheets.FirstOrDefault().Row(1).Height = 25; // using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count]) // var modelTable = pkg.Workbook.Worksheets.FirstOrDefault().Cells["A1:MP" + (count - 1)]; //+ (count - 1) var modelTable = pkg.Workbook.Worksheets.FirstOrDefault().Cells[ws.Dimension.Start.Row, 1, ws.Dimension.Start.Row + table.Rows.Count, table.Columns.Count]; //+ (count - 1) var border = modelTable.Style.Border.Top.Style = modelTable.Style.Border.Left.Style = modelTable.Style.Border.Right.Style = modelTable.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; pkg.Workbook.Properties.Title = "Attempts"; this.Response.BinaryWrite(pkg.GetAsByteArray()); this.Response.End(); } } // End Export Block #endregion // End Probes } else { MessageBox.MessageShow(this.GetType(), "Please Choose Export Date!.", ClientScript); } }
protected void btnSubmit_Click(object sender, EventArgs e) { if (!ValidateForm()) { return; } using (TransactionScope scope = new TransactionScope()) { if (btnSubmit.Text == "Submit") { try { var userEntity = (UserEntity)Session["ID"]; new ProbesRate { Entity = new RateEntity { ID = GeneralUtility.GeneratedKey, Rate1 = Convert.ToDecimal(txtRate1.Text), Rate2 = Convert.ToDecimal(txtRate2.Text), Rate3 = Convert.ToDecimal(txtRate3.Text), RatedYear = GeneralUtility.ConvertMonthYearStringFormat(txtMonth.Text.Trim()), Createdby = userEntity.ID, UpdatedBy = userEntity.ID, UpdatedDate = GeneralUtility.ConvertSystemDateStringFormat(System.DateTime.Now) } }.Save(); MessageBox.MessageShow(this.GetType(), "Successfully Save.", ClientScript); scope.Complete(); } catch (Exception ex) { throw ex; } } else { try { var userEntity = (UserEntity)Session["ID"]; new ProbesRate { Entity = new RateEntity { ID = ID = hdID.Value, Rate1 = Convert.ToDecimal(txtRate1.Text), Rate2 = Convert.ToDecimal(txtRate2.Text), Rate3 = Convert.ToDecimal(txtRate3.Text), RatedYear = GeneralUtility.ConvertMonthYearStringFormat(txtMonth.Text.Trim()), Createdby = userEntity.ID, UpdatedBy = userEntity.ID, UpdatedDate = GeneralUtility.ConvertSystemDateStringFormat(System.DateTime.Now), } }.Update(); MessageBox.MessageShow(this.GetType(), "Successfully Updated.", ClientScript); btnSubmit.Text = "Submit"; scope.Complete(); } catch (Exception ex) { throw ex; } } } txtRate1.Text = ""; txtRate2.Text = ""; txtRate3.Text = ""; txtMonth.Text = ""; BindGrid(); //if (btnSubmit.Text == "Submit") //{ // using (TransactionScope scope = new TransactionScope()) // { // try // { // var entity = new ProbesRate().FindRate(); // var userEntity = (UserEntity)Session["ID"]; // if (entity != null) // { // //ID = hdID.Value; // entity.Rate1 = Convert.ToInt32(txtRate1.Text); // entity.Rate2 = Convert.ToInt32(txtRate2.Text); // entity.Rate3 = Convert.ToInt32(txtRate3.Text); // entity.RatedYear = GeneralUtility.ConvertMonthYearStringFormat(txtMonth.Text.Trim()); // entity.Createdby = entity.Createdby; // entity.UpdatedBy = userEntity.ID; // entity.UpdatedDate = GeneralUtility.ConvertSystemDateStringFormat(System.DateTime.Now); // var business = new ProbesRate(); // business.Entity = entity; // business.Update(); // MessageBox.MessageShow(this.GetType(), "Successfully Updated.", ClientScript); // } // else // { // //string RYear=string.Empty; // //RYear = GeneralUtility.ConvertMonthYearStringFormat(txtMonth.Text.Trim()); // new ProbesRate // { // Entity = new RateEntity // { // ID = GeneralUtility.GeneratedKey, // Rate1 = Convert.ToInt32(txtRate1.Text), // Rate2 = Convert.ToInt32(txtRate2.Text), // Rate3 = Convert.ToInt32(txtRate3.Text), // RatedYear = GeneralUtility.ConvertMonthYearStringFormat(txtMonth.Text.Trim()), // Createdby = userEntity.ID, // UpdatedBy = userEntity.ID, // UpdatedDate = GeneralUtility.ConvertSystemDateStringFormat(System.DateTime.Now) // } // }.Save(); // MessageBox.MessageShow(this.GetType(), "Successfully Save.", ClientScript); // } // scope.Complete(); // } // catch (Exception ex) // { // throw ex; // } // } //} //else //{ // MessageBox.MessageShow(this.GetType(), "Please Choose Date!.", ClientScript); //} }
private void Deno_BindBusiness(List <ProjectDenominatorsEntity> denolist, ExcelWorksheet ws) { var userEntity = (UserEntity)Session["ID"]; for (var rowNumber = 1; rowNumber <= ws.Dimension.End.Row; rowNumber++) { var denoEntity = new ProjectDenominatorsEntity(); denoEntity.ID = GeneralUtility.GeneratedKey; #region Populate Excel data for (var colNumber = 1; colNumber <= ws.Dimension.End.Column; colNumber++) { // var columnName1 = ws.Cells[1, colNumber].Value.ToString().Trim(); try { if (true) { switch (colNumber) { #region case 1: denoEntity.PROJECT = Convert.ToString(ws.Cells[rowNumber, colNumber].Value); break; case 2: denoEntity.Probes = Convert.ToDecimal(Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == "" || Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == "-" || Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == null ? 0 : ws.Cells[rowNumber, colNumber].Value); break; case 3: denoEntity.Pricingprobes = Convert.ToDecimal(Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == "" || Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == "-" || Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == null ? 0 : ws.Cells[rowNumber, colNumber].Value); break; case 4: denoEntity.Masks = Convert.ToDecimal(Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == "" || Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == "-" || Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == null ? 0 : ws.Cells[rowNumber, colNumber].Value); break; case 5: denoEntity.Repricing = Convert.ToDecimal(Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == "" || Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == "-" || Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == null ? 0 : ws.Cells[rowNumber, colNumber].Value); break; case 6: denoEntity.SceneRecog = Convert.ToDecimal(Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == "" || Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == "-" || Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == null ? 0 : ws.Cells[rowNumber, colNumber].Value); break; case 7: denoEntity.Expert = Convert.ToDecimal(Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == "" || Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == "-" || Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == null ? 0 : ws.Cells[rowNumber, colNumber].Value); break; case 8: denoEntity.ProbesperScene = Convert.ToDecimal(Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == "" || Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == "-" || Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == null ? 0 : ws.Cells[rowNumber, colNumber].Value); break; //case 9: // denoEntity.AMERICAS_DEMO = Convert.ToInt32(Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == "" || Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == null ? 0 : ws.Cells[rowNumber, colNumber].Value); // break; default: break; #endregion } } } catch (Exception ex) { Response.Redirect("error.aspx"); throw ex; } } denoEntity.CreatedDate = GeneralUtility.ConvertSystemDateStringFormat(txtImportDate.Text.Trim());//GeneralUtility.ConvertSystemDateStringFormat(System.DateTime.Now); denoEntity.DenoMonth = GeneralUtility.ConvertMonthYearStringFormat(txtImportDate.Text); //probesEntity.ImportTime = GeneralUtility.ConvertSystemTimeFormat(System.DateTime.Now); denoEntity.Createdby = userEntity.ID; denolist.Add(denoEntity); #endregion } }
protected void btnCancelImport_Click(object sender, EventArgs e) { List <string> import_successList = new List <string>(); if (ddlJobName.SelectedIndex != 0) { if (FileUpload1.HasFile) { if (Path.GetExtension(FileUpload1.FileName) == ".xlsx") { #region "Multi Sheet or Sheet by Sheet Import" (third thinking => ) using (ExcelPackage ex_package = new ExcelPackage(FileUpload1.FileContent)) { int ws_count = ex_package.Workbook.Worksheets.Count(); if (ws_count == 1) { var jobName = ddlJobName.SelectedValue.ToString(); var center = ddlCenterName.SelectedValue.ToString(); #region "For Probes" if (jobName.ToString().Trim() == "Probes") //job_name == "Probes" { var cancel = new Cancel().FindByCancelMonthAndJob(GeneralUtility.ConvertMonthYearStringFormat(txtImportDate.Text.Trim()), jobName, center); if (cancel.Count() == 0) { var cancel_list = new List <CancelEntity>(); ExcelWorksheet cancel_workSheet = ex_package.Workbook.Worksheets[1]; // probes_workSheet.DeleteRow(1); if (cancel_workSheet.Dimension != null) { Cancel_Probes_BindBusiness(cancel_list, cancel_workSheet); #region Save Cancel Probes Cancel itemBusiness = new Cancel(); int i = BulkInsertIntoCancel(cancel_list); MessageBox.MessageShow(this.GetType(), "Cancel Probes Import Successfully!.", ClientScript); //using (TransactionScope Scope = new TransactionScope()) //{ // try // { // //probes_list.RemoveAt(0); // foreach (var v in cancel_list) // { // itemBusiness.Entity = v; // itemBusiness.Save(); // } // Scope.Complete(); // import_successList.Add("Cancel Probes Import Successfully!\n"); // MessageBox.MessageShow(this.GetType(), "Cancel Probes Import Successfully!.", ClientScript); // } // catch (Exception ex) // { // Response.Redirect("error.aspx"); // throw ex; // } //} #endregion } } else { MessageBox.MessageShow(this.GetType(), "This Excel File has already been Imported!", ClientScript); } } #endregion #region "For Scenes" if (jobName.ToString().Trim() == "Scenes") //job_name == "Probes" { var cancel = new Cancel().FindByCancelMonthAndJob(GeneralUtility.ConvertMonthYearStringFormat(txtImportDate.Text.Trim()), jobName, center); if (cancel.Count() == 0) { var cancel_list = new List <CancelEntity>(); ExcelWorksheet cancel_workSheet = ex_package.Workbook.Worksheets[1]; // probes_workSheet.DeleteRow(1); if (cancel_workSheet.Dimension != null) { Cancel_Scenes_BindBusiness(cancel_list, cancel_workSheet); #region Save Cancel Probes Cancel itemBusiness = new Cancel(); int i = BulkInsertIntoCancel(cancel_list); MessageBox.MessageShow(this.GetType(), "Cancel Scenes Import Successfully!.", ClientScript); //using (TransactionScope Scope = new TransactionScope()) //{ // try // { // //probes_list.RemoveAt(0); // foreach (var v in cancel_list) // { // itemBusiness.Entity = v; // itemBusiness.Save(); // } // Scope.Complete(); // import_successList.Add("Cancel Scenes Import Successfully!\n"); // MessageBox.MessageShow(this.GetType(), "Cancel Scenes Import Successfully!.", ClientScript); // } // catch (Exception ex) // { // Response.Redirect("error.aspx"); // throw ex; // } //} #endregion } } else { MessageBox.MessageShow(this.GetType(), "This Excel File has already been Imported!", ClientScript); } } #endregion #region "For Stitching" if (jobName.ToString().Trim() == "Stitching") //job_name == "Stitching" { var cancel = new Cancel().FindByCancelMonthAndJob(GeneralUtility.ConvertMonthYearStringFormat(txtImportDate.Text.Trim()), jobName, center); if (cancel.Count() == 0) { var cancel_list = new List <CancelEntity>(); ExcelWorksheet cancel_workSheet = ex_package.Workbook.Worksheets[1]; // probes_workSheet.DeleteRow(1); if (cancel_workSheet.Dimension != null) { Cancel_Stitching_BindBusiness(cancel_list, cancel_workSheet); #region Save Cancel Probes Cancel itemBusiness = new Cancel(); int i = BulkInsertIntoCancel(cancel_list); MessageBox.MessageShow(this.GetType(), "Cancel Probes Import Successfully!.", ClientScript); //using (TransactionScope Scope = new TransactionScope()) //{ // try // { // //probes_list.RemoveAt(0); // foreach (var v in cancel_list) // { // itemBusiness.Entity = v; // itemBusiness.Save(); // } // Scope.Complete(); // import_successList.Add("Cancel Probes Import Successfully!\n"); // MessageBox.MessageShow(this.GetType(), "Cancel Probes Import Successfully!.", ClientScript); // } // catch (Exception ex) // { // Response.Redirect("error.aspx"); // throw ex; // } //} #endregion } } else { MessageBox.MessageShow(this.GetType(), "This Excel File has already been Imported!", ClientScript); } } #endregion } else { MessageBox.MessageShow(this.GetType(), "Not Provided Multiple Sheet Import!", ClientScript); } } #endregion } } } else { MessageBox.MessageShow(this.GetType(), "Please Choose Job Name!.", ClientScript); } }
private void Cancel_Stitching_BindBusiness(List <CancelEntity> cancellist, ExcelWorksheet ws) { var userEntity = (UserEntity)Session["ID"]; for (var rowNumber = 1; rowNumber <= ws.Dimension.End.Row; rowNumber++) #region for Loop Row { var cancelEntity = new CancelEntity(); cancelEntity.ID = GeneralUtility.GeneratedKey; #region Populate Excel data for (var colNumber = 1; colNumber <= ws.Dimension.End.Column; colNumber++) { var columnName1 = ws.Cells[1, colNumber].Value.ToString().Trim(); try { if (true && rowNumber > 1) { switch (columnName1) { #region //case 1: case "user id": //QAT cancelEntity.QAT = Convert.ToString(ws.Cells[rowNumber, colNumber].Value); break; //case 2: case "Total Deduct Count": cancelEntity.Stitching = Convert.ToDecimal(ws.Cells[rowNumber, colNumber].Value); break; #endregion default: break; #endregion } } } catch (Exception ex) { Response.Redirect("error.aspx"); throw ex; } } if (rowNumber > 1) { cancelEntity.Center = ddlCenterName.SelectedValue.ToString(); cancelEntity.CancelMonth = GeneralUtility.ConvertMonthYearStringFormat(txtImportDate.Text); cancelEntity.CreatedDate = GeneralUtility.ConvertSystemDateStringFormat(System.DateTime.Now); cancelEntity.Createdby = userEntity.ID; cancellist.Add(cancelEntity); } #endregion } }
protected void btnSubmit_Click(object sender, EventArgs e) { if (!ValidateForm()) { return; } using (TransactionScope scope = new TransactionScope()) { if (btnSubmit.Text != "Search") { try { var userEntity = (UserEntity)Session["ID"]; new ProjectDenominators { Entity = new ProjectDenominatorsEntity { ID = GeneralUtility.GeneratedKey, PROJECT = this.txtProject.Text.Trim().ToUpper(), DenoMonth = GeneralUtility.ConvertMonthYearStringFormat(txtMonth.Text.Trim()), Probes = Convert.ToDecimal(string.IsNullOrEmpty(txtProbes.Text) ? "0" : txtProbes.Text), Pricingprobes = Convert.ToDecimal(string.IsNullOrEmpty(txtPricingProbes.Text) ? "0" : txtPricingProbes.Text), Masks = Convert.ToDecimal(string.IsNullOrEmpty(txtMasks.Text) ? "0" : txtMasks.Text), Repricing = Convert.ToDecimal(string.IsNullOrEmpty(txtRepricing.Text) ? "0" : txtRepricing.Text), SceneRecog = Convert.ToDecimal(string.IsNullOrEmpty(txtSceneRecog.Text) ? "0" : txtSceneRecog.Text), ProbesperScene = Convert.ToDecimal(string.IsNullOrEmpty(txtScenes.Text) ? "0" : txtScenes.Text), Expert = Convert.ToDecimal(string.IsNullOrEmpty(txtCategoryExpert.Text) ? "0" : txtCategoryExpert.Text), Createdby = userEntity.ID, } }.Save(); MessageBox.MessageShow(this.GetType(), "Successfully Save.", ClientScript); btnSubmit.Text = "Submit"; BindGrid(); scope.Complete(); } catch (Exception ex) { throw ex; } } else { try { BindGrid(); } catch (Exception ex) { throw ex; } } } this.txtProject.Text = string.Empty; txtProbes.Text = ""; txtPricingProbes.Text = ""; txtMasks.Text = ""; txtRepricing.Text = ""; txtScenes.Text = ""; txtSceneRecog.Text = ""; txtCategoryExpert.Text = ""; }
protected void btnExport_Click(object sender, EventArgs e) { if (txtMonth.Text != "" && txtMonth.Text != null) { #region "For Above 96%+5K" string count1 = "0"; string count2 = "0"; calculateMonth = GeneralUtility.ConvertMonthYearStringFormat(txtMonth.Text.Trim()); //calculateFromDate = calculateMonth + "16"; //calculateFromDate calculateToDate = calculateMonth + "15"; //calculateToDate string calculatedate = new AccuracyPercentage().FindPreviousMonth(calculateToDate); DateTime calfromtime = DateTime.Parse(calculatedate); var cdate = GeneralUtility.ConvertSystemDateStringFormat(calfromtime); string cmonth = cdate.Substring(4, 2); string cyear = cdate.Substring(0, 4); cMonth2 = cyear + cmonth; //calculateToDate = cMonth2 + "15"; //calculateToDate calculateFromDate = cMonth2 + "16"; //calculateFromDate if ((txtFromDate.Text == "" || txtFromDate.Text == null) || (txtToDate.Text == "" || txtToDate.Text == null)) { MessageBox.MessageShow(this.GetType(), "Please Choose From/To Date!.", ClientScript); return; } else { fromDate = GeneralUtility.ConvertSystemDateStringFormat(txtFromDate.Text); toDate = GeneralUtility.ConvertSystemDateStringFormat(txtToDate.Text); DateTime dtStartDate = DateTime.ParseExact(GeneralUtility.ConvertDisplayDateStringFormat(this.txtFromDate.Text.Trim()), "dd/MM/yyyy", CultureInfo.InvariantCulture); DateTime dtEndDate = DateTime.ParseExact(GeneralUtility.ConvertDisplayDateStringFormat(this.txtToDate.Text.Trim()), "dd/MM/yyyy", CultureInfo.InvariantCulture); count1 = new Probes().CheckDate(calculateFromDate, calculateToDate, fromDate); count2 = new Probes().CheckDate(calculateFromDate, calculateToDate, toDate); if (count1 == "0" || count2 == "0") { MessageBox.MessageShow(this.GetType(), "Please Check From/To Date Range!.", ClientScript); return; } if ((dtEndDate.Date != dtStartDate.Date)) { if (!(dtEndDate.Date > dtStartDate.Date)) { MessageBox.MessageShow(this.GetType(), "Invalid End Date.", ClientScript); //this.txtToDate.Focus(); return; } } string fromYear = fromDate.Substring(0, 4); string fromMonth = fromDate.Substring(4, 2); string toYear = toDate.Substring(0, 4); string toMonth = toDate.Substring(4, 2); if (fromYear == toYear && fromMonth == toMonth) { Month1 = fromYear + fromMonth; Month2 = string.Empty; FromDate2 = string.Empty; ToDate2 = string.Empty; } else { //string comparedate=new AccuracyPercentage().FindMonthAndYear(fromDate); //string comparemonth = comparedate.Substring(3, 2); //string compareyear = comparedate.Substring(6, 4); string comparedate = new AccuracyPercentage().FindMonthAndYear(fromDate); DateTime fromtime = DateTime.Parse(comparedate); var fdate = GeneralUtility.ConvertSystemDateStringFormat(fromtime); string comparemonth = fdate.Substring(4, 2); string compareyear = fdate.Substring(0, 4); if (compareyear != toYear || comparemonth != toMonth) { MessageBox.MessageShow(this.GetType(), "Please Check FromDate and ToDate!.", ClientScript); return; } Month1 = fromYear + fromMonth; Month2 = compareyear + comparemonth; FromDate2 = Month2 + "01"; ToDate2 = toDate; toDate = new AccuracyPercentage().FindLastDayOfMonth(fromDate); } } // FindByMonthFor3PA(branchcode, GeneralUtility.ConvertMonthYearStringFormat(txtMonth.Text.Trim()),fromDate,toDate,month2,fromDate2,toDate2); //@Center nvarchar(30),@Month nvarchar(30),@FromDate VARCHAR(8), @ToDate VARCHAR(8),@Month2 nvarchar(30),@FromDate2 VARCHAR(8), @ToDate2 VARCHAR(8) //var branchcode = string.Empty; //if (ddlCenterName.SelectedItem.Value != "All") //{ // branchcode = ddlCenterName.SelectedValue.ToString(); //} var branchcode = string.Empty; if (ddlCenterName.SelectedIndex != 0) { branchcode = ddlCenterName.SelectedValue.ToString(); } else { MessageBox.MessageShow(this.GetType(), "Please Choose Center!.", ClientScript); return; } new Probes { Criteria = new PPP_Project.Criteria.ImportJobsCriteria { CenterName = branchcode, FromDate = calculateFromDate, ToDate = calculateToDate, Month1 = cMonth2, Month2 = Month2, FromDate2 = FromDate2, ToDate2 = ToDate2, } }.CalculateTotalProbes(); var finalAccuracyList = new AccuracyPercentage().FindByMonthFor3PAGSS(branchcode, cMonth2, fromDate, toDate, Month2, FromDate2, ToDate2); DataTable attTbl = new DataTable(); attTbl.Clear(); attTbl.Columns.Clear(); var result = (from dd in finalAccuracyList orderby dd.QAT select dd).ToList(); // Convert to DataTable. DataTable table = ConvertToDataTable(result); table.Columns.Remove("RQuality"); table.Columns.Remove("AmountforProbes"); table.Columns.Remove("AmountforAccuracy"); table.Columns.Remove("PPPA"); table.Columns.Remove("Center"); table.Columns.Remove("Month"); table.Columns.Remove("Quality"); table.Columns.Remove("Name"); //int sum = finalProbesdt.AsEnumerable().Sum(s => s.Field<int>("Total Probes")); var yrm = GeneralUtility.ConvertSystemDateStringFormat(calculateToDate); int yr = Convert.ToInt32(yrm.Substring(0, 4).ToString()); int mth = Convert.ToInt32(yrm.Substring(4, 2).ToString()); DateTime date = new DateTime(yr, mth, 1); var mm = date.ToString("MMMM"); var yy = date.ToString("yy"); if (result.Count().Equals(0)) { MessageBox.MessageShow(GetType(), "No Export Data.!", ClientScript); } else { var fileName = "3PA" + mm + "'" + yy + ".xlsx"; int count = 0; Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; //Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("Probes_List_Export.xlsx", System.Text.Encoding.UTF8)); this.Response.AddHeader( "content-disposition", string.Format("attachment; filename={0}", fileName)); ExcelPackage pkg = new ExcelPackage(); using (pkg) { ExcelWorksheet ws = pkg.Workbook.Worksheets.Add("3PA Ori"); ws.Cells["A1"].LoadFromDataTable(table, true); #region "No need region" // using (ExcelRange rng = ws.Cells["A1:W1"]) // using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count]) using (ExcelRange rng = ws.Cells[1, 1, 1, table.Columns.Count]) { rng.Style.Font.Bold = true; //Set Pattern for the background to Solid rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set color to dark blue rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189)); // rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(122,160,205)); rng.Style.Font.Color.SetColor(System.Drawing.Color.White); } //ws.Cells["A1:MT"].Style.Font.Bold = true; ////ws.Cells["A1"].Style.Font.Bold = true; //using (ExcelRange rng = ws.Cells["A1:U" + (colcount - 1)]) //{ // rng.Style.Font.Bold = true; //} //var headerCell = ws.Cells["A5:MT"]; //headerCell.Style.Fill.PatternType = ExcelFillStyle.Solid; //headerCell.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.BurlyWood); //var headerFont = headerCell.Style.Font; //headerFont.Bold = true; //ws.Cells[1, 30].Style.Fill.PatternType = ExcelFillStyle.Solid; //ws.Cells[1, 30].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightBlue); //ws.Cells[1, 30].Style.VerticalAlignment = ExcelVerticalAlignment.Center; //ws.Cells[1, 30].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; #endregion if (result.Count() > 0) { count = result.Count() + 2; // pkg.Workbook.Worksheets.FirstOrDefault().Cells[count, 3].Value = sum;//result.Sum(x => x.TotalProbes); // pkg.Workbook.Worksheets.FirstOrDefault().Cells[count, 3].Style.Font.Bold = true; // pkg.Workbook.Worksheets.FirstOrDefault().Cells[count, 3].Style.Font.UnderLine = true; // pkg.Workbook.Worksheets.FirstOrDefault().Cells[count, 3].Style.Font.Color.SetColor(System.Drawing.Color.Blue); // pkg.Workbook.Worksheets.FirstOrDefault().Cells[count, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin); } pkg.Workbook.Worksheets.FirstOrDefault().DefaultColWidth = 20; pkg.Workbook.Worksheets.FirstOrDefault().Row(1).Height = 25; // using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count]) // var modelTable = pkg.Workbook.Worksheets.FirstOrDefault().Cells["A1:MP" + (count - 1)]; //+ (count - 1) var modelTable = pkg.Workbook.Worksheets.FirstOrDefault().Cells[ws.Dimension.Start.Row, 1, ws.Dimension.Start.Row + table.Rows.Count, table.Columns.Count]; //+ (count - 1) var border = modelTable.Style.Border.Top.Style = modelTable.Style.Border.Left.Style = modelTable.Style.Border.Right.Style = modelTable.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; pkg.Workbook.Properties.Title = "Attempts"; this.Response.BinaryWrite(pkg.GetAsByteArray()); this.Response.End(); } } // End Export Block #endregion // End Probes } else { MessageBox.MessageShow(this.GetType(), "Please Choose Export Date!.", ClientScript); } }
private bool ValidateForm() { if (string.IsNullOrEmpty(this.txtProject.Text.Trim())) { MessageBox.MessageShow(this.GetType(), "Please type PROJECT.", ClientScript); this.txtProject.Focus(); return(false); } if (string.IsNullOrEmpty(this.txtMonth.Text.Trim())) { MessageBox.MessageShow(this.GetType(), "Please choose Month.", ClientScript); this.txtMonth.Focus(); return(false); } //if (txtProbes.Text == "" && txtPricingProbes.Text == "" && txtMasks.Text == "" && txtRepricing.Text == "" && txtScenes.Text == "" && txtSceneRecog.Text == "" && txtCategoryExpert.Text == "") //{ // MessageBox.MessageShow(this.GetType(), "Please type Denominator.", ClientScript); // txtProbes.Focus(); // return false; //} var entity = new ProjectDenominators().FindDataByProjectAndMonth(this.txtProject.Text.Trim(), GeneralUtility.ConvertMonthYearStringFormat(txtMonth.Text.Trim())); if (entity != null) { MessageBox.MessageShow(this.GetType(), "This Project already exits.", ClientScript); this.txtProject.Focus(); return(false); } return(true); }
private void Acc_BindBusiness(List <AccuracyEntity> denolist, ExcelWorksheet ws) { var userEntity = (UserEntity)Session["ID"]; for (var rowNumber = 1; rowNumber <= ws.Dimension.End.Row; rowNumber++) { var accEntity = new AccuracyEntity(); accEntity.ID = GeneralUtility.GeneratedKey; #region Populate Excel data for (var colNumber = 1; colNumber <= ws.Dimension.End.Column; colNumber++) { try { if (true) { switch (colNumber) { #region case 1: accEntity.QAT = Convert.ToString(ws.Cells[rowNumber, colNumber].Value); break; case 2: // accEntity.AccuracyPercent //#REF! if (Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() != "" && Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() != "-" && Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() != null && Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() != "#DIV/0!" && Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() != "#REF!") { var orgPercent = ws.Cells[rowNumber, colNumber].Value.ToString().Trim(new[] { '%' }); if (orgPercent == "100") { accEntity.AccuracyPercent = Convert.ToDecimal(orgPercent); } else { accEntity.AccuracyPercent = Convert.ToDecimal(orgPercent) * 100; } } //if (orgPercent.Length != 0 && orgPercent!="0") //{ // var percent= orgPercent.Substring(0, orgPercent.Length-1); // accEntity.AccuracyPercent = Convert.ToDecimal(percent); //} //accEntity.AccuracyPercent = Convert.ToDecimal(Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == "" || Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == "-" || Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == null ? 0 : ws.Cells[rowNumber, colNumber].Value); break; //case 9: // denoEntity.AMERICAS_DEMO = Convert.ToInt32(Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == "" || Convert.ToString(ws.Cells[rowNumber, colNumber].Value).Trim() == null ? 0 : ws.Cells[rowNumber, colNumber].Value); // break; default: break; #endregion } } } catch (Exception ex) { Response.Redirect("error.aspx"); throw ex; } } accEntity.CreatedDate = GeneralUtility.ConvertSystemDateStringFormat(txtImportDate.Text.Trim());//GeneralUtility.ConvertSystemDateStringFormat(System.DateTime.Now); accEntity.AccMonth = GeneralUtility.ConvertMonthYearStringFormat(txtImportDate.Text); accEntity.Center = ddlCenterName.SelectedValue.ToString(); //probesEntity.ImportTime = GeneralUtility.ConvertSystemTimeFormat(System.DateTime.Now); accEntity.Createdby = userEntity.ID; denolist.Add(accEntity); #endregion } }
//public static void UpdateData<T>(List<AccuracyEntity> list, string TableName) //{ // DataTable dt = new DataTable("MyTable"); // dt = ConvertToDataTable(list); // using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SchoolSoulDataEntitiesForReport"].ConnectionString)) // { // using (SqlCommand command = new SqlCommand("", conn)) // { // try // { // conn.Open(); // //Creating temp table on database // command.CommandText = "CREATE TABLE #TmpTable(...)"; // command.ExecuteNonQuery(); // //Bulk insert into temp table // using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn)) // { // bulkcopy.BulkCopyTimeout = 660; // bulkcopy.DestinationTableName = "#TmpTable"; // bulkcopy.WriteToServer(dt); // bulkcopy.Close(); // } // // Updating destination table, and dropping temp table // command.CommandTimeout = 300; // command.CommandText = "UPDATE T SET ... FROM " + TableName + " T INNER JOIN #TmpTable Temp ON ...; DROP TABLE #TmpTable;"; // command.ExecuteNonQuery(); // } // catch (Exception ex) // { // // Handle exception properly // } // finally // { // conn.Close(); // } // } // } //} protected void btnAccImport_Click(object sender, EventArgs e) { var acclist = new List <AccuracyEntity>(); // NEED 1 (first) var userEntity = (UserEntity)Session["ID"]; if (txtImportDate.Text != "" && txtImportDate.Text != null) { var center = ddlCenterName.SelectedValue.ToString(); var accPercent = new AccuracyPercentage().FindByAccPerDate(GeneralUtility.ConvertMonthYearStringFormat(txtImportDate.Text.Trim()), center); if (accPercent.Count() == 0) { if (FileUpload1.HasFile) { if (Path.GetExtension(FileUpload1.FileName) == ".xlsx") { ExcelPackage package = new ExcelPackage(FileUpload1.FileContent); // NEED 2 (first) ExcelWorksheet workSheet = package.Workbook.Worksheets.First(); // NEED 2 (first) //workSheet.DeleteRow(1); // NEED 3 (first) Acc_BindBusiness(acclist, workSheet); // NEED 4 (first) } } int i = BulkInsertIntoAccuracy(acclist); MessageBox.MessageShow(this.GetType(), "Project Accuracy Import Successfully!.", ClientScript); //#region Save Accuracy (first) //AccuracyPercentage itemBusiness = new AccuracyPercentage(); //using (TransactionScope Scope = new TransactionScope( // )) //{ // try // { // foreach (var v in acclist) // { // itemBusiness.Entity = v; // itemBusiness.Save(); // } // itemBusiness.ReplaceQATAccuracy(center); // Scope.Complete(); // MessageBox.MessageShow(this.GetType(), "Project Accuracy Import Successfully!.", ClientScript); // } // catch (Exception ex) // { // //Response.Redirect("error.aspx"); // throw ex; // } //} //#endregion } else { MessageBox.MessageShow(this.GetType(), "This Excel File has already been Imported!", ClientScript); } } else { MessageBox.MessageShow(this.GetType(), "Please Choose Import Date!.", ClientScript); } }
protected void btnSubmit_Click(object sender, EventArgs e) { if (!ValidateForm()) { return; } using (TransactionScope scope = new TransactionScope()) { if (btnSubmit.Text == "Submit") { try { var userEntity = (UserEntity)Session["ID"]; new AccuracyPercentRate { Entity = new AccPerRateEntity { ID = GeneralUtility.GeneratedKey, AccPerRate1 = Convert.ToInt32(txtAccPercentRate1.Text), AccPerRate2 = Convert.ToInt32(txtAccPercentRate2.Text), AccPerRate3 = Convert.ToInt32(txtAccPercentRate3.Text), AccPerRatedYear = GeneralUtility.ConvertMonthYearStringFormat(txtAccPerMonth.Text.Trim()), Createdby = userEntity.ID, UpdatedBy = userEntity.ID, UpdatedDate = GeneralUtility.ConvertSystemDateStringFormat(System.DateTime.Now) } }.Save(); MessageBox.MessageShow(this.GetType(), "Successfully Save.", ClientScript); scope.Complete(); } catch (Exception ex) { throw ex; } } else { try { var userEntity = (UserEntity)Session["ID"]; new AccuracyPercentRate { Entity = new AccPerRateEntity { ID = ID = hdID.Value, AccPerRate1 = Convert.ToInt32(txtAccPercentRate1.Text), AccPerRate2 = Convert.ToInt32(txtAccPercentRate2.Text), AccPerRate3 = Convert.ToInt32(txtAccPercentRate3.Text), AccPerRatedYear = GeneralUtility.ConvertMonthYearStringFormat(txtAccPerMonth.Text.Trim()), Createdby = userEntity.ID, UpdatedBy = userEntity.ID, UpdatedDate = GeneralUtility.ConvertSystemDateStringFormat(System.DateTime.Now), } }.Update(); MessageBox.MessageShow(this.GetType(), "Successfully Updated.", ClientScript); btnSubmit.Text = "Submit"; scope.Complete(); } catch (Exception ex) { throw ex; } } } txtAccPercentRate1.Text = ""; txtAccPercentRate2.Text = ""; txtAccPercentRate3.Text = ""; txtAccPerMonth.Text = ""; BindGrid(); }
protected void btnExport_Click(object sender, EventArgs e) { if (txtMonth.Text != "" && txtMonth.Text != null) { #region "QAT" var center = ddlCenterName.SelectedValue.ToString(); var QATList = new QAT().AllQAT(); DataTable attTbl = new DataTable(); attTbl.Clear(); attTbl.Columns.Clear(); var result = (from dd in QATList.Where(x => x.Center == this.ddlCenterName.SelectedValue).ToList() orderby dd.QAT select dd).ToList(); // Convert to DataTable. DataTable table = ConvertToDataTable(result); table.Columns.Remove("ID"); table.Columns.Remove("Createdby"); table.Columns.Remove("Month"); table.Columns.Remove("CreatedDate"); table.Columns.Remove("TableName"); var yrm = GeneralUtility.ConvertMonthYearStringFormat(txtMonth.Text.Trim()); int yr = Convert.ToInt32(yrm.Substring(0, 4).ToString()); int mth = Convert.ToInt32(yrm.Substring(4, 2).ToString()); DateTime date = new DateTime(yr, mth, 1); var mm = date.ToString("MMMM"); var yy = date.ToString("yy"); if (result.Count().Equals(0)) { MessageBox.MessageShow(GetType(), "No Export Data.!", ClientScript); } else { var fileName = "QATList" + mm + "'" + yy + ".xlsx"; int count = 0; Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; //Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("Probes_List_Export.xlsx", System.Text.Encoding.UTF8)); this.Response.AddHeader( "content-disposition", string.Format("attachment; filename={0}", fileName)); ExcelPackage pkg = new ExcelPackage(); using (pkg) { ExcelWorksheet ws = pkg.Workbook.Worksheets.Add("QAT"); ws.Cells["A1"].LoadFromDataTable(table, true); #region "No need region" // using (ExcelRange rng = ws.Cells["A1:W1"]) // using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count]) using (ExcelRange rng = ws.Cells[1, 1, 1, table.Columns.Count]) { rng.Style.Font.Bold = true; //Set Pattern for the background to Solid rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set color to dark blue rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189)); // rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(122,160,205)); rng.Style.Font.Color.SetColor(System.Drawing.Color.White); } #endregion if (result.Count() > 0) { count = result.Count() + 2; } pkg.Workbook.Worksheets.FirstOrDefault().DefaultColWidth = 20; pkg.Workbook.Worksheets.FirstOrDefault().Row(1).Height = 25; var modelTable = pkg.Workbook.Worksheets.FirstOrDefault().Cells[ws.Dimension.Start.Row, 1, ws.Dimension.Start.Row + table.Rows.Count, table.Columns.Count]; //+ (count - 1) var border = modelTable.Style.Border.Top.Style = modelTable.Style.Border.Left.Style = modelTable.Style.Border.Right.Style = modelTable.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; pkg.Workbook.Properties.Title = "Attempts"; this.Response.BinaryWrite(pkg.GetAsByteArray()); this.Response.End(); } } // End Export Block #endregion // End Probes } else { MessageBox.MessageShow(this.GetType(), "Please Choose Export Date!.", ClientScript); } }
protected void btnExport_Click(object sender, EventArgs e) { if (txtMonth.Text != "" && txtMonth.Text != null) { #region "For Project Name Not In Project Denominator" var projectNameList = new ProjectNames().FindByProjectName(GeneralUtility.ConvertMonthYearStringFormat(txtMonth.Text.Trim()), ddlJobName.SelectedValue); DataTable attTbl = new DataTable(); attTbl.Clear(); attTbl.Columns.Clear(); var result = (from dd in projectNameList orderby dd.PROJECT select dd).ToList(); // Convert to DataTable. DataTable table = ConvertToDataTable(result); table.Columns.Remove("ID"); table.Columns.Remove("Createdby"); //table.Columns.Remove("ImportMonth"); table.Columns.Remove("CreatedDate"); table.Columns.Remove("TableName"); var yrm = GeneralUtility.ConvertMonthYearStringFormat(txtMonth.Text.Trim()); int yr = Convert.ToInt32(yrm.Substring(0, 4).ToString()); int mth = Convert.ToInt32(yrm.Substring(4, 2).ToString()); DateTime date = new DateTime(yr, mth, 1); var mm = date.ToString("MMMM"); var yy = date.ToString("yy"); if (result.Count().Equals(0)) { MessageBox.MessageShow(GetType(), "No Export Data.!", ClientScript); } else { var fileName = "Project Name List " + mm + "'" + yy + ".xlsx"; int count = 0; Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; //Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("Probes_List_Export.xlsx", System.Text.Encoding.UTF8)); this.Response.AddHeader( "content-disposition", string.Format("attachment; filename={0}", fileName)); ExcelPackage pkg = new ExcelPackage(); using (pkg) { ExcelWorksheet ws = pkg.Workbook.Worksheets.Add("Project"); ws.Cells["A1"].LoadFromDataTable(table, true); #region "No need region" // using (ExcelRange rng = ws.Cells["A1:W1"]) // using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count]) using (ExcelRange rng = ws.Cells[1, 1, 1, table.Columns.Count]) { rng.Style.Font.Bold = true; //Set Pattern for the background to Solid rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set color to dark blue rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189)); // rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(122,160,205)); rng.Style.Font.Color.SetColor(System.Drawing.Color.White); } //ws.Cells["A1:MT"].Style.Font.Bold = true; ////ws.Cells["A1"].Style.Font.Bold = true; //using (ExcelRange rng = ws.Cells["A1:U" + (colcount - 1)]) //{ // rng.Style.Font.Bold = true; //} //var headerCell = ws.Cells["A5:MT"]; //headerCell.Style.Fill.PatternType = ExcelFillStyle.Solid; //headerCell.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.BurlyWood); //var headerFont = headerCell.Style.Font; //headerFont.Bold = true; // ws.Cells[2, 2].Style.Font.Name = "Zawgyi-One"; //ws.Cells[1, 30].Style.Fill.PatternType = ExcelFillStyle.Solid; //ws.Cells[1, 30].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightBlue); //ws.Cells[1, 30].Style.VerticalAlignment = ExcelVerticalAlignment.Center; //ws.Cells[1, 30].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; #endregion if (result.Count() > 0) { count = result.Count() + 2; // pkg.Workbook.Worksheets.FirstOrDefault().Cells[count, 3].Value = sum;//result.Sum(x => x.TotalProbes); // pkg.Workbook.Worksheets.FirstOrDefault().Cells[count, 3].Style.Font.Bold = true; // pkg.Workbook.Worksheets.FirstOrDefault().Cells[count, 3].Style.Font.UnderLine = true; // pkg.Workbook.Worksheets.FirstOrDefault().Cells[count, 3].Style.Font.Color.SetColor(System.Drawing.Color.Blue); // pkg.Workbook.Worksheets.FirstOrDefault().Cells[count, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin); } pkg.Workbook.Worksheets.FirstOrDefault().DefaultColWidth = 20; pkg.Workbook.Worksheets.FirstOrDefault().Row(1).Height = 25; // using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count]) // var modelTable = pkg.Workbook.Worksheets.FirstOrDefault().Cells["A1:MP" + (count - 1)]; //+ (count - 1) var modelTable = pkg.Workbook.Worksheets.FirstOrDefault().Cells[ws.Dimension.Start.Row, 1, ws.Dimension.Start.Row + table.Rows.Count, table.Columns.Count]; //+ (count - 1) var border = modelTable.Style.Border.Top.Style = modelTable.Style.Border.Left.Style = modelTable.Style.Border.Right.Style = modelTable.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; pkg.Workbook.Properties.Title = "Attempts"; this.Response.BinaryWrite(pkg.GetAsByteArray()); // Response.Output.Write("<meta http-equiv=\"Content-Type\"content=\"text/html; charset=utf-8\">"); this.Response.End(); } } // End Export Block #endregion // End Probes } else { MessageBox.MessageShow(this.GetType(), "Please Choose Export Date!.", ClientScript); } }
protected void btnSubmit_Click(object sender, EventArgs e) { if (!ValidateForm()) { return; } using (TransactionScope scope = new TransactionScope()) { if (btnSubmit.Text != "Search") { try { var userEntity = (UserEntity)Session["ID"]; //string count = ""; //count = change.getCountForProject(projectname, GeneralUtility.ConvertSystemDateStringFormat(txtMonth.Text.Trim())); //if (count == "0") new ProjectDenominators { Entity = new ProjectDenominatorsEntity { ID = hdID.Value, PROJECT = hdProject.Value, DenoMonth = GeneralUtility.ConvertMonthYearStringFormat(txtMonth.Text.Trim()), Probes = Convert.ToDecimal(string.IsNullOrEmpty(txtProbes.Text) ? "0" : txtProbes.Text), Pricingprobes = Convert.ToDecimal(string.IsNullOrEmpty(txtPricingProbes.Text) ? "0" : txtPricingProbes.Text), Masks = Convert.ToDecimal(string.IsNullOrEmpty(txtMasks.Text) ? "0" : txtMasks.Text), Repricing = Convert.ToDecimal(string.IsNullOrEmpty(txtRepricing.Text) ? "0" : txtRepricing.Text), SceneRecog = Convert.ToDecimal(string.IsNullOrEmpty(txtSceneRecog.Text) ? "0" : txtSceneRecog.Text), ProbesperScene = Convert.ToDecimal(string.IsNullOrEmpty(txtScenes.Text) ? "0" : txtScenes.Text), Expert = Convert.ToDecimal(string.IsNullOrEmpty(txtCategoryExpert.Text) ? "0" : txtCategoryExpert.Text), Createdby = userEntity.ID, } }.Update(); MessageBox.MessageShow(this.GetType(), "Successfully Save.", ClientScript); btnSubmit.Text = "Search"; divProbes.Attributes.Add("style", "display:none"); divPricing.Attributes.Add("style", "display:none"); divMask.Attributes.Add("style", "display:none"); divRepricing.Attributes.Add("style", "display:none"); divSceneRecog.Attributes.Add("style", "display:none"); divScene.Attributes.Add("style", "display:none"); divExpert.Attributes.Add("style", "display:none"); BindGrid(); scope.Complete(); } catch (Exception ex) { throw ex; } } else { try { BindGrid(); } catch (Exception ex) { throw ex; } } } ddlPROJECT.Enabled = true; txtProbes.Text = ""; txtPricingProbes.Text = ""; txtMasks.Text = ""; txtRepricing.Text = ""; txtScenes.Text = ""; txtSceneRecog.Text = ""; txtCategoryExpert.Text = ""; }