private int ImportRecordFromFile(string fname)
{
string strCompanyCode = "";
string strCompanyName = "";
string strCompanyTypeID = "";
string strMemberTypeID = "";
string strNetworkID = "";
string strUserID = "";
string strSecVendorID = "";
string strBuyerCompanyTypeID = "";
string strBuyerCompanyName = "";
string strVendorClass = "";
string strVendorGroup = "";
string strVendorAdd1 = "";
string strVendorAdd2 = "";
string strVendorAdd3 = "";
string strVendorAdd4 = "";
string strPostalCode = "";
string strVendorRegNum = "";
string strPhoneNumber = "";
string strEmailID = "";
string strAccountCurrency = "";
int iCount = 0;
strCompanyTypeID = "2";
strMemberTypeID = "2";
if (Session["UserID"] != null)
{
strUserID = Session["UserID"].ToString().Trim();
SqlConnection objConn = new SqlConnection(CBSAppUtils.PrimaryConnectionString);
objConn.Open();
StreamReader MyFileStream = null;
strBuyerCompanyID = "180918";//"20944";//124529 for AnchorSafety changed to 180918 for JKS
string fileName = "";
fileName = fname;
FileInfo fi = new FileInfo(Server.MapPath(ConfigurationManager.AppSettings["UploadExcel"]) + fname);
if (fi.Exists)
{
MyFileStream = File.OpenText(Server.MapPath(ConfigurationManager.AppSettings["UploadExcel"]) + fileName);
string MyLine;
int i = 1;
while (MyFileStream.Peek() != -1)
{
i++;
MyLine = MyFileStream.ReadLine();
if (i > 0)
{
MyLine = MyLine.Replace("\\", "").Replace("\"", "");
String[] MyArray = MyLine.Split(',');
strCompanyCode = MyArray[0].ToString().Trim();
strSecVendorID = MyArray[1].ToString().Trim();
strBuyerCompanyTypeID = MyArray[2].ToString().Trim();
strBuyerCompanyName = MyArray[3].ToString().Trim();
strVendorClass = MyArray[4].ToString().Trim();
strVendorGroup = MyArray[5].ToString().Trim();
strCompanyName = MyArray[6].ToString().Trim();
strVendorAdd1 = MyArray[7].ToString().Trim();
strVendorAdd2 = MyArray[8].ToString().Trim();
strVendorAdd3 = MyArray[9].ToString().Trim();
strVendorAdd4 = MyArray[10].ToString().Trim();
strPostalCode = MyArray[11].ToString().Trim();
strVendorRegNum = MyArray[12].ToString().Trim();
strPhoneNumber = MyArray[13].ToString().Trim();
strEmailID = MyArray[14].ToString().Trim();
strAccountCurrency = MyArray[15].ToString().Trim();
if (strCompanyName.Trim() != "" && strCompanyName.ToUpper().Trim().IndexOf("VENDOR_NAME") == -1)
{
strNetworkID = System.Guid.NewGuid().ToString().Substring(0, 11).Trim();
int intRecordCount = 0;
sqlCmd = new SqlCommand("sp_ImportRecordsFromURL_NL", sqlConn);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.Add("@CompanyCode", strCompanyCode);
sqlCmd.Parameters.Add("@CompanyName", strCompanyName);
sqlCmd.Parameters.Add("@CompanyTypeID", strCompanyTypeID);
sqlCmd.Parameters.Add("@MemberTypeID", strMemberTypeID);
sqlCmd.Parameters.Add("@NetworkID", strNetworkID);
sqlCmd.Parameters.Add("@BuyerCompanyID", strBuyerCompanyID);
sqlCmd.Parameters.Add("@UserID", 0);
sqlCmd.Parameters.Add("@SecVendorID", strSecVendorID);
sqlCmd.Parameters.Add("@BuyerCompanyTypeID", strBuyerCompanyTypeID);
sqlCmd.Parameters.Add("@BuyerCompanyName", strBuyerCompanyName);
sqlCmd.Parameters.Add("@VendorClass", strVendorClass);
sqlCmd.Parameters.Add("@VendorGroup", strVendorGroup);
sqlCmd.Parameters.Add("@VendorAdd1", strVendorAdd1);
sqlCmd.Parameters.Add("@VendorAdd2", strVendorAdd2);
sqlCmd.Parameters.Add("@VendorAdd3", strVendorAdd3);
sqlCmd.Parameters.Add("@VendorAdd4", strVendorAdd4);
sqlCmd.Parameters.Add("@PostalCode", strPostalCode);
sqlCmd.Parameters.Add("@VendorRegNum", strVendorRegNum);
sqlCmd.Parameters.Add("@PhoneNumber", strPhoneNumber);
sqlCmd.Parameters.Add("@EmailID", strEmailID);
sqlCmd.Parameters.Add("@AccountCurrency", strAccountCurrency);
SqlParameter paramReturnValue = objComm.Parameters.Add("ReturnValue", SqlDbType.Int);
paramReturnValue.Direction = ParameterDirection.ReturnValue;
try
{
objComm.ExecuteNonQuery();
intRecordCount = Convert.ToInt32(objComm.Parameters["ReturnValue"].Value);
}
catch (Exception ex) { string ss = ex.Message.ToString(); }
finally
{
objComm.Dispose();
}
if (intRecordCount > 0)
{
iCount = iCount + 1;
}
strNetworkID = "";
}
}
}
}
MyFileStream.Close();
if (fi.Exists)
{
fi.Delete();
fi = null;
}
}
return(iCount);
}
// =========================================================================================================
#region ImportRecordFromFile
private int ImportRecordFromFile(string fname)
{
if (Session["UserID"] == null)
{
Response.Redirect("../close_win.aspx");
}
iCount = 0; //ch 200905
//231105 SURAJIT
String strSucess = "";
String strFailure = "";
//231105 SURAJIT
if (Session["UserID"] != null)
{
string strUserID = Session["UserID"].ToString().Trim();
SqlConnection objConn = new SqlConnection(CBSAppUtils.PrimaryConnectionString);
StreamReader MyFileStream;
//230905 SURAJIT
try
{
String fileName = RollDice().ToString().Trim() + ".CSV";
FileInfo fi = new FileInfo(Server.MapPath(ConfigurationManager.AppSettings["UploadExcel"]) + fname);
fi.CopyTo(Server.MapPath(ConfigurationManager.AppSettings["UploadExcel"]) + fileName);
FileInfo fi1 = new FileInfo(Server.MapPath(ConfigurationManager.AppSettings["UploadExcel"]) + fileName);
if (fi1.Exists)
{
MyFileStream = File.OpenText(Server.MapPath(ConfigurationManager.AppSettings["UploadExcel"]) + fileName);
//230905 SURAJIT
string MyLine;
int i = 1;
while (MyFileStream.Peek() != -1)
{
i++;
MyLine = MyFileStream.ReadLine();
string strDocumentType = "";
string strGMGCompany = "";
string strVendor = "";
string strInvoiceDate = "";
string strPONumber = "";
string strInvoiceNumber = "";
string strNetAmount = "";
string strVatAmount = "";
string strTotalAmount = "";
string strCurrency = "";
string strTimePaymentDue = "";
string strRepositoryDocumentID = "";
if (i > 0)
{
//MyLine = MyLine.Replace("\\","").Replace(" ","").Replace("\"",""); //291105
String[] MyArray = MyLine.Split(',');
strDocumentType = MyArray[3].ToString().Trim().Replace("\\", "").Replace(" ", "").Replace("\"", "");
strGMGCompany = MyArray[5].ToString().Trim().Replace("\\", "").Replace(" ", "").Replace("\"", "");
strVendor = MyArray[7].ToString().Trim().Replace("\\", "").Replace(" ", "").Replace("\"", "");
strInvoiceDate = MyArray[9].ToString().Trim().Replace("\\", "").Replace(" ", "").Replace("\"", "");
string invdate1 = "";
string invdate2 = "";
String[] invdate = strInvoiceDate.Split('/');
if (invdate[0].Length < 2)
{
invdate[0] = '0' + invdate[0];
}
if (invdate.Length != 3) //011205 SURAJIT
{
//strInvoiceDate
}
else
{
if (invdate.Length > 1) //301105 SURAJIT
{
if (invdate[1].Length < 2)
{
invdate[1] = '0' + invdate[1];
}
invdate1 = invdate[1];
}
else
{
invdate1 = ""; //301105 SURAJIT
}
if (invdate.Length > 2) //301105 SURAJIT
{
if (invdate[2].Length < 4)
{
invdate[2] = "20" + invdate[2];
}
invdate2 = invdate[2];
}
else
{
invdate2 = ""; //301105 SURAJIT
}
strInvoiceDate = invdate[0] + '/' + invdate1 + '/' + invdate2;
}
strPONumber = MyArray[11].ToString().Trim().Replace("\\", "").Replace(" ", "").Replace("\"", "");
strInvoiceNumber = MyArray[13].ToString().Trim().Replace("\\", "").Replace(" ", "").Replace("\"", "");
strNetAmount = MyArray[15].ToString().Trim().Replace("\\", "").Replace(" ", "").Replace("\"", "");
strVatAmount = MyArray[17].ToString().Trim().Replace("\\", "").Replace(" ", "").Replace("\"", "");
strTotalAmount = MyArray[19].ToString().Trim().Replace("\\", "").Replace(" ", "").Replace("\"", "");
strCurrency = MyArray[21].ToString().Trim().Replace("\\", "").Replace(" ", "").Replace("\"", "");
strTimePaymentDue = MyArray[23].ToString().Trim().Replace("\\", "").Replace(" ", "").Replace("\"", "");
strRepositoryDocumentID = MyArray[24].ToString().Trim(); //.Replace("\\","").Replace(" ","").Replace("\"","");
strRepositoryDocumentID = strRepositoryDocumentID.ToLower().Replace("\\\\off-gmg-fin\\invoicerep\\postprocess\\", ""); //http://invoices.gmgradio.com//");
strRepositoryDocumentID = strRepositoryDocumentID.ToLower().Replace("\\\\off-gmg-fin\\invoicerep\\preprocess\\", ""); //http://invoices.gmgradio.com//");
//230905 SURAJIT
{
//230905 SURAJIT
SqlCommand objComm = new SqlCommand("stpAddNewRecordFromExcelIntoInvoice", objConn);
objComm.CommandType = CommandType.StoredProcedure;
objComm.Parameters.Add("@DocumentType", strDocumentType); //030905 SURAJIT
objComm.Parameters.Add("@GMGCompany", strGMGCompany);
objComm.Parameters.Add("@Vendor", strVendor);
objComm.Parameters.Add("@InvoiceDate", strInvoiceDate);
objComm.Parameters.Add("@PONumber", strPONumber);
objComm.Parameters.Add("@InvoiceNumber", strInvoiceNumber);
objComm.Parameters.Add("@NetAmount", strNetAmount);
objComm.Parameters.Add("@VatAmount", strVatAmount);
objComm.Parameters.Add("@TotalAmount", strTotalAmount);
objComm.Parameters.Add("@Currency", strCurrency);
objComm.Parameters.Add("@TimePaymentDue", strTimePaymentDue);
objComm.Parameters.Add("@RepositoryDocumentID", strRepositoryDocumentID);
objComm.Parameters.Add("@UserID", strUserID);
SqlParameter paramReturnValue = objComm.Parameters.Add("ReturnValue", SqlDbType.Int);
paramReturnValue.Direction = ParameterDirection.ReturnValue;
//291105
int intRecordCount = 0;
String msgErr = "";
if (strGMGCompany != "" && invdate.Length > 2) //ch 011205 SURAJIT
{
try
{
//291105
objConn.Open();
objComm.ExecuteNonQuery();
}
catch (System.Exception Ex0)
{
msgErr = Ex0.Message;
intRecordCount = 0;
} //291105
intRecordCount = Convert.ToInt32(objComm.Parameters["ReturnValue"].Value);
}
objComm.Dispose();
objConn.Close();
if (intRecordCount > 0)
{
iCount = iCount + 1;
strSucess += strInvoiceNumber + ","; //231105 SURAJIT
}
//231105 SURAJIT
else
{
strFailure += strInvoiceNumber + ",";
CreateLog(fi, fileName, i, strDocumentType, strUserID, strGMGCompany, strInvoiceDate, strVendor, strInvoiceNumber);
}
//231105 SURAJIT
}//230905 SURAJIT
}
totCount = i; //200905 SURAJIT
}
MyFileStream.Close();
//230905 SURAJIT
if (fi.Exists)
{
fi.Delete();
}
if (fi1.Exists)
{
fi1.Delete();
}
}
else
{
lblErr.Text = "File is already exclusively open by another user. Please close the file and try again.";
}
}
catch (System.Exception Ex1)
{
lblErr.Text = Ex1.Message;
}
//230905 SURAJIT
}
return(iCount);
}