protected void UploadFile(FileUpload f, string DestinationTable)
{
string proj_id = Session["PROJECT_ID"].ToString();
string FileName = Path.GetFileName(f.PostedFile.FileName);
string Extension = Path.GetExtension(f.PostedFile.FileName);
string FolderPath = WebTools.SessionDataPath();
string FilePath = FolderPath + FileName;
f.SaveAs(FilePath);
// delete old data
WebTools.ExecNonQuery("DELETE FROM " + DestinationTable + " WHERE PROJECT_ID = '" + Session["PROJECT_ID"].ToString() + "'");
FileStream stream = new FileStream(FilePath, FileMode.Open, FileAccess.Read);
DataTable dt = new DataTable();
dt = ExcelImport.xlsxToDT2(stream);
dt.Columns.Add("IMPORT_BY");
foreach (DataRow r in dt.Rows)
{
r["IMPORT_BY"] = Session["USER_NAME"].ToString();
}
ExcelImport.ImportDataTable(dt, DestinationTable, "", "PROJECT_ID", proj_id);
}
protected void UploadETAFile()
{
try
{
string proj_id = Session["PROJECT_ID"].ToString();
string FileName = Path.GetFileName(matEtaUploader.PostedFile.FileName);
string Extension = Path.GetExtension(matEtaUploader.PostedFile.FileName);
string FolderPath = WebTools.SessionDataPath();
string FilePath = FolderPath + FileName;
matEtaUploader.SaveAs(FilePath);
// delete old data
WebTools.ExecNonQuery("DELETE FROM TBL_TEMP_SML_MAT_ETA WHERE PROJECT_ID = '" + Session["PROJECT_ID"].ToString() + "'");
FileStream stream = new FileStream(FilePath, FileMode.Open, FileAccess.Read);
DataTable dt = new DataTable();
dt = ExcelImport.xlsxToDT2(stream);
ExcelImport.ImportDataTable(dt, "TBL_TEMP_SML_MAT_ETA", "", "PROJECT_ID", proj_id);
}
catch (Exception ex)
{
Master.show_error("Error ETA File : " + ex.Message);
}
}
public void ImportTable(string table_name, string file_path, string project_field)
{
WebTools.ExecNonQuery("DELETE FROM " + table_name);
FileStream stream;
DataTable dt;
stream = new FileStream(file_path, FileMode.Open, FileAccess.Read);
dt = new DataTable();
dt = ExcelImport.xlsxToDT2(stream);
ExcelImport.ImportDataTable(dt, table_name, "", project_field, "1");
}
protected void btnImport_Click(object sender, EventArgs e)
{
try
{
if (RadAsyncUpload1.UploadedFiles.Count == 0)
{
Master.show_error("Please Upload File to proceed.");
return;
}
string proj_id = Session["PROJECT_ID"].ToString();
string user_id = WebTools.GetExpr("USER_ID", "USERS", " WHERE USER_NAME ='" + Session["USER_NAME"] + "'");
//string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
//string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = WebTools.SessionDataPath();
string FileName = Path.GetFileName(RadAsyncUpload1.UploadedFiles[0].FileName);
string Extension = Path.GetExtension(RadAsyncUpload1.UploadedFiles[0].FileName);
string FilePath = FolderPath + FileName;
//FileUpload1.SaveAs(FilePath);
RadAsyncUpload1.UploadedFiles[0].SaveAs(FilePath);
// delete old data
WebTools.ExecNonQuery("DELETE FROM PIP_PO_IMPORT WHERE PROJECT_ID = '" + Session["PROJECT_ID"].ToString() + "'");
FileStream stream = new FileStream(FilePath, FileMode.Open, FileAccess.Read);
DataTable dt = new DataTable();
dt = ExcelImport.xlsxToDT2(stream);
dt.Columns.Add("USER_ID");
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["USER_ID"] = user_id;
}
ExcelImport.ImportDataTable(dt, "PIP_PO_IMPORT", "", "PROJECT_ID", proj_id);
WebTools.ExecNonQuery("BEGIN PKG_BULK_IMPORT.PROC_PO_IMPORT('" + user_id + "') ; END;");
Master.show_success("PO Data Imported Successfully.");
}
catch (Exception ex)
{
Master.show_error(ex.Message);
}
}
protected void btnImport_Click(object sender, EventArgs e)
{
try
{
if (!FileUpload1.HasFile)
{
return;
}
string proj_id = Session["PROJECT_ID"].ToString();
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = WebTools.SessionDataPath();
string FilePath = FolderPath + FileName;
FileUpload1.SaveAs(FilePath);
// delete old data
WebTools.ExecNonQuery("DELETE FROM TBL_ISO_ATTRIB_IMP WHERE PROJECT_ID = '" + Session["PROJECT_ID"].ToString() + "'");
FileStream stream = new FileStream(FilePath, FileMode.Open, FileAccess.Read);
DataTable dt = new DataTable();
dt = ExcelImport.xlsxToDT2(stream);
ExcelImport.ImportDataTable(dt, "TBL_ISO_ATTRIB_IMP", "", "PROJECT_ID", proj_id);
WebTools.ExecNonQuery("BEGIN " +
"PKG_PAGE_VALIDATION.PROC_UDPATE_ISO_ATTRIBUTE; " +
" END;");
string ErrorCount = WebTools.CountExpr("ISO_TITLE1", "TBL_ISO_ATTRIB_IMP", " ISO_ID IS NULL OR ERROR_MSG IS NOT NULL");
Master.show_success("Isometric Attribute Data Imported Successfully with " + ErrorCount + " Errors.");
if (Convert.ToInt32(ErrorCount) > 0)
{
linkDownloadError.Visible = true;
}
}
catch (Exception ex)
{
Master.show_error(ex.Message);
}
}
protected void btnImport_Click(object sender, EventArgs e)
{
try
{
//if (!FileUpload1.HasFile) return;
if (RadAsyncUpload1.UploadedFiles.Count == 0)
{
Master.show_error("Please Upload File to proceed.");
return;
}
string user_id = WebTools.GetExpr("USER_ID", "USERS", "USER_NAME='" + Session["USER_NAME"].ToString() + "'");
string proj_id = Session["PROJECT_ID"].ToString();
string FolderPath = WebTools.SessionDataPath();
string FileName = Path.GetFileName(RadAsyncUpload1.UploadedFiles[0].FileName);
string Extension = Path.GetExtension(RadAsyncUpload1.UploadedFiles[0].FileName);
string FilePath = FolderPath + FileName;
RadAsyncUpload1.UploadedFiles[0].SaveAs(FilePath);
WebTools.ExecNonQuery("DELETE FROM PIP_BULK_JC_SHOP_IMPORT WHERE PROJECT_ID = '" + Session["PROJECT_ID"].ToString() + "' AND USER_ID=" + user_id);
FileStream stream = new FileStream(FilePath, FileMode.Open, FileAccess.Read);
DataTable dt = new DataTable();
dt = ExcelImport.xlsxToDT2(stream);
DataColumn user_id_col = new DataColumn("USER_ID", typeof(int));
user_id_col.DefaultValue = int.Parse(user_id);
dt.Columns.Add(user_id_col);
ExcelImport.ImportDataTable(dt, "PIP_BULK_JC_SHOP_IMPORT", "", "PROJECT_ID", proj_id);
Master.show_success("Proceed to check status");
btnProceed.Visible = true;
}
catch (Exception ex)
{
Master.show_error(ex.Message);
}
}
protected void btnImport_Click(object sender, EventArgs e)
{
try
{
//if (!FileUpload1.HasFile) return;
if (RadAsyncUpload1.UploadedFiles.Count == 0)
{
Master.show_error("Please Upload File to proceed.");
return;
}
string proj_id = Session["PROJECT_ID"].ToString();
//string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
//string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = WebTools.SessionDataPath();
string FileName = Path.GetFileName(RadAsyncUpload1.UploadedFiles[0].FileName);
string Extension = Path.GetExtension(RadAsyncUpload1.UploadedFiles[0].FileName);
string FilePath = FolderPath + FileName;
//FileUpload1.SaveAs(FilePath);
RadAsyncUpload1.UploadedFiles[0].SaveAs(FilePath);
// delete old data
WebTools.ExecNonQuery("DELETE FROM TEMP_TBL_IDF_MTO WHERE PROJECT_ID = '" + Session["PROJECT_ID"].ToString() + "'");
FileStream stream = new FileStream(FilePath, FileMode.Open, FileAccess.Read);
DataTable dt = new DataTable();
dt = ExcelImport.xlsxToDT2(stream);
ExcelImport.ImportDataTable(dt, "TEMP_TBL_IDF_MTO", "", "PROJECT_ID", proj_id);
WebTools.ExecNonQuery("BEGIN PKG_PAGE_VALIDATION.proc_update_idf_mto_data; END;");
Master.show_success("IDF MTO Data Imported Successfully.");
}
catch (Exception ex)
{
Master.show_error(ex.Message);
}
}
protected void ImportData(string tablename, string exp_type)
{
//tablename = "PIP_BULK_EXPORT_FILE";
if (RadAsyncUpload1.UploadedFiles.Count == 0)
{
return;
}
string proj_id = Session["PROJECT_ID"].ToString();
string FileName = Path.GetFileName(RadAsyncUpload1.UploadedFiles[0].FileName);
string Extension = Path.GetExtension(RadAsyncUpload1.UploadedFiles[0].FileName);
string FolderPath = WebTools.SessionDataPath();
string sql = "DELETE FROM " + tablename + " WHERE USER_NAME = '" + Session["USER_NAME"] + "'";
try
{
//WebTools.ExeSql(sql);
string FilePath = FolderPath + FileName;
RadAsyncUpload1.UploadedFiles[0].SaveAs(FilePath);
FileStream stream = new FileStream(FilePath, FileMode.Open, FileAccess.Read);
DataTable dt = new DataTable();
dt = ExcelImport.xlsxToDT2(stream);
dt.Columns.Add("USER_NAME", typeof(System.String));
dt.Columns.Add("EXPORT_OPTION", typeof(System.String));
foreach (DataRow row in dt.Rows)
{
row["USER_NAME"] = Session["USER_NAME"].ToString();
row["EXPORT_OPTION"] = exp_type;
}
ExcelImport.ImportDataTable(dt, tablename, "", "PROJECT_ID", proj_id);
}
catch (Exception ex)
{
Master.ShowError(ex.Message);
}
}
protected void btnImport_Click(object sender, EventArgs e)
{
try
{
if (!FileUpload1.HasFile)
{
return;
}
string proj_id = Session["PROJECT_ID"].ToString();
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = WebTools.SessionDataPath();
string FilePath = FolderPath + FileName;
FileUpload1.SaveAs(FilePath);
// delete old data
WebTools.ExecNonQuery("DELETE FROM PIP_LINE_MTO WHERE PROJECT_ID = '" + Session["PROJECT_ID"].ToString() + "'");
FileStream stream = new FileStream(FilePath, FileMode.Open, FileAccess.Read);
DataTable dt = new DataTable();
dt = ExcelImport.xlsxToDT2(stream);
ExcelImport.ImportDataTable(dt, "PIP_LINE_MTO", "", "PROJECT_ID", proj_id);
WebTools.ExecNonQuery("BEGIN " +
"PKG_PAGE_VALIDATION.UPDATE_LINE_MTO('" + Session["PROJECT_ID"].ToString() + "'); " +
" END;");
Master.show_success("LINE MTO Data Imported Successfully.");
}
catch (Exception ex)
{
Master.show_error(ex.Message);
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
try
{
if (!FileUpload1.HasFile)
{
return;
}
string proj_id = Session["PROJECT_ID"].ToString();
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = WebTools.SessionDataPath();
string FilePath = FolderPath + FileName;
FileUpload1.SaveAs(FilePath);
if (RadioButtonList1.SelectedValue == "0")
{
// delete old data
WebTools.ExecNonQuery("DELETE FROM PIP_PPCS_ADD_MAT WHERE PROJECT_ID = '" + Session["PROJECT_ID"].ToString() + "'");
}
FileStream stream = new FileStream(FilePath, FileMode.Open, FileAccess.Read);
DataTable dt = new DataTable();
dt = ExcelImport.xlsxToDT2(stream);
ExcelImport.ImportDataTable(dt, "PIP_PPCS_ADD_MAT", "", "PROJECT_ID", proj_id);
Master.show_success("Data Imported Successfully.");
}
catch (Exception ex)
{
Master.show_error(ex.Message);
}
}
protected void btnValidate_Click(object sender, EventArgs e)
{
if (!FileUpload1.HasFile)
{
return;
}
string proj_id = Session["PROJECT_ID"].ToString();
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = WebTools.SessionDataPath();
string FilePath = FolderPath + FileName;
FileUpload1.SaveAs(FilePath);
string tbl_name = WebTools.GetExpr("IMP_REP_TABLE", "PIP_BULK_IMPORT_REP", " IMP_REP_ID=" + Request.QueryString["IMPORT_ID"]);
string user_id = WebTools.GetExpr("USER_ID", "USERS", " Upper(USER_NAME)='" + Session["USER_NAME"].ToString().ToUpper() + "'");
string proj_field = WebTools.GetExpr("IMP_PROJ_ID_FIELD", "PIP_BULK_IMPORT_REP", " IMP_REP_ID=" + Request.QueryString["IMPORT_ID"]);
// delete old data
WebTools.ExecNonQuery("DELETE FROM " + tbl_name + " Where USER_ID = '" + user_id + "'");
FileStream stream;
DataTable dt;
stream = new FileStream(FilePath, FileMode.Open, FileAccess.Read);
dt = new DataTable();
dt = ExcelImport.xlsxToDT2(stream);
dt.Columns.Add("USER_ID");
foreach (DataRow r in dt.Rows)
{
r["USER_ID"] = user_id;
}
ExcelImport.ImportDataTable(dt, tbl_name, "", proj_field, proj_id);
try
{
//WebTools.ExecNonQuery()
string valid_pkg = WebTools.GetExpr("IMP_VALIDATE_PKG", "PIP_BULK_IMPORT_REP", " IMP_REP_ID=" + Request.QueryString["IMPORT_ID"]);
string upd_pkg = WebTools.GetExpr("IMP_UPDATE_PKG", "PIP_BULK_IMPORT_REP", " IMP_REP_ID=" + Request.QueryString["IMPORT_ID"]);
if (valid_pkg.Trim().Length > 0)
{
WebTools.ExecNonQuery("BEGIN " + valid_pkg + "(" + user_id + "); " +
" END;");
}
if (upd_pkg.Trim().Length > 0)
{
WebTools.ExecNonQuery("BEGIN " + upd_pkg + "(" + user_id + "); " +
" END;");
}
//string user_id = WebTools.GetExpr("USER_ID", "USERS", " USER_NAME='" + Session["USER_NAME"].ToString() + "'");
string sql = WebTools.GetExpr("GRID_SQL_TEXT", "PIP_BULK_IMPORT_REP", " IMP_REP_ID=" + Request.QueryString["IMPORT_ID"]);
itemsDataSource.SelectCommand = sql.Replace("PARAM", user_id);
sql = WebTools.GetExpr("IMP_ERR_LOG_SQL", "PIP_BULK_IMPORT_REP", " IMP_REP_ID=" + Request.QueryString["IMPORT_ID"]);
itemsErrorSource.SelectCommand = sql.Replace("PARAM", user_id);
linkImportFormat.NavigateUrl = WebTools.GetExpr("SAMPLE_FILE_URL", "PIP_BULK_IMPORT_REP", " IMP_REP_ID=" + Request.QueryString["IMPORT_ID"]);
itemsGrid.Rebind();
string err_table = WebTools.GetExpr("IMP_REP_TABLE", "PIP_BULK_IMPORT_REP", " IMP_REP_ID=" + Request.QueryString["IMPORT_ID"]);
int totalRows = dt.Rows.Count - 1;
int ErrorRows = int.Parse(WebTools.CountExpr("STATUS", err_table, " USER_ID = " + user_id + "AND STATUS IS NOT NULL"));
if (ErrorRows > 0)
{
Master.ShowError("Total Records : " + totalRows + " <br/> Successful : " + (totalRows - ErrorRows) + " <br/> Errors : " + ErrorRows);
}
else
{
Master.ShowMessage("Total Records : " + totalRows + " <br/> Successful : " + (totalRows - ErrorRows));
}
}
catch (Exception ex)
{
Master.ShowError(ex.Message);
}
finally
{
stream.Close();
dt.Dispose();
}
}
protected void btnImport_Click(object sender, EventArgs e)
{
try
{
//if (!FileUpload1.HasFile) return;
if (RadAsyncUpload1.UploadedFiles.Count == 0)
{
Master.show_error("Please Upload File to proceed.");
return;
}
string ImportOption = ImportOptions.SelectedValue;
string proj_id = Session["PROJECT_ID"].ToString();
//string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
//string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = WebTools.SessionDataPath();
string FileName = Path.GetFileName(RadAsyncUpload1.UploadedFiles[0].FileName);
string Extension = Path.GetExtension(RadAsyncUpload1.UploadedFiles[0].FileName);
string FilePath = FolderPath + FileName;
//FileUpload1.SaveAs(FilePath);
RadAsyncUpload1.UploadedFiles[0].SaveAs(FilePath);
// delete old data
FileStream stream = new FileStream(FilePath, FileMode.Open, FileAccess.Read);
DataTable dt = new DataTable();
dt = ExcelImport.xlsxToDT2(stream);
switch (ImportOption)
{
case "ADD":
break;
case "REPLACE":
DataView view = new DataView(dt);
DeleteExisting(view.ToTable(true, "IRN_NO"));
break;
case "REIMPORT":
WebTools.ExecNonQuery("DELETE FROM PO_VENDOR_DATA WHERE PROJECT_ID = '" + Session["PROJECT_ID"].ToString() + "'");
break;
}
dt.Columns.Add("CREATE_DATE");
dt.Columns.Add("CREATE_BY");
foreach (DataRow r in dt.Rows)
{
r["CREATE_DATE"] = System.DateTime.Today;
r["CREATE_BY"] = Session["USER_NAME"];
}
ExcelImport.ImportDataTable(dt, "PO_VENDOR_DATA", "", "PROJECT_ID", proj_id);
//WebTools.ExecNonQuery("BEGIN PKG_PAGE_VALIDATION.proc_update_idf_mto_data; END;");
Master.show_success("Vendor Data Imported Successfully.");
}
catch (Exception ex)
{
Master.show_error(ex.Message);
}
}
