Boolean ImportFile(List <string> filepaths, List <string> sources, string wiredt)
{
string _yrmo = ddlYrmo.SelectedItem.Text;
bool importStat = false;
VWA_ExcelImport tObj = new VWA_ExcelImport();
DataSet ds = new DataSet(); ds.Clear();
int _totalcounter = 0;
for (int i = 0; i < filepaths.Count; i++)
{
VWAImportDAL.Rollback(sources[i], _yrmo);
ds = tObj.importRemittance(filepaths[i], sources[i]);
_counter = tObj.parseRemittance(ds, _yrmo, sources[i]);
VWAImportDAL.insertImportStatus(_yrmo, sources[i]);
SetImportStatus(sources[i]);
_totalcounter += _counter;
}
VWAImportDAL.updateRemitWireDt(wiredt, _yrmo);
VWA_Results.SavePrintFiles(source, _yrmo);
Session["taskId"] = Convert.ToInt32(Session["taskId"]) + 1;
Audit.auditUserTaskI(Session.SessionID, Session["mid"].ToString(), Session["taskId"].ToString(), "VWA", "VWA Imports", "VWA_remit", "Remittance Reports", _yrmo, _totalcounter);
importStat = true;
return(importStat);
}
protected void btn_manImport_Click(object sender, EventArgs e)
{
string yrmo = ddlYrmo.SelectedItem.Text;
lbl_error.Text = "";
lblError1.Text = "";
if (Page.IsValid)
{
if (txtPrevYRMO.Visible)
{
lbl_error.Text = "Enter YRMO in format 'yyyymm'";
return;
}
string strFilePath1 = "";
if (FileUpload1.GotFile)
{
try
{
string fn = System.IO.Path.GetFileName(FileUpload1.FilePost.FileName);
strFilePath1 = Server.MapPath("~/uploads/") + fn;
if (File.Exists(strFilePath1))
{
File.Delete(strFilePath1);
}
FileUpload1.FilePost.SaveAs(strFilePath1);
if (ImportFile(strFilePath1))
{
bindResults();
VWA_Results.SavePrintFiles(source, yrmo);
MultiView1.SetActiveView(view_result);
lbl_result.Text = "Import completed successfully -- " + _counter + " records imported";
}
}
catch (Exception ex)
{
resultDiv.Visible = false;
VWAImportDAL.Rollback(source, yrmo);
lbl_error.Text = "Error - " + ex.Message;
}
finally
{
if (File.Exists(strFilePath1))
{
File.Delete(strFilePath1);
}
FileUpload1.FilePost.InputStream.Flush();
FileUpload1.FilePost.InputStream.Close();
FileUpload1.FilePost.InputStream.Dispose();
}
}
}
}
protected void UpdateImportType()
{
if (VWAImportDAL.CheckAutoImport(_category, source))
{
SetAutoImport();
}
else
{
SetManImport();
}
}
private void bankReport()
{
DataSet dsresult = new DataSet();
dsresult = VWAImportDAL.getBOAData(ddlYrmo.SelectedItem.Text);
DataRow[] rows;
DataRow rowNew;
decimal _fdx, _vwa, _disab, _disabT;
_bfdxT = 0; _bvwaT = 0; _disabT = 0;
if (dsresult.Tables[0].Rows.Count > 0)
{
rows = dsresult.Tables[0].Select("[WireTo] IN ('FDX','VWA','DisAb')");
foreach (DataRow dr in rows)
{
_fdx = 0; _vwa = 0; _disab = 0;
rowNew = tbSum1.NewRow();
rowNew["Bank Post Date"] = dr["PostDate"];
if (dr["WireTo"].ToString().Equals("FDX"))
{
_fdx = Convert.ToDecimal(dr["Amount"]);
}
if (dr["WireTo"].ToString().Equals("VWA"))
{
_vwa = Convert.ToDecimal(dr["Amount"]);
}
if (dr["WireTo"].ToString().Equals("DisAb"))
{
_disab = Convert.ToDecimal(dr["Amount"]);
_fdx = _disab;
}
rowNew["Bank RefNum"] = dr["RefNum"];
rowNew["Bank FDX Amount"] = _fdx.ToString().Equals("0") ? "" : _fdx.ToString();
rowNew["Bank VWA Amount"] = _vwa.ToString().Equals("0") ? "" : _vwa.ToString();
tbSum1.Rows.Add(rowNew);
}
_bfdxT =
Convert.ToDecimal(dsresult.Tables[0].Compute("SUM([Amount])", "[importType] = 'Detail' AND [WireTo] = 'FDX'").ToString());
_bvwaT =
Convert.ToDecimal(dsresult.Tables[0].Compute("SUM([Amount])", "[importType] = 'Detail' AND [WireTo] = 'VWA'").ToString());
_disabT =
Convert.ToDecimal(dsresult.Tables[0].Compute("SUM([Amount])", "[importType] = 'Detail' AND [WireTo] = 'DisAb'").ToString());
_bfdxT = _bfdxT + _disabT;
rowNew = tbSum1.NewRow();
rowNew["Bank RefNum"] = "Total:";
rowNew["Bank FDX Amount"] = _bfdxT.ToString();
rowNew["Bank VWA Amount"] = _bvwaT.ToString();
tbSum1.Rows.Add(rowNew);
}
}
void checkTranImported()
{
string _yrmo = ddlYrmo.SelectedItem.Text;
if (VWAImportDAL.PastImport("TranDtl", _yrmo))
{
lbl_err.Text = "";
}
else
{
lbl_err.Text = "VWA Transaction Detail for YRMO - " + _yrmo + " not imported!";
}
}
void SetAutoImport()
{
upload_auto.Visible = true;
upload_manually.Visible = false;
if (VWAImportDAL.GetClientFilePath(_category, source).Trim() == String.Empty)
{
lbl_clientfile.Text = "";
}
else
{
lbl_clientfile.Text = VWAImportDAL.GetClientFilePath(_category, source) + "\\" + VWA.GetInputFileName(ddlYrmo.SelectedItem.Value, source);
}
cbx_autoImport.Checked = false;
}
Boolean ImportFile(string strFilePath1)
{
string _yrmo = ddlYrmo.SelectedItem.Text;
bool importStat = false;
VWAImport vobj = new VWAImport();
VWAImportDAL.Rollback("BankStat", _yrmo);
_counter = vobj.importBOA_VWA(_yrmo, strFilePath1);
VWAImportDAL.insertImportStatus(_yrmo, source);
Session["taskId"] = Convert.ToInt32(Session["taskId"]) + 1;
Audit.auditUserTaskI(Session.SessionID, Session["mid"].ToString(), Session["taskId"].ToString(), "VWA", "VWA Imports", "VWA_Bank", "Bank Statement", _yrmo, _counter);
importStat = true;
return(importStat);
}
protected void checkPastImport()
{
string _yrmo = ddlYrmo.SelectedItem.Text;
if (VWAImportDAL.PastImport(source_OC, _yrmo))
{
lbl_suc_OC.Visible = true;
}
else
{
lbl_suc_OC.Visible = false;
}
if (VWAImportDAL.PastImport(source_Cigna, _yrmo))
{
lbl_suc_Cigna.Visible = true;
}
else
{
lbl_suc_Cigna.Visible = false;
}
if (VWAImportDAL.PastImport(source_UHC, _yrmo))
{
lbl_suc_UHC.Visible = true;
}
else
{
lbl_suc_UHC.Visible = false;
}
if (VWAImportDAL.PastImport(source_Disab, _yrmo))
{
lbl_suc_Disab.Visible = true;
}
else
{
lbl_suc_Disab.Visible = false;
}
if (VWAImportDAL.PastImport(source_Anth, _yrmo))
{
lbl_suc_Anth.Visible = true;
}
else
{
lbl_suc_Anth.Visible = false;
}
if (lbl_suc_OC.Visible || lbl_suc_Cigna.Visible || lbl_suc_UHC.Visible || lbl_suc_Disab.Visible || lbl_suc_Anth.Visible)
{
View_Result();
}
}
protected void checkPastImport()
{
string _yrmo = ddlYrmo.SelectedItem.Text;
if (VWAImportDAL.PastImport(source, _yrmo))
{
MultiView1.SetActiveView(view_reimport);
lbl_reimport.Text = "Imported already for year-month (YRMO): " + _yrmo;
}
else
{
MultiView1.SetActiveView(view_main);
UpdateImportType();
}
}
protected void bindResults()
{
clearMessages();
resultDiv.Visible = true;
string _sb1 = "", _td1 = "", _tw1 = "", _eb1 = "";
try
{
DataSet dsresult = new DataSet();
dsresult = VWAImportDAL.getBOAData(ddlYrmo.SelectedItem.Text);
foreach (DataRow dr in dsresult.Tables[0].Rows)
{
string _type = dr["Type"].ToString();
switch (_type)
{
case "Start Balance":
_sb1 = Convert.ToDecimal(dr["Amount"]).ToString("C");
break;
case "Total Deposits":
_td1 = Convert.ToDecimal(dr["Amount"]).ToString("C");
break;
case "Total Withdrawls":
_tw1 = ((-1) * Convert.ToDecimal(dr["Amount"])).ToString("C");
break;
case "End Balance":
_eb1 = Convert.ToDecimal(dr["Amount"]).ToString("C");
break;
}
}
if (dsresult.Tables[0].Rows.Count > 0)
{
//Summary
lblSBalance.Text = _sb1;
lblDeposits.Text = _td1;
lblWithdrawls.Text = _tw1;
lblEnding.Text = _eb1;
decimal _tot1 = decimal.Parse(_sb1, System.Globalization.NumberStyles.Currency)
+ decimal.Parse(_td1, System.Globalization.NumberStyles.Currency)
+ decimal.Parse(_tw1, System.Globalization.NumberStyles.Currency);
lblTotalV.Text = _tot1.ToString("C");
decimal _tot2 = _tot1 - decimal.Parse(_eb1, System.Globalization.NumberStyles.Currency);
lblFinalV.Text = _tot2.ToString("C");
if (_tot2 != 0)
{
Image3.Visible = true;
}
else
{
Image1.Visible = true;
}
//Deposits
decimal _dep1 =
decimal.Parse((dsresult.Tables[0].Compute("SUM([Amount])", "[importType] = 'Detail' AND ([Type] = 'DEP' OR [Type] = 'MSCC')")).ToString(), System.Globalization.NumberStyles.Currency);
decimal _tot3 = decimal.Parse(_td1, System.Globalization.NumberStyles.Currency) - _dep1;
lblDep11.Text = _dep1.ToString("C");
lblDep12.Text = _td1;
lblDeptotal.Text = _tot3.ToString("C");
if (_tot2 != 0)
{
Image5.Visible = true;
}
else
{
Image4.Visible = true;
}
//Deposits
decimal _withd1 =
decimal.Parse((dsresult.Tables[0].Compute("SUM([Amount])", "[importType] = 'Detail' AND ([Type] <> 'DEP' AND [Type] <> 'MSCC')")).ToString(), System.Globalization.NumberStyles.Currency);
decimal _tot4 = decimal.Parse(_tw1, System.Globalization.NumberStyles.Currency) + _withd1;
lblWithd11.Text = _withd1.ToString("C");
lblWithd12.Text = ((-1) * decimal.Parse(_tw1, System.Globalization.NumberStyles.Currency)).ToString("C");
lblWithdtotal.Text = _tot4.ToString("C");
if (_tot4 != 0)
{
Image7.Visible = true;
}
else
{
Image6.Visible = true;
}
}
}
catch (Exception ex)
{
errorDiv1.Visible = true;
lblError1.Text = ex.Message;
}
}
protected void btn_autoImport_Click(object sender, EventArgs e)
{
Impersonation _imp = new Impersonation();
string yrmo = ddlYrmo.SelectedItem.Text;
string clientfilename, clientfile, serverPath;
string serverfile = "";
lbl_error.Text = "";
lblError1.Text = "";
if (Page.IsValid)
{
if (txtPrevYRMO.Visible)
{
lbl_error.Text = "Enter YRMO in format 'yyyymm'";
return;
}
try
{
serverPath = Server.MapPath("~/uploads/");
clientfilename = VWA.GetInputFileName(yrmo, source);
serverfile = serverPath + clientfilename;
if (_imp.impersonateValidUser(Session["uid"].ToString(), "CORP", EncryptDecrypt.Decrypt(Session["pwd"].ToString())))
{
clientfile = VWAImportDAL.GetClientFilePath(_category, source) + "\\" + clientfilename;
File.Copy(clientfile, serverfile);
_imp.undoImpersonation();
}
else
{
throw new Exception("Error in accessing network location");
}
if (!(File.Exists(serverfile)))
{
throw new Exception("Unable to import file from specified location.<br/>Please check if file exists and you are logged in to the network.");
}
if (ImportFile(serverfile))
{
bindResults();
VWA_Results.SavePrintFiles(source, yrmo);
MultiView1.SetActiveView(view_result);
lbl_result.Text = "Import completed successfully -- " + _counter + " records imported";
}
else
{
throw new Exception("Unable to parse file.<br/>Please check file type and format.");
}
}
catch (Exception ex)
{
resultDiv.Visible = false;
VWAImportDAL.Rollback(source, yrmo);
lbl_error.Text = "Error - " + ex.Message;
}
finally
{
if (File.Exists(serverfile))
{
File.Delete(serverfile);
}
}
}
}
protected void btn_autoImport_Click(object sender, EventArgs e)
{
Impersonation _imp = new Impersonation();
string yrmo = ddlYrmo.SelectedItem.Text;
string clientfilename1, clientfilename2, clientfilename3, clientfilename4, clientfilename5;
string clientfile1, clientfile2, clientfile3, clientfile4, clientfile5;
string serverfile1, serverfile2, serverfile3, serverfile4, serverfile5;
List <string> filepaths = new List <string>();
List <string> sources = new List <string>();
string wiredt = tbx_autoWiredt.Text;
ErrMsgDiv1.Visible = false;
ErrMsgDiv2.Visible = false;
if (Page.IsValid)
{
if (txtPrevYRMO.Visible)
{
lbl_error1.Text = "Enter YRMO in format 'yyyymm'";
return;
}
serverfile1 = "";
serverfile2 = "";
serverfile3 = "";
serverfile4 = "";
serverfile5 = "";
try
{
clientfilename1 = VWA.GetInputFileName(yrmo, source_OC);
clientfilename2 = VWA.GetInputFileName(yrmo, source_Cigna);
clientfilename3 = VWA.GetInputFileName(yrmo, source_UHC);
clientfilename4 = VWA.GetInputFileName(yrmo, source_Disab);
clientfilename5 = VWA.GetInputFileName(yrmo, source_Anth);
serverfile1 = Server.MapPath("~/uploads/") + clientfilename1;
serverfile2 = Server.MapPath("~/uploads/") + clientfilename2;
serverfile3 = Server.MapPath("~/uploads/") + clientfilename3;
serverfile4 = Server.MapPath("~/uploads/") + clientfilename4;
serverfile5 = Server.MapPath("~/uploads/") + clientfilename5;
if (_imp.impersonateValidUser(Session["uid"].ToString(), "CORP", EncryptDecrypt.Decrypt(Session["pwd"].ToString())))
{
clientfile1 = VWAImportDAL.GetClientFilePath(_category, source) + "\\" + clientfilename1;
clientfile2 = VWAImportDAL.GetClientFilePath(_category, source) + "\\" + clientfilename2;
clientfile3 = VWAImportDAL.GetClientFilePath(_category, source) + "\\" + clientfilename3;
clientfile4 = VWAImportDAL.GetClientFilePath(_category, source) + "\\" + clientfilename4;
clientfile5 = VWAImportDAL.GetClientFilePath(_category, source) + "\\" + clientfilename5;
if (File.Exists(clientfile1) || File.Exists(clientfile2) || File.Exists(clientfile3) || File.Exists(clientfile4) || File.Exists(clientfile5) || wiredt != String.Empty)
{
if (File.Exists(clientfile1))
{
File.Copy(clientfile1, serverfile1);
filepaths.Add(serverfile1);
sources.Add(source_OC);
}
if (File.Exists(clientfile2))
{
File.Copy(clientfile2, serverfile2);
filepaths.Add(serverfile2);
sources.Add(source_Cigna);
}
if (File.Exists(clientfile3))
{
File.Copy(clientfile3, serverfile3);
filepaths.Add(serverfile3);
sources.Add(source_UHC);
}
if (File.Exists(clientfile4))
{
File.Copy(clientfile4, serverfile4);
filepaths.Add(serverfile4);
sources.Add(source_Disab);
}
if (File.Exists(clientfile5))
{
File.Copy(clientfile5, serverfile5);
filepaths.Add(serverfile5);
sources.Add(source_Anth);
}
_imp.undoImpersonation();
}
else
{
throw new Exception("Error in accessing network location");
}
if (ImportFile(filepaths, sources, wiredt))
{
View_Result();
}
else
{
throw new Exception("Unable to parse reports.<br/>Please check reports type and format.");
}
}
}
catch (Exception ex)
{
resultdiv.Visible = false;
VWAImportDAL.DeleteRemit(yrmo);
ErrMsgDiv1.Visible = true;
lbl_error1.Text = "Error - " + ex.Message;
}
finally
{
if (File.Exists(serverfile1))
{
File.Delete(serverfile1);
}
;
if (File.Exists(serverfile2))
{
File.Delete(serverfile2);
}
;
if (File.Exists(serverfile3))
{
File.Delete(serverfile3);
}
;
if (File.Exists(serverfile4))
{
File.Delete(serverfile4);
}
;
if (File.Exists(serverfile5))
{
File.Delete(serverfile5);
}
;
}
}
}
protected void btn_manImport_Click(object sender, EventArgs e)
{
ErrMsgDiv1.Visible = false;
ErrMsgDiv2.Visible = false;
string yrmo = ddlYrmo.SelectedItem.Text;
string fn1, fn2, fn3, fn4, fn5;
string strFilePath1, strFilePath2, strFilePath3, strFilePath4, strFilePath5;
List <string> filepaths = new List <string>();
List <string> sources = new List <string>();
string wiredt = tbx_manWiredt.Text;
if (Page.IsValid)
{
if (txtPrevYRMO.Visible)
{
lbl_error1.Text = "Enter YRMO in format 'yyyymm'";
return;
}
if (yrmo.Length == 0 || yrmo.Length != 6)
{
Alert.Show("Please Enter YRMO in format yyyymm");
return;
}
if (tbx_manWiredt.Text == "" || tbx_manWiredt.Text.Length == 0)
{
Alert.Show("Please Enter Wire Date in the format yyyymm");
return;
}
strFilePath1 = "";
strFilePath2 = "";
strFilePath3 = "";
strFilePath4 = "";
strFilePath5 = "";
if (FileUpload1_OC.GotFile || FileUpload1_Cigna.GotFile || FileUpload1_UHC.GotFile || FileUpload1_Disab.GotFile || FileUpload1_Anth.GotFile || wiredt != String.Empty)
{
try
{
if (FileUpload1_OC.GotFile)
{
fn1 = System.IO.Path.GetFileName(FileUpload1_OC.FilePost.FileName);
strFilePath1 = Server.MapPath("~/uploads/") + fn1;
if (File.Exists(strFilePath1))
{
File.Delete(strFilePath1);
}
FileUpload1_OC.FilePost.SaveAs(strFilePath1);
filepaths.Add(strFilePath1);
sources.Add(source_OC);
}
if (FileUpload1_Cigna.GotFile)
{
fn2 = System.IO.Path.GetFileName(FileUpload1_Cigna.FilePost.FileName);
strFilePath2 = Server.MapPath("~/uploads/") + fn2;
if (File.Exists(strFilePath2))
{
File.Delete(strFilePath2);
}
FileUpload1_Cigna.FilePost.SaveAs(strFilePath2);
filepaths.Add(strFilePath2);
sources.Add(source_Cigna);
}
if (FileUpload1_UHC.GotFile)
{
fn3 = System.IO.Path.GetFileName(FileUpload1_UHC.FilePost.FileName);
strFilePath3 = Server.MapPath("~/uploads/") + fn3;
if (File.Exists(strFilePath3))
{
File.Delete(strFilePath3);
}
FileUpload1_UHC.FilePost.SaveAs(strFilePath3);
filepaths.Add(strFilePath3);
sources.Add(source_UHC);
}
if (FileUpload1_Disab.GotFile)
{
fn4 = System.IO.Path.GetFileName(FileUpload1_Disab.FilePost.FileName);
strFilePath4 = Server.MapPath("~/uploads/") + fn4;
if (File.Exists(strFilePath4))
{
File.Delete(strFilePath4);
}
FileUpload1_Disab.FilePost.SaveAs(strFilePath4);
filepaths.Add(strFilePath4);
sources.Add(source_Disab);
}
if (FileUpload1_Anth.GotFile)
{
fn5 = System.IO.Path.GetFileName(FileUpload1_Anth.FilePost.FileName);
strFilePath5 = Server.MapPath("~/uploads/") + fn5;
if (File.Exists(strFilePath5))
{
File.Delete(strFilePath5);
}
;
FileUpload1_Anth.FilePost.SaveAs(strFilePath5);
filepaths.Add(strFilePath5);
sources.Add(source_Anth);
}
if (ImportFile(filepaths, sources, wiredt))
{
View_Result();
}
}
catch (Exception ex)
{
resultdiv.Visible = false;
VWAImportDAL.DeleteRemit(yrmo);
ErrMsgDiv1.Visible = true;
lbl_error1.Text = "Error - " + ex.Message;
}
finally
{
if (File.Exists(strFilePath1))
{
File.Delete(strFilePath1);
FileUpload1_OC.FilePost.InputStream.Flush(); FileUpload1_OC.FilePost.InputStream.Close(); FileUpload1_OC.FilePost.InputStream.Dispose();
}
if (File.Exists(strFilePath2))
{
File.Delete(strFilePath2);
FileUpload1_Cigna.FilePost.InputStream.Flush(); FileUpload1_Cigna.FilePost.InputStream.Close(); FileUpload1_Cigna.FilePost.InputStream.Dispose();
}
if (File.Exists(strFilePath3))
{
File.Delete(strFilePath3);
FileUpload1_UHC.FilePost.InputStream.Flush(); FileUpload1_UHC.FilePost.InputStream.Close(); FileUpload1_UHC.FilePost.InputStream.Dispose();
}
if (File.Exists(strFilePath4))
{
File.Delete(strFilePath4);
FileUpload1_Disab.FilePost.InputStream.Flush(); FileUpload1_Disab.FilePost.InputStream.Close(); FileUpload1_Disab.FilePost.InputStream.Dispose();
}
if (File.Exists(strFilePath5))
{
File.Delete(strFilePath5);
FileUpload1_Anth.FilePost.InputStream.Flush(); FileUpload1_Anth.FilePost.InputStream.Close(); FileUpload1_Anth.FilePost.InputStream.Dispose();
}
}
}
}
}