private void SaveContent(string file) { OracleConnection con = OraDBConnection.ConnectionOpen(); //truncate tables OraDBConnection.ExecQryOnConnection(con, "TRUNCATE TABLE CADRE.CHARGESHEETDATA"); OraDBConnection.ExecQryOnConnection(con, "TRUNCATE TABLE CADRE.CHARGESHEETDATA_ERRORS"); using (TextFieldParser MyParser = new TextFieldParser(file)) { MyParser.TextFieldType = FieldType.Delimited; MyParser.SetDelimiters(","); MyParser.TrimWhiteSpace = true; MyParser.HasFieldsEnclosedInQuotes = true; //skip header MyParser.ReadLine(); while (!MyParser.EndOfData) { try { SaveContentLines(con, MyParser.LineNumber, MyParser.ReadFields()); } catch (Exception ex) { OraDBConnection.ExecQryOnConnection(con, string.Format("INSERT INTO CADRE.CHARGESHEETDATA_ERRORS VALUES({0},'{1}')", MyParser.ErrorLineNumber, ex.Message)); } } } OraDBConnection.ConnectionClose(con); //delete uploaded file System.IO.File.Delete(file); lnkSuccess.Text = OraDBConnection.GetScalar("select count(*) as cnt from CADRE.CHARGESHEETDATA"); lnkError.Text = OraDBConnection.GetScalar("select count(*) as cnt from CADRE.CHARGESHEETDATA_ERRORS"); lblStatus.Text = "Completed"; }
void UploadSAP(common.Categs categ) { string path = string.Empty; string cypherText; string plainText; string[] lines; int oracle_ins = 0; int oracle_dup = 0; int oracle_err = 0; int start = 0; string userID = Session["userID"].ToString(); //string empID = Session["empID"].ToString(); string dtUpload = DateTime.Now.ToString(common.dtFmtDotNet); string strExtension = string.Empty; StringBuilder sbsql = new StringBuilder(2000); OracleConnection con; string[] qtdFields; const int POS_SCHMRDT = 7; const int POS_PRVMRDT = 24; const int POS_CURMRDT = 18; const int POS_BILLDT = 49; const int POS_DUEDT = 50; //int posSancLoad = 80; //capture sessionid hidSID.Value = System.Guid.NewGuid().ToString(); if (FileUpload1.HasFile) { strExtension = System.IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToLower(); if (strExtension == ".enc") { path = string.Format("C:/SBM_Bills/BILL_{0}.txt", hidSID.Value); //path = Server.MapPath(string.Format("./bills/BILL_{0}.txt", hidSID.Value)); FileUpload1.SaveAs(path); } else { lblMessage.Text = "Invalid File"; return; } } //open file try { cypherText = System.IO.File.ReadAllText(path); plainText = common.Decrypt(cypherText); lines = plainText.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries); } catch (Exception ex) { lblMessage.Text = "Error Reading File: " + ex.Message; return; } //delete uploaded file System.IO.File.Delete(path); if (lines.Length == 0) { lblMessage.Text = "No Record to Upload"; return; } //skip header line, header lines end with H in the last field start = (lines[0].Split(categ.delimiter)[6] == "H") ? 1 : 0; //open connection to oracle con = OraDBConnection.ConnectionOpen(); for (int line = start; line < lines.Length; line++) { //sanitize line lines[line] = lines[line].Replace("'", "").Replace("--", ""); //get fields string[] fields = lines[line].Split(categ.delimiter); #region invalid_record_check //check the field length for every record //to avoid crash if sbm m/c malfunctions //Supporting SBM Reading Data for MS/SP/Temp Records: //only accept this file if //1. for SAP_SBM_READING type MRU field contains the word "READING" //2. for other types MRU field doesn't containg the word "READING" if (fields.Length != categ.numFields || (categ.categName == "SAP_SBM_READING" && fields[categ.posMRU].Trim().ToUpper() != common.strReading) || (categ.categName != "SAP_SBM_READING" && fields[categ.posMRU].Trim().ToUpper() == common.strReading)) { //if its first line then exit as its an invalid file if (line == start) { lblMessage.Text = "Error. Invalid File."; return; } else { sbsql.Clear(); oracle_err++; sbsql.AppendFormat("INSERT INTO ONLINEBILL.DUPBILL" + "(LINENO, ACCOUNTNO, BILLCYCLE, BILLYEAR, SESSIONID, DATED, TYPE) " + "VALUES({0},'{1}','{2}','{3}','{4}',to_date('{5}','{6}'),'{7}')", line + 1, common.strErrStyle, common.strErrStyle, common.strErrStyle, hidSID.Value, dtUpload, common.dtFmtOracle, common.strErrLetter); OraDBConnection.ExecQryOnConnection(con, sbsql.ToString()); continue; } } #endregion //reset sbsql sbsql.Clear(); sbsql.AppendFormat("INSERT INTO {0} VALUES (", categ.tableName); //trim the PK fields to remove any whitespace fields[categ.posAccountNo] = fields[categ.posAccountNo].Trim(); fields[categ.posBillCycle] = fields[categ.posBillCycle].Trim(); fields[categ.posBillYear] = fields[categ.posBillYear].Trim(); //make the insert query qtdFields = fields.Select(n => "'" + n + "'").ToArray(); qtdFields[POS_SCHMRDT] = string.Format("to_date({0},'dd/mm/yyyy')", qtdFields[POS_SCHMRDT]); qtdFields[POS_PRVMRDT] = string.Format("to_date({0},'dd/mm/yyyy')", qtdFields[POS_PRVMRDT]); qtdFields[POS_CURMRDT] = string.Format("to_date({0},'dd-mm-yyyy')", qtdFields[POS_CURMRDT]); qtdFields[POS_BILLDT] = string.Format("to_date({0},'dd-mm-yyyy')", qtdFields[POS_BILLDT]); qtdFields[POS_DUEDT] = string.Format("to_date({0},'dd-mm-yyyy')", qtdFields[POS_DUEDT]); sbsql.Append(string.Join(",", qtdFields)); //add userID, empID, date_upload, synched, syncmsg, syncdt as NULL at last, we already have comma and space at the end of string //add semicolon at end of query to enable it to run in atomic BEGIN END block sbsql.AppendFormat(",'{0}', '{1}', to_date('{2}','{3}'), NULL, NULL, NULL); ", userID, '0', dtUpload, common.dtFmtOracle); //insert into oracle //Primary Key: MR_DOC_NO //to handle dups we let the exception come and then continue //in case of error, we record the info about record containing error and continue //append merge query, with semicolon at end to make part of BEGIN END block //not to merge in case of SAP_SBM_READING if (categ.categName != "SAP_SBM_READING") { sbsql.AppendFormat("merge into onlinebill.mast_account m1 using " + "(select '{0}' as acno from dual) d on (m1.account_no=d.acno) " + "when matched then update set m1.table_name = '{1}', m1.cname = '{2}', m1.category = '{3}', if_sap = 'Y', m1.code_sdiv='{4}', updatedt = sysdate " + "when not matched then insert (m1.account_no, m1.table_name, m1.cname, m1.category, m1.if_sap, m1.code_sdiv, updatedt) values(d.acno,'{1}','{2}','{3}','Y','{4}', sysdate); ", fields[categ.posAccountNo], categ.tableName.ToUpper().Trim(), fields[categ.posCname].ToUpper().Trim(), fields[categ.posCategory].ToUpper().Trim(), fields[categ.posCode_sdiv].Trim()); } try { //make atomic transaction and execute OraDBConnection.ExecQryOnConnection(con, string.Format("BEGIN {0} END;", sbsql.ToString())); oracle_ins++; } catch (Exception ex) { sbsql.Clear(); if (ex.Message.Contains("ORA-00001:")) { oracle_dup++; sbsql.AppendFormat("INSERT INTO ONLINEBILL.DUPBILL" + "(LINENO, ACCOUNTNO, BILLCYCLE, BILLYEAR, SESSIONID, DATED, TYPE) " + "VALUES({0},'{1}','{2}','{3}','{4}',to_date('{5}','{6}'),'{7}')", line + 1, fields[categ.posAccountNo], fields[categ.posBillCycle], fields[categ.posBillYear], hidSID.Value, dtUpload, common.dtFmtOracle, common.strDupLetter); } else { oracle_err++; sbsql.AppendFormat("INSERT INTO ONLINEBILL.DUPBILL" + "(LINENO, ACCOUNTNO, BILLCYCLE, BILLYEAR, SESSIONID, DATED, TYPE) " + "VALUES({0},'{1}','{2}','{3}','{4}',to_date('{5}','{6}'),'{7}')", line + 1, common.strErrStyle, common.strErrStyle, common.strErrStyle, hidSID.Value, dtUpload, common.dtFmtOracle, common.strErrLetter); } OraDBConnection.ExecQryOnConnection(con, sbsql.ToString()); } } OraDBConnection.ConnectionClose(con); //enable Export Button if dup rows or error rows btnExport.Visible = (oracle_dup > 0 || oracle_err > 0); //show success message lblMessage.Text = String.Format("Done. Total Rows {0}. Inserted {1}. Duplicate {2}. Error {3}", lines.Length, oracle_ins, oracle_dup, oracle_err); //insert summary in userrec InsertUserRec(oracle_ins, oracle_dup, oracle_err, dtUpload, categ.categName, common.strSAP.ToUpper()); }
void UploadNonSAP(common.Categs categ) { string path = string.Empty; string[] lines; int oracle_ins = 0; int oracle_dup = 0; int oracle_err = 0; int start = 0; string userID = Session["userID"].ToString(); //string empID = Session["empID"].ToString(); string dtUpload = DateTime.Now.ToString(common.dtFmtDotNet); string strExtension = string.Empty; OracleConnection con; string[] qtdFields; StringBuilder sbsql = new StringBuilder(2000); int Check_NumFields = categ.numFields; string sql_backup; //capture sessionid hidSID.Value = System.Guid.NewGuid().ToString(); if (FileUpload1.HasFile) { strExtension = System.IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToLower(); if (strExtension == ".txt" || strExtension == ".web") { path = string.Format("C:/SBM_Bills/BILL_{0}.txt", hidSID.Value); //path = Server.MapPath(string.Format("./bills/BILL_{0}.txt", hidSID.Value)); FileUpload1.SaveAs(path); } else { lblMessage.Text = "Invalid File"; return; } } //open file try { lines = System.IO.File.ReadAllLines(path); } catch (Exception ex) { lblMessage.Text = "Error Reading File: " + ex.Message; return; } //delete uploaded file System.IO.File.Delete(path); //if "AccountNo" word at line 0 then start from line 1 else start from line 0 start = (lines[0].Split(categ.delimiter)[0] == "AccountNo") ? 1 : 0; con = OraDBConnection.ConnectionOpen(); for (int line = start; line < lines.Length; line++) { //sanitize line lines[line] = lines[line].Replace("'", "").Replace("--", ""); //get fields string[] fields = lines[line].Split(categ.delimiter); #region invalid_record_check /*Support for old formats*/ if (categ.categName == "DSBELOW10KW" && fields.Length == 72) { Check_NumFields = 72; } else if (categ.categName == "DSABOVE10KW" && fields.Length == 76) { Check_NumFields = 76; } else if (categ.categName == "MS" && fields.Length == 92) { Check_NumFields = 92; } else if (categ.categName == "SP" && fields.Length == 69) { Check_NumFields = 69; } //check the field length for every record //to avoid crash if sbm machine malfunctions if (fields.Length != Check_NumFields) { //if its first line then exit //as its an invalid file if (line == start) { lblMessage.Text = "Error. Invalid File."; return; } else { sbsql.Clear(); oracle_err++; sbsql.AppendFormat("INSERT INTO ONLINEBILL.DUPBILL" + "(LINENO, ACCOUNTNO, BILLCYCLE, BILLYEAR, SESSIONID, DATED, TYPE) " + "VALUES({0},'{1}','{2}','{3}','{4}',to_date('{5}','{6}'),'{7}')", line + 1, common.strErrStyle, common.strErrStyle, common.strErrStyle, hidSID.Value, dtUpload, common.dtFmtOracle, common.strErrLetter); OraDBConnection.ExecQryOnConnection(con, sbsql.ToString()); continue; } } #endregion //set/reset sbsql sbsql.Clear(); sbsql.AppendFormat("INSERT INTO {0} VALUES (", categ.tableName); //trim the PK fields to remove any whitespace fields[categ.posAccountNo] = fields[categ.posAccountNo].Trim().ToUpper(); fields[categ.posBillCycle] = fields[categ.posBillCycle].Trim(); fields[categ.posBillYear] = fields[categ.posBillYear].Trim(); //make the insert query qtdFields = fields.Select(n => "'" + n + "'").ToArray(); sbsql.Append(string.Join(",", qtdFields)); //for (int field = 0; field < fields.Length; field++) //{ // sbsql.Append("'") // .Append(fields[field]) // .Append("', "); //} //add userID, empID and date_upload at last, we already have comma and space at the end of string //sbsql.AppendFormat("'{0}', '{1}', to_date('{2}','{3}'))", userID, empID, dtUpload, common.dtFmtOracle); /*add empty values in the last fields of the tables if old formats are being used*/ //if (Check_NumFields == 72 || Check_NumFields == 76) //{ // sbsql.AppendFormat(",'','','','','','',''"); //} /*add empty values in the last fields of the tables if old formats are being used*/ if (categ.categName == "DSBELOW10KW" && fields.Length == 72) { sbsql.AppendFormat(",'','','','','','',''"); } else if (categ.categName == "DSABOVE10KW" && fields.Length == 76) { sbsql.AppendFormat(",'','','','','','',''"); } else if (categ.categName == "MS" && fields.Length == 92) { sbsql.AppendFormat(",'','','','','','','','','','','','','',''"); } else if (categ.categName == "SP" && fields.Length == 69) { sbsql.AppendFormat(",'','','','',''"); } sbsql.AppendFormat(",'{0}', '{1}', to_date('{2}','{3}')); ", userID, '0', dtUpload, common.dtFmtOracle); sql_backup = string.Empty; //sql_backup = sbsql.ToString().Replace("'", "$#$"); //insert into oracle //Composite Primary Key: ACCOUNTNO, BILLCYCLE, BILLYEAR //to handle dups we let the exception come and then continue //in case of error, we record the info about record containing error and continue //append merge query sbsql.AppendFormat("merge into onlinebill.mast_account m1 using " + "(select '{0}' as acno from dual) d on (m1.account_no=d.acno) " + "when matched then update set m1.table_name = '{1}', m1.cname = '{2}', m1.category = '{3}', if_sap = 'N', m1.code_sdiv='{4}', updatedt = sysdate " + "when not matched then insert (m1.account_no, m1.table_name, m1.cname, m1.category, m1.if_sap, m1.code_sdiv, updatedt) values(d.acno,'{1}','{2}','{3}','N','{4}', sysdate); ", fields[categ.posAccountNo].ToUpper(), categ.tableName.ToUpper().Trim(), fields[categ.posCname].ToUpper().Trim(), fields[categ.posCategory].ToUpper().Trim(), "0"); try { //make atomic transaction and execute OraDBConnection.ExecQryOnConnection(con, string.Format("BEGIN {0} END;", sbsql.ToString())); oracle_ins++; } catch (Exception ex) { sbsql.Clear(); if (ex.Message.Contains("ORA-00001:")) { oracle_dup++; sbsql.AppendFormat("INSERT INTO ONLINEBILL.DUPBILL" + "(LINENO, ACCOUNTNO, BILLCYCLE, BILLYEAR, SESSIONID, DATED, TYPE) " + "VALUES({0},'{1}','{2}','{3}','{4}',to_date('{5}','{6}'),'{7}')", line + 1, fields[categ.posAccountNo], fields[categ.posBillCycle], fields[categ.posBillYear], hidSID.Value, dtUpload, common.dtFmtOracle, common.strDupLetter); } else { oracle_err++; sbsql.AppendFormat("INSERT INTO ONLINEBILL.DUPBILL" + "(LINENO, ACCOUNTNO, BILLCYCLE, BILLYEAR, SESSIONID, DATED, TYPE, USERID, TBLNAME, QSQL) " + "VALUES({0},'{1}','{2}','{3}','{4}',to_date('{5}','{6}'),'{7}','{8}','{9}','{10}')", line + 1, common.strErrStyle, common.strErrStyle, common.strErrStyle, hidSID.Value, dtUpload, common.dtFmtOracle, common.strErrLetter, userID, categ.tableName.ToUpper().Trim(), sql_backup); //sbsql.AppendFormat("INSERT INTO ONLINEBILL.DUPBILL(LINENO, SESSIONID, DATED, TYPE) "+ // "VALUES({0},'{1}',to_date('{2}','{3}'),'{4}')", line + 1, hidSID.Value, dtUpload, common.dtFmtOracle,"E"); } OraDBConnection.ExecQryOnConnection(con, sbsql.ToString()); } } OraDBConnection.ConnectionClose(con); //enable Export Button if dup rows or error rows btnExport.Visible = (oracle_dup > 0 || oracle_err > 0); //show success message lblMessage.Text = String.Format("Done. Total Rows {0}. Inserted {1}. Duplicate {2}. Error {3}", lines.Length, oracle_ins, oracle_dup, oracle_err); //insert summary in userrec InsertUserRec(oracle_ins, oracle_dup, oracle_err, dtUpload, categ.categName, common.strNonSAP.ToUpper()); }