public static string Export_Quotation_Summary_Product(SummaryQuotationContractData[] dataSummaryQuotationContract)
{
var strDownloadUrl = "";
try
{
string strTemplatePath = "/Master/Template/";
string baseUrl = string.Empty;
String strExcelPath = string.Empty;
string strSummaryQUTemplateFile = "Template_QuotationSummaryReport-Contract.xlsx";
int intStartRow = 5;
DataSet dsResult;
string excelTemplate = Path.Combine(HttpContext.Current.Server.MapPath(strTemplatePath), strSummaryQUTemplateFile);
FileInfo templateFile = new FileInfo(excelTemplate);
ExcelPackage excel = new ExcelPackage(templateFile);
var templateSheet = excel.Workbook.Worksheets["Sheet1"];
var workSheet = excel.Workbook.Worksheets.Copy("Sheet1", "SummaryQU");
workSheet.DeleteRow(intStartRow, 1048576 - intStartRow);
var data = (from t in dataSummaryQuotationContract select t).FirstOrDefault();
if (data != null)
{
using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString()))
{
//Create array of Parameters
List <SqlParameter> arrParm = new List <SqlParameter>
{
new SqlParameter("@customer_id", SqlDbType.Int)
{
Value = data.customer_id
},
new SqlParameter("@customer_code", SqlDbType.VarChar, 50)
{
Value = data.customer_code
},
new SqlParameter("@customer_group", SqlDbType.Int)
{
Value = data.customer_group
},
new SqlParameter("@contract_no", SqlDbType.VarChar, 50)
{
Value = data.contract_no
},
new SqlParameter("@type_of_contract_id", SqlDbType.Int)
{
Value = data.contract_type
},
new SqlParameter("@quotation_no", SqlDbType.VarChar, 20)
{
Value = data.quotation_no
},
new SqlParameter("@quotation_status", SqlDbType.VarChar, 2)
{
Value = data.quotation_status
},
new SqlParameter("@quotation_date_from", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.quotation_date_from)
},
new SqlParameter("@quotation_date_to", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.quotation_date_to)
},
new SqlParameter("@po_date_from", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.po_date_from)
},
new SqlParameter("@po_date_to", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.po_date_to)
},
new SqlParameter("@starting_date_from", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.Starting_date_from)
},
new SqlParameter("@starting_date_to", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.Starting_date_to)
},
new SqlParameter("@expire_date_from", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.expire_date_from)
},
new SqlParameter("@expire_date_to", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.expire_date_to)
},
};
conn.Open();
dsResult = SqlHelper.ExecuteDataset(conn, "sp_report_quotation_summary_service_contract", arrParm.ToArray());
var i = 0;
foreach (DataRow row in dsResult.Tables[0].Rows)
{
i++;
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.NO].Value = i;
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.CONTRACT_NO].Value = row["contract_no"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.QUOTATION_No].Value = row["quotation_no"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.ISSUE_DATE].Value = row["quotation_date_dd"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.ISSUE_DATE_MM].Value = row["quotation_date_mm"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.ISSUE_DATE_YY].Value = row["quotation_date_yy"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.CUSTOMER_CODE].Value = row["customer_code"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.CUSTOMER_NAME].Value = row["customer_name"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.PROJECT_NAME].Value = row["project_name"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.CONTACT].Value = row["attention"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.QTY].Value = row["qty"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.TYPE_OF_CONTRACT].Value = row["remark1"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.STARTIING_DATE].Value = row["remark2"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.QUSTATUS].Value = row["status_name"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.UPDATE_DD].Value = row["updated_date_dd"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.UPDATE_MM].Value = row["updated_date_mm"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.UPDATE_YY].Value = row["updated_date_yy"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.PO_NO].Value = row["po_no"].ToString(); // row["inv_no"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.PO_DATE].Value = row["po_date"].ToString(); // row["po_no"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.AMOUNTPO].Value = row["total_amount"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.INVDATE].Value = row["inv_date"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.INVOICE_NO].Value = row["inv_no"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Contract.REMARK].Value = row["remark_status"].ToString();
intStartRow++;
}
}
}
templateSheet = excel.Workbook.Worksheets["Sheet1"];
excel.Workbook.Worksheets.Delete(templateSheet);
//strDownloadUrl = baseUrl + strTemplatePath + strProductTemplateFile;
string pathTemp = Path.GetTempFileName();
excel.SaveAs(new FileInfo(pathTemp));
strDownloadUrl = string.Format("LocalPath={0}&FileName={1}", pathTemp, "QuotationSummaryReport-Contract.xlsx");
}
catch (Exception ex)
{
string strErrorMsg = SPlanetUtil.LogErrorCollect(ex);
}
return(strDownloadUrl);
}
public static void GetViewGrid(SummaryQuotationContractData[] dataSummaryQuotationContract)
{
var dsData = new DataSet();
var summaryQuotationContractData = new List <SummaryQuotationContractData>();
var data = (from t in dataSummaryQuotationContract select t).FirstOrDefault();
if (data != null)
{
try
{
using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString()))
{
//Create array of Parameters
List <SqlParameter> arrParm = new List <SqlParameter>
{
new SqlParameter("@customer_id", SqlDbType.Int)
{
Value = data.customer_id
},
new SqlParameter("@customer_code", SqlDbType.VarChar, 50)
{
Value = data.customer_code
},
new SqlParameter("@customer_group", SqlDbType.Int)
{
Value = data.customer_group
},
new SqlParameter("@contract_no", SqlDbType.VarChar, 50)
{
Value = data.contract_no
},
new SqlParameter("@type_of_contract_id", SqlDbType.Int)
{
Value = data.contract_type
},
new SqlParameter("@quotation_no", SqlDbType.VarChar, 20)
{
Value = data.quotation_no
},
new SqlParameter("@quotation_status", SqlDbType.VarChar, 2)
{
Value = data.quotation_status
},
new SqlParameter("@quotation_date_from", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.quotation_date_from)
},
new SqlParameter("@quotation_date_to", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.quotation_date_to)
},
new SqlParameter("@po_date_from", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.po_date_from)
},
new SqlParameter("@po_date_to", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.po_date_to)
},
new SqlParameter("@starting_date_from", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.Starting_date_from)
},
new SqlParameter("@starting_date_to", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.Starting_date_to)
},
new SqlParameter("@expire_date_from", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.expire_date_from)
},
new SqlParameter("@expire_date_to", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.expire_date_to)
},
};
conn.Open();
dsData = SqlHelper.ExecuteDataset(conn, "sp_report_quotation_summary_service_contract", arrParm.ToArray());
if (dsData.Tables[0].Rows.Count > 0)
{
foreach (var row in dsData.Tables[0].AsEnumerable())
{
summaryQuotationContractData.Add(new SummaryQuotationContractData()
{
contract_no = Convert.IsDBNull(row["contract_no"]) ? string.Empty : Convert.ToString(row["contract_no"]),
quotation_no = Convert.IsDBNull(row["quotation_no"]) ? string.Empty : Convert.ToString(row["quotation_no"]),
quotation_date = Convert.IsDBNull(row["quotation_date"]) ? string.Empty : Convert.ToString(row["quotation_date"]),
customer_name = Convert.IsDBNull(row["customer_name"]) ? string.Empty : Convert.ToString(row["customer_name"]),
project_name = Convert.IsDBNull(row["project_name"]) ? string.Empty : Convert.ToString(row["project_name"]),
attention = Convert.IsDBNull(row["attention"]) ? string.Empty : Convert.ToString(row["attention"]),
contract_type = Convert.IsDBNull(row["contract_type"]) ? string.Empty : Convert.ToString(row["contract_type"]),
starting_date = Convert.IsDBNull(row["starting_date"]) ? string.Empty : Convert.ToString(row["starting_date"]),
expire_date = Convert.IsDBNull(row["expire_date"]) ? string.Empty : Convert.ToString(row["expire_date"]),
schedule_date_min_mmyy = Convert.IsDBNull(row["schedule_date_min_mmyy"]) ? string.Empty : Convert.ToString(row["schedule_date_min_mmyy"]),
schedule_date_max_mmyy = Convert.IsDBNull(row["schedule_date_max_mmyy"]) ? string.Empty : Convert.ToString(row["schedule_date_max_mmyy"]),
});
}
}
}
HttpContext.Current.Session["SESSION_EXPORT_SUMMARY_QUOTATION_CONTRACT"] = summaryQuotationContractData;
}
catch (Exception ex)
{
throw ex;
}
}
}
public static void GetViewGrid(SummaryQuotationDesignData[] dataSummaryQuotationDesign)
{
var dsData = new DataSet();
var summaryQuotationDesignData = new List <SummaryQuotationDesignData>();
var data = (from t in dataSummaryQuotationDesign select t).FirstOrDefault();
if (data != null)
{
try
{
using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString()))
{
//Create array of Parameters
List <SqlParameter> arrParm = new List <SqlParameter>
{
new SqlParameter("@customer_id", SqlDbType.Int)
{
Value = Convert.ToInt32(data.customer_id)
},
new SqlParameter("@customer_group", SqlDbType.Int)
{
Value = Convert.ToInt32(data.customer_group)
},
new SqlParameter("@employee_id", SqlDbType.Int)
{
Value = Convert.ToInt32(data.employee_id)
},
new SqlParameter("@quotation_status", SqlDbType.VarChar, 2)
{
Value = data.quotation_status
},
new SqlParameter("@quotation_date_from", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.quotation_date_from)
},
new SqlParameter("@quotation_date_to", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.quotation_date_to)
},
new SqlParameter("@po_date_from", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.po_date_from)
},
new SqlParameter("@po_date_to", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.po_date_to)
},
new SqlParameter("@quotation_no", SqlDbType.VarChar, 20)
{
Value = data.quotation_no
},
new SqlParameter("@other", SqlDbType.VarChar, 200)
{
Value = data.other
},
};
conn.Open();
dsData = SqlHelper.ExecuteDataset(conn, "sp_report_quotation_summary_design", arrParm.ToArray());
if (dsData.Tables[0].Rows.Count > 0)
{
foreach (var row in dsData.Tables[0].AsEnumerable())
{
summaryQuotationDesignData.Add(new SummaryQuotationDesignData()
{
id = Convert.ToInt32(row["id"]),
quotation_no = Convert.IsDBNull(row["quotation_no"]) ? string.Empty : Convert.ToString(row["quotation_no"]),
issue_date = Convert.IsDBNull(row["issue_stock_date"]) ? string.Empty : Convert.ToString(row["issue_stock_date"]),
customer_name = Convert.IsDBNull(row["customer_name"]) ? string.Empty : Convert.ToString(row["customer_name"]),
project_name = Convert.IsDBNull(row["project_name"]) ? string.Empty : Convert.ToString(row["project_name"]),
amount_po = Convert.IsDBNull(row["amount_po"]) ? 0 : Convert.ToInt32(row["amount_po"]),
});
}
}
}
HttpContext.Current.Session["SESSION_EXPORT_SUMMARY_QUOTATION_DESIGN"] = summaryQuotationDesignData;
}
catch (Exception ex)
{
throw ex;
}
}
}
public static string Export_Quotation_Summary_Design(SummaryQuotationDesignData[] dataSummaryQuotationDesign)
{
var strDownloadUrl = "";
try
{
string strTemplatePath = "/Master/Template/";
string baseUrl = string.Empty;
String strExcelPath = string.Empty;
string strSummaryQUTemplateFile = "Template_QuotationSummaryReport-Design.xlsx";
int intStartRow = 5;
DataSet dsResult;
string excelTemplate = Path.Combine(HttpContext.Current.Server.MapPath(strTemplatePath), strSummaryQUTemplateFile);
FileInfo templateFile = new FileInfo(excelTemplate);
ExcelPackage excel = new ExcelPackage(templateFile);
var templateSheet = excel.Workbook.Worksheets["Sheet1"];
var workSheet = excel.Workbook.Worksheets.Copy("Sheet1", "SummaryQU");
workSheet.DeleteRow(intStartRow, 1048576 - intStartRow);
var data = (from t in dataSummaryQuotationDesign select t).FirstOrDefault();
if (data != null)
{
using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString()))
{
//Create array of Parameters
List <SqlParameter> arrParm = new List <SqlParameter>
{
new SqlParameter("@customer_id", SqlDbType.Int)
{
Value = Convert.ToInt32(data.customer_id)
},
new SqlParameter("@customer_group", SqlDbType.Int)
{
Value = Convert.ToInt32(data.customer_group)
},
new SqlParameter("@employee_id", SqlDbType.Int)
{
Value = Convert.ToInt32(data.employee_id)
},
new SqlParameter("@quotation_status", SqlDbType.VarChar, 2)
{
Value = data.quotation_status
},
new SqlParameter("@quotation_date_from", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.quotation_date_from)
},
new SqlParameter("@quotation_date_to", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.quotation_date_to)
},
new SqlParameter("@po_date_from", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.po_date_from)
},
new SqlParameter("@po_date_to", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.po_date_to)
},
new SqlParameter("@quotation_no", SqlDbType.VarChar, 20)
{
Value = data.quotation_no
},
new SqlParameter("@other", SqlDbType.VarChar, 200)
{
Value = data.other
},
//quotation_no: quotation_no,
//other: other,
};
conn.Open();
dsResult = SqlHelper.ExecuteDataset(conn, "sp_report_quotation_summary_design", arrParm.ToArray());
var i = 2;
foreach (DataRow row in dsResult.Tables[0].Rows)
{
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.COUNT_CALL].Value = i - 1;
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.QUOTATION_NO].Value = row["quotation_no"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.ISSUE_DD].Value = row["quotation_date_dd"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.ISSUE_MM].Value = row["quotation_date_mm"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.ISSUE_YY].Value = row["quotation_date_yy"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.CUSTOMER_NAME].Value = row["customer_name"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.PROJECT_NAME].Value = row["project_name"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.SUbJECT].Value = row["quotation_subject"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.DESCRIPTION].Value = row["descirption"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.SALES].Value = row["created_by"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.PRICE_LIST].Value = row["total_amount"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.DISCOUNT].Value = row["total_discount"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.FINAL_PRICE].Value = row["grand_total"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.CONTACT_PERSON].Value = row["contact_name"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.QUSTATUS].Value = row["status_name"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.UPDATE_DD].Value = row["updated_date_dd"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.UPDATE_MM].Value = row["updated_date_mm"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.UPDATE_YY].Value = row["updated_date_yy"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.PO_NO].Value = row["po_no"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.PODATE].Value = row["po_date"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.INVOICE].Value = row["inv_no"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Design.TEL].Value = row["customer_tel"].ToString();
intStartRow++; i++;
}
}
}
templateSheet = excel.Workbook.Worksheets["Sheet1"];
excel.Workbook.Worksheets.Delete(templateSheet);
//strDownloadUrl = baseUrl + strTemplatePath + strProductTemplateFile;
string pathTemp = Path.GetTempFileName();
excel.SaveAs(new FileInfo(pathTemp));
strDownloadUrl = string.Format("LocalPath={0}&FileName={1}", pathTemp, "QuotationSummaryReport-Design.xlsx");
}
catch (Exception ex)
{
string strErrorMsg = SPlanetUtil.LogErrorCollect(ex);
}
return(strDownloadUrl);
}
public static void GetViewGrid(SummaryQuotationProductData[] dataSummaryQuotationProduct)
{
var dsData = new DataSet();
var SummaryQuotationProductData = new List <SummaryQuotationProductData>();
var data = (from t in dataSummaryQuotationProduct select t).FirstOrDefault();
if (data != null)
{
try
{
using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString()))
{
//Create array of Parameters
List <SqlParameter> arrParm = new List <SqlParameter>
{
new SqlParameter("@customer_id", SqlDbType.Int)
{
Value = Convert.ToInt32(data.customer_id)
},
new SqlParameter("@customer_group", SqlDbType.Int)
{
Value = Convert.ToInt32(data.customer_group)
},
new SqlParameter("@cat_id", SqlDbType.Int)
{
Value = Convert.ToInt32(data.cat_id)
},
new SqlParameter("@employee_id", SqlDbType.Int)
{
Value = Convert.ToInt32(data.employee_id)
},
new SqlParameter("@quotation_status", SqlDbType.VarChar, 2)
{
Value = data.quotation_status
},
new SqlParameter("@quotation_date_from", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.quotation_date_from)
},
new SqlParameter("@quotation_date_to", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.quotation_date_to)
},
new SqlParameter("@model_list_air_compressor", SqlDbType.VarChar, 200)
{
Value = data.model_list_air_compressor
},
new SqlParameter("@model_list_air_presure", SqlDbType.VarChar, 200)
{
Value = data.model_list_air_presure
},
new SqlParameter("@model_list_air_dryer", SqlDbType.VarChar, 200)
{
Value = data.model_list_air_dryer
},
new SqlParameter("@model_list_line_filter", SqlDbType.VarChar, 200)
{
Value = data.model_list_line_filter
},
new SqlParameter("@model_list_mist_filter", SqlDbType.VarChar, 200)
{
Value = data.model_list_mist_filter
},
new SqlParameter("@model_list_air_tank", SqlDbType.VarChar, 200)
{
Value = data.model_list_air_tank
},
new SqlParameter("@model_list_other", SqlDbType.VarChar, 200)
{
Value = data.model_list_other
},
new SqlParameter("@other", SqlDbType.VarChar, 200)
{
Value = data.other
},
new SqlParameter("@po_date_from", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.po_date_from)
},
new SqlParameter("@po_date_to", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.po_date_to)
},
new SqlParameter("@quotation_no", SqlDbType.VarChar, 20)
{
Value = data.quotation_no
},
};
conn.Open();
dsData = SqlHelper.ExecuteDataset(conn, "sp_report_quotation_summary_product", arrParm.ToArray());
if (dsData.Tables[0].Rows.Count > 0)
{
foreach (var row in dsData.Tables[0].AsEnumerable())
{
SummaryQuotationProductData.Add(new SummaryQuotationProductData()
{
id = Convert.ToInt32(row["id"]),
quotation_no = Convert.IsDBNull(row["quotation_no"]) ? string.Empty : Convert.ToString(row["quotation_no"]),
quotation_date_from = Convert.IsDBNull(row["quotation_date"]) ? string.Empty : Convert.ToString(row["quotation_date"]),
customer_name = Convert.IsDBNull(row["customer_name"]) ? string.Empty : Convert.ToString(row["customer_name"]),
grand_total = Convert.IsDBNull(row["grand_total"]) ? string.Empty : Convert.ToString(row["grand_total"]),
po_no = Convert.IsDBNull(row["po_no"]) ? string.Empty : Convert.ToString(row["po_no"]),
amount_po = Convert.IsDBNull(row["amount_po"]) ? string.Empty : Convert.ToString(row["amount_po"]),
});
}
}
}
HttpContext.Current.Session["SESSION_EXPORT_SUMMARY_QUOTATION_CONTRACT"] = SummaryQuotationProductData;
}
catch (Exception ex)
{
throw ex;
}
}
}
public static string Export_Quotation_Summary_Product(SummaryQuotationProductData[] dataSummaryQuotationProduct)
{
string strDownloadUrl = string.Empty;
try
{
string strTemplatePath = "/Master/Template/";
string baseUrl = string.Empty;
String strExcelPath = string.Empty;
string strSummaryQUTemplateFile = "Template_QuotationSummaryReport-Product.xlsx";
int intStartRow = 5;
DataSet dsResult;
string excelTemplate = Path.Combine(HttpContext.Current.Server.MapPath(strTemplatePath), strSummaryQUTemplateFile);
FileInfo templateFile = new FileInfo(excelTemplate);
ExcelPackage excel = new ExcelPackage(templateFile);
var templateSheet = excel.Workbook.Worksheets["Sheet1"];
var workSheet = excel.Workbook.Worksheets.Copy("Sheet1", "SummaryQU");
workSheet.DeleteRow(5, 1048576 - 5);
var data = (from t in dataSummaryQuotationProduct select t).FirstOrDefault();
using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString()))
{
//Create array of Parameters
List <SqlParameter> arrParm = new List <SqlParameter>
{
new SqlParameter("@customer_id", SqlDbType.Int)
{
Value = Convert.ToInt32(data.customer_id)
},
new SqlParameter("@customer_group", SqlDbType.Int)
{
Value = Convert.ToInt32(data.customer_group)
},
new SqlParameter("@cat_id", SqlDbType.Int)
{
Value = Convert.ToInt32(data.cat_id)
},
new SqlParameter("@employee_id", SqlDbType.Int)
{
Value = Convert.ToInt32(data.employee_id)
},
new SqlParameter("@quotation_status", SqlDbType.VarChar, 2)
{
Value = data.quotation_status
},
new SqlParameter("@quotation_date_from", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.quotation_date_from)
},
new SqlParameter("@quotation_date_to", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.quotation_date_to)
},
new SqlParameter("@model_list_air_compressor", SqlDbType.VarChar, 200)
{
Value = data.model_list_air_compressor
},
new SqlParameter("@model_list_air_presure", SqlDbType.VarChar, 200)
{
Value = data.model_list_air_presure
},
new SqlParameter("@model_list_air_dryer", SqlDbType.VarChar, 200)
{
Value = data.model_list_air_dryer
},
new SqlParameter("@model_list_line_filter", SqlDbType.VarChar, 200)
{
Value = data.model_list_line_filter
},
new SqlParameter("@model_list_mist_filter", SqlDbType.VarChar, 200)
{
Value = data.model_list_mist_filter
},
new SqlParameter("@model_list_air_tank", SqlDbType.VarChar, 200)
{
Value = data.model_list_air_tank
},
new SqlParameter("@model_list_other", SqlDbType.VarChar, 200)
{
Value = data.model_list_other
},
new SqlParameter("@other", SqlDbType.VarChar, 200)
{
Value = data.other
},
new SqlParameter("@po_date_from", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.po_date_from)
},
new SqlParameter("@po_date_to", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.po_date_to)
},
new SqlParameter("@quotation_no", SqlDbType.VarChar, 20)
{
Value = data.quotation_no
},
};
conn.Open();
dsResult = SqlHelper.ExecuteDataset(conn, "sp_report_quotation_summary_product", arrParm.ToArray());
var i = 0;
foreach (DataRow row in dsResult.Tables[0].Rows)
{
i++;
templateSheet.SelectedRange["A5:XFA5"].Copy(workSheet.SelectedRange[intStartRow, 1]);
//workSheet.Cells[intStartRow, 1].Value = i;
//workSheet.Cells[intStartRow, 2].Value = Convert.IsDBNull(row["id"]) ? string.Empty : Convert.ToString(row["id"]);
//workSheet.Cells[intStartRow, 3].Value = Convert.IsDBNull(row["product_no"]) ? string.Empty : Convert.ToString(row["product_no"]);
//workSheet.Cells[intStartRow, 4].Value = Convert.IsDBNull(row["product_name_tha"]) ? string.Empty : Convert.ToString(row["product_name_tha"]);
//workSheet.Cells[intStartRow, 5].Value = Convert.IsDBNull(row["unit_code"]) ? string.Empty : Convert.ToString(row["unit_code"]);
//workSheet.Cells[intStartRow, 6].Value = Convert.IsDBNull(row["quantity"]) ? string.Empty : Convert.ToString(row["quantity"]);
//workSheet.Cells[intStartRow, 7].Value = Convert.IsDBNull(row["quantity_reserve"]) ? string.Empty : Convert.ToString(row["quantity_reserve"]);
//workSheet.Cells[intStartRow, 8].Value = 0;
//workSheet.Cells[intStartRow, 9].Value = string.Empty;
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.COUNT_CALL].Value = i;
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.QUOTATION_NO].Value = row["quotation_no"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.QUOTATION_DATE_DD].Value = row["quotation_date_dd"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.QUOTATION_DATE_MM].Value = row["quotation_date_mm"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.QUOTATION_DATE_YY].Value = row["quotation_date_yy"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.CUSTOMER_NAME].Value = row["customer_name"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.PROJECT_NAME].Value = row["project_name"].ToString();
//workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.PRODUCT_NAME].Value = row["product_name"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.PROVINCE_NAME].Value = row["province_name"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.CUSTOMER_ADDRESS].Value = row["customer_address"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.CATEGORY_ALIAS_LIST].Value = row["cat_code"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_AIR_COMPRESSOR].Value = row["model_list_air_compressor"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_AIR_COMPRESSOR_PRESSURE].Value = row["model_list_air_compressor_pressure"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_AIR_COMPRESSOR_QTY].Value = row["model_list_air_compressor_qty"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_AIR_DRYER].Value = row["model_list_air_dryer"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_AIR_DRYER_QTY].Value = row["model_list_air_dryer_qty"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_LINE_FILTER].Value = row["model_list_line_filter"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_LINE_FILTER_QTY].Value = row["model_list_line_filter_qty"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_MIST_FILTER].Value = row["model_list_mist_filter"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_MIST_FILTER_QTY].Value = row["model_list_mist_filter_qty"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_AIR_TANK].Value = row["model_list_air_tank"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_AIR_TANK_QTY].Value = row["model_list_air_tank_qty"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_OTHER].Value = row["model_list_other"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.MODEL_LIST_OTHER_QTY].Value = row["model_list_other_qty"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.SALES_NAME].Value = row["sales_name"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.PRICE_LIST].Value = row["total_amount"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.TOTAL_DISCOUNT].Value = row["total_discount"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.PRICE].Value = row["grand_total"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.CONTACT_NAME].Value = row["contact_name"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.STATUS_NAME].Value = row["status_name"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.UPDATE_DATE_DD].Value = row["updated_date_dd"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.UPDATE_DATE_MM].Value = row["updated_date_mm"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.UPDATE_DATE_YY].Value = row["updated_date_yy"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.PO_NUMBER].Value = row["po_no"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.PO_AMOUNT].Value = row["amount_po"].ToString();
//workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.CUSTOMER_REQUEST_BY].Value = row["sent_by"].ToString();
//workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.CUSTOMER_REQUEST_DATE_DD].Value = row["customer_request_date"].ToString();
//workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.CUSTOMER_REQUEST_DATE_MM].Value = row["customer_request_month"].ToString();
//workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.CUSTOMER_REQUEST_DATE_YY].Value = row["customer_request_year"].ToString();
//workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.TEST_RUN_DATE_DD].Value = row["test_run_date"].ToString();
//workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.TEST_RUN_DATE_MM].Value = row["test_run_month"].ToString();
//workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.TEST_RUN_DATE_YY].Value = row["test_run_year"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.CUSTOMER_TEL].Value = row["customer_tel"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.CUSTOMER_FAX].Value = row["customer_fax"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.CUSTOMER_EMAIL].Value = row["customer_email"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_Product.REMARK].Value = row["remark"].ToString();
intStartRow++;
}
}
templateSheet = excel.Workbook.Worksheets["Sheet1"];
excel.Workbook.Worksheets.Delete(templateSheet);
//strDownloadUrl = baseUrl + strTemplatePath + strProductTemplateFile;
string pathTemp = Path.GetTempFileName();
excel.SaveAs(new FileInfo(pathTemp));
strDownloadUrl = string.Format("LocalPath={0}&FileName={1}", pathTemp, "QuotationSummaryReport_Product.xlsx");
}
catch (Exception ex)
{
string strErrorMsg = SPlanetUtil.LogErrorCollect(ex);
}
return(strDownloadUrl);
}
public static string Export_Quotation_Summary_SparePart(SummaryQuotationSparePartData[] dataSummaryQuotationSparePart)
{
string strDownloadUrl = string.Empty;
try
{
string strTemplatePath = "/Master/Template/";
string baseUrl = string.Empty;
String strExcelPath = string.Empty;
string strSummaryQUTemplateFile = "Template_QuotationSummaryReport_SparePart.xlsx";
int intStartRow = 7;
DataSet dsResult;
string excelTemplate = Path.Combine(HttpContext.Current.Server.MapPath(strTemplatePath), strSummaryQUTemplateFile);
FileInfo templateFile = new FileInfo(excelTemplate);
ExcelPackage excel = new ExcelPackage(templateFile);
var templateSheet = excel.Workbook.Worksheets["Sheet1"];
var workSheet = excel.Workbook.Worksheets.Copy("Sheet1", "SummaryQU");
workSheet.DeleteRow(intStartRow, 1048576 - intStartRow);
var data = (from t in dataSummaryQuotationSparePart select t).FirstOrDefault();
using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString()))
{
//Create array of Parameters
List <SqlParameter> arrParm = new List <SqlParameter>
{
new SqlParameter("@customer_id", SqlDbType.Int)
{
Value = Convert.ToInt32(data.customer_id)
},
new SqlParameter("@cat_id", SqlDbType.Int)
{
Value = Convert.ToInt32(data.cat_id)
},
new SqlParameter("@shelf_id", SqlDbType.Int)
{
Value = Convert.ToInt32(data.shelf_id)
},
new SqlParameter("@quotation_status", SqlDbType.VarChar, 2)
{
Value = data.quotation_status
},
new SqlParameter("@customer_group", SqlDbType.Int)
{
Value = Convert.ToInt32(data.customer_group)
},
new SqlParameter("@quotation_no", SqlDbType.VarChar, 20)
{
Value = data.quotation
},
new SqlParameter("@model", SqlDbType.VarChar, 20)
{
Value = data.model
},
new SqlParameter("@mfg", SqlDbType.VarChar, 50)
{
Value = data.mfg
},
new SqlParameter("@quotation_date_from", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.quotation_date_from)
},
new SqlParameter("@quotation_date_to", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.quotation_date_to)
},
new SqlParameter("@po_date_from", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.po_date_from)
},
new SqlParameter("@po_date_to", SqlDbType.VarChar, 20)
{
Value = SPlanetUtil.Convert_ddmmyyyy_to_yyyymmdd(data.po_date_to)
},
new SqlParameter("@other", SqlDbType.VarChar, 200)
{
Value = data.other
},
new SqlParameter("@producttype", SqlDbType.VarChar, 200)
{
Value = data.producttype
},
};
conn.Open();
dsResult = SqlHelper.ExecuteDataset(conn, "sp_report_quotation_summary_spare_part", arrParm.ToArray());
var i = 0;
foreach (DataRow row in dsResult.Tables[0].Rows)
{
i++;
templateSheet.SelectedRange["A7:XFA7"].Copy(workSheet.SelectedRange[intStartRow, 1]);
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.COUNT_CALL].Value = i;
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.QUOTATION_NO].Value = row["quotation_no"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.QUOTATION_DATE_DD].Value = row["created_date_dd"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.QUOTATION_DATE_MM].Value = row["created_date_mm"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.QUOTATION_DATE_YY].Value = row["created_date_yy"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.CUSTOMER_NAME].Value = row["customer_name"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.PROJECT_NAME].Value = row["project_name"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.Subject].Value = row["quotation_subject"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.MODEL].Value = row["model"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.MFG].Value = row["mfg"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.Hour].Value = row["hour_amount"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.MACHINE].Value = row["machine"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.PRESSURE].Value = row["pressure"];
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.QUOTATION_TYPE].Value = row["quotation_type"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.DETAIL_OF_PART].Value = row["detail_of_part"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.DISCOUNT].Value = row["discount"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.TOTAL_AMOUNT].Value = row["total_amount"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.ATTENTION_NAME].Value = row["attention_name"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.QUOTATION_STATUS].Value = row["quotation_status"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.UPDATE_DD].Value = row["updated_date_dd"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.UPDATE_MM].Value = row["updated_date_mm"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.UPDATE_YY].Value = row["updated_date_yy"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.PO_NO].Value = row["po_no"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.PO_DATE].Value = row["ref_po_date"].ToString();
workSheet.Cells[intStartRow, (int)Column_QuotationSummary_SparePart.REMARK].Value = row["remark_status"].ToString();
intStartRow++;
}
}
templateSheet = excel.Workbook.Worksheets["Sheet1"];
excel.Workbook.Worksheets.Delete(templateSheet);
//strDownloadUrl = baseUrl + strTemplatePath + strProductTemplateFile;
string pathTemp = Path.GetTempFileName();
excel.SaveAs(new FileInfo(pathTemp));
strDownloadUrl = string.Format("LocalPath={0}&FileName={1}", pathTemp, "QuotationSummaryReport_SparePart.xlsx");
}
catch (Exception ex)
{
string strErrorMsg = SPlanetUtil.LogErrorCollect(ex);
}
return(strDownloadUrl);
}