protected void ddlCenterName_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
var result = new AccuracyPercentage().FindQAT();
if (ddlCenterName.SelectedValue != "All")
{
var qats = (from data in result.Where(x => x.Center == this.ddlCenterName.SelectedValue).ToList()
select data).ToList();
ddlQAT.Items.Clear();
ddlQAT.Items.Insert(0, "Select");
for (int i = 0; i < qats.Count; i++)
{
ddlQAT.Items.Insert(i + 1, new ListItem(qats[i].QAT, qats[i].QAT));
}
}
else
{
ddlQAT.Items.Clear();
ddlQAT.Items.Insert(0, "Select");
for (int i = 0; i < result.Count; i++)
{
ddlQAT.Items.Insert(i + 1, new ListItem(result[i].QAT, result[i].QAT));
}
}
//btnSearch_Click(sender, e);
}
catch (Exception ex)
{
MessageBox.MessageShow(this.GetType(), ex.Message.ToString(), ClientScript);
}
}
private void BindQAT()
{
var result = new AccuracyPercentage().FindQAT();
this.ddlQAT.Items.Clear();
ddlQAT.Items.Insert(0, "Select");
for (int i = 0; i < result.Count; i++)
{
ddlQAT.Items.Insert(i + 1, new ListItem(result[i].QAT, result[i].QAT));
}
}
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 gvRecheckAcc_SelectedIndexChanged(object sender, EventArgs e)
{
hdID.Value = gvRecheckAcc.SelectedRow.Cells[0].Text;
hdQAT.Value = gvRecheckAcc.SelectedRow.Cells[1].Text;
hdCenter.Value = gvRecheckAcc.SelectedRow.Cells[4].Text;
string id = hdID.Value.ToString();
AccuracyPercentage business = new AccuracyPercentage();
var entity = business.FindDataByID(id);
if (entity != null)
{
this.ddlQAT.SelectedValue = entity.QAT;
this.ddlCenterName.SelectedValue = entity.Center;
this.txtAccuracy.Text = entity.AccuracyPercent.ToString();
this.txtMonth.Text = GeneralUtility.ConvertDisplayMonthStringFormat(entity.AccMonth);
}
divAccuracy.Attributes.Add("style", "");
ddlQAT.Enabled = false;
ddlCenterName.Enabled = false;
// divAccuracy.Style = "";
btnSubmit.Text = "Update";
}
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);
}
}
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);
}
}
//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 btnExport_Click(object sender, EventArgs e)
{
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 date = GeneralUtility.ConvertSystemDateStringFormat(fromtime);
string comparemonth = date.Substring(4, 2);
string compareyear = date.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);
}
//(branchcode, Month1, 7500, 96, fromDate, toDate, Month2, FromDate2, ToDate2);
var branchcode = string.Empty;
if (ddlCenterName.SelectedItem.Value != "All")
{
branchcode = ddlCenterName.SelectedValue.ToString();
}
#region "For TicTac"
var tictacslist = new TicTac
{
Criteria = new PPP_Project.Criteria.ImportJobsCriteria
{
CenterName = branchcode,
FromDate = fromDate,
ToDate = toDate,
Month1 = Month1,
Month2 = Month2,
FromDate2 = FromDate2,
ToDate2 = ToDate2,
}
}.FindByCriteriaDenominatorForTicTacs();
DataTable attTbl = new DataTable();
attTbl.Clear();
attTbl.Columns.Clear();
var result = (from dd in tictacslist
orderby dd.Center
select dd).ToList();
// Convert to DataTable.
DataTable table = ConvertToDataTable(result);
DataTable finalProbesdt = SupressEmptyColumnsForDenominator(table);
var yrm = GeneralUtility.ConvertSystemDateStringFormat(txtFromDate.Text.Trim());
int yr = Convert.ToInt32(yrm.Substring(0, 4).ToString());
int mth = Convert.ToInt32(yrm.Substring(4, 2).ToString());
DateTime date1 = new DateTime(yr, mth, 1);
var mm = date1.ToString("MMMM");
var yy = date1.ToString("yy");
if (result.Count().Equals(0))
{
MessageBox.MessageShow(GetType(), "No Export Data.!", ClientScript);
}
else
{
var fileName = "tictacslist_" + 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("Probes");
ws.Cells["A1"].LoadFromDataTable(finalProbesdt, true);
#region "No need region"
using (ExcelRange rng = ws.Cells[1, 1, 1, finalProbesdt.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();
}
}
#endregion // End Probes
}
