private int[] InsertDNCTextFile()
{
ListID = lstListsValue;
int[] ResultArray;
int Rowcnt = 0;
//string Row = "";
int badNumbers = 0;
// Byte badNumberFlag = 0;
// Byte RepeatFlag =0;
int repeatNumbers = 0;
int NoAreaCode = 0;
int notmatchedZipcode = 0;
try
{
DataSet dsCustomeFieldDetails = new DataSet();
int varcount = 0;
//DataSet StateCountry = ClsList.StateCountry_GetAll();
DataTable dtTemp = new DataTable();
ClsImportLeadsDataService clsReterive = new ClsImportLeadsDataService();
dsCustomeFieldDetails = clsReterive.ReteriveCustomeFieldDetails(Convert.ToInt64(cmbFormat[0]));
// dsCustomeFieldDetails = ClsList.GetLeadFields(Convert.ToInt64(str[0]));
DataRow[] drPhoneNumber = dsCustomeFieldDetails.Tables[0].Select("CustomFieldID = 1");
if (drPhoneNumber.Length == 0)
{
System.Windows.MessageBox.Show("LeadFormat has no phone Number Field");
}
int phoneStartpos = Convert.ToInt32(drPhoneNumber[0]["StartPosition"]);
int phonelen = Convert.ToInt32(drPhoneNumber[0]["Length"]);
DataRow[] drStateID = dsCustomeFieldDetails.Tables[0].Select("CustomFieldID = 2");
int StateStartpos =0;
int Statelen = 0;
if (drStateID.Length == 0)
{
//System.Windows.MessageBox.Show("LeadFormat has no state field");
}
else
{
StateStartpos = Convert.ToInt32(drStateID[0]["StartPosition"]);
Statelen = Convert.ToInt32(drStateID[0]["Length"]);
}
//int StateStartpos = Convert.ToInt32(drStateID[0]["StartPosition"]);
//int Statelen = Convert.ToInt32(drStateID[0]["Length"]);
DataRow[] drZipID = dsCustomeFieldDetails.Tables[0].Select("CustomFieldID = 3");
if (drZipID.Length == 0)
{
System.Windows.MessageBox.Show("LeadFormat has no ZipCode Field");
}
int ZipStartpos = Convert.ToInt32(drZipID[0]["StartPosition"]);
int Ziplen = Convert.ToInt32(drZipID[0]["Length"]);
if (drPhoneNumber.Count() > 0)
{
FileStream fs1 = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.None, 2 << 18, FileOptions.SequentialScan);
DataTable dtTimezone = new DataTable();
DataRow[] drStateCountry = null;
DataSet dsTimeZone = ClsList.TimeZone_GetAll();
dtTimezone = dsTimeZone.Tables[0];
Int64 LeadMaxcount = ClsList.GetMaxIDLeads();
Int64 MaxLeadDetail = ClsList.GetMaxLeadDetailID();
Int64 MaxLocationID = ClsList.GetMaxLocationID();
createDataTable();
int TotalColm = dsCustomeFieldDetails.Tables[0].Rows.Count;
StreamReader sr1 = new StreamReader(fs1);
SqlConnection conn = new SqlConnection(VMuktiAPI.VMuktiInfo.MainConnectionString);
SqlCommand cmd = new SqlCommand("select getdate()", conn);
conn.Open();
SqlDataReader dr1;
Byte AreaCodeFlag = 0;
DataTable dtPhoneNumber = new DataTable();
DataSet ds1 = ClsList.PhoneNumbers_GetAll(ListID);
dtPhoneNumber = ds1.Tables[0];
try
{
while (sr1.EndOfStream != true)
{
try
{
string Row1 = sr1.ReadLine();
string ZipCode1 = Row1.Substring(ZipStartpos - 1, Ziplen).Replace(" ", "");
Int64 finalzipcode1 = String.IsNullOrEmpty(ZipCode1) ? 0 : Int64.Parse(ZipCode1.Substring(0, 5));
dtBulkZipCode.Rows.Add(finalzipcode1);
}
catch (Exception ex)
{
continue;
}
}
}
catch (Exception ex)
{
VMuktiHelper.ExceptionHandler(ex, "BulkZip", "Windows1.xaml.cs");
}
SqlBulkCopy sqlBulkBulkZipCode = new SqlBulkCopy(VMuktiAPI.VMuktiInfo.MainConnectionString);
sqlBulkBulkZipCode.DestinationTableName = "ZipCodeTemp";
sqlBulkBulkZipCode.ColumnMappings.Add("ZipCode", "ZipCode");
sqlBulkBulkZipCode.BulkCopyTimeout = 1000;
sqlBulkBulkZipCode.WriteToServer(dtBulkZipCode);
sqlBulkBulkZipCode.Close();
DataSet DataFromLocation1 = ClsList.GetLocationDetail();
DataTable dtforlocation = new DataTable();
dtforlocation = DataFromLocation1.Tables[0];
DataSet ds2 = ClsList.AreaCode_GetAll();
dtTemp = ds2.Tables[0];
fs1.Close();
sr1.Close();
fs1 = null;
sr1 = null;
FileStream fs = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.None, 2 << 18, FileOptions.SequentialScan);
StreamReader sr = new StreamReader(fs);
try
{
while (sr.EndOfStream != true)
//for (Rowcnt = 0; Rowcnt < FileValueArray.Count(); Rowcnt++)
{
Rowcnt = Rowcnt + 1;
string Row = sr.ReadLine();
//FileValueArray.ElementAt(Rowcnt);
Int64 phoneNumber;
try
{
phoneNumber = Convert.ToInt64(Row.Substring(phoneStartpos - 1, phonelen).Replace("-", "").Replace(" ", ""));
}
catch (Exception exp)
{
badNumbers++;
// badNumberFlag = 1;
continue;
}
DataRow[] drRepeat = dtPhoneNumber.Select("PhoneNo = " + phoneNumber);
if (drRepeat.Length > 0)
{
repeatNumbers++;
// RepeatFlag = 1;
continue;
}
dtPhoneNumber.Rows.Add(phoneNumber);
dr1 = cmd.ExecuteReader();
dr1.Read();
string ZipCode = Row.Substring(ZipStartpos - 1, Ziplen).Replace(" ", "");
Int64 finalzipcode = String.IsNullOrEmpty(ZipCode) ? 0 : Int64.Parse(ZipCode.Substring(0, 5));
try
{
//DataSet DataFromLocation = ClsList.GetLocationID(finalzipcode);
//DataTable dt = new DataTable();
//dt = DataFromLocation.Tables[0];
//drStateCountry = dt.Select("ZipCode=" + finalzipcode);
drStateCountry = dtforlocation.Select("ZipCode=" + finalzipcode);
}
catch (Exception ex)
{
badNumbers++;
dr1.Close();
continue;
}
int condition = 0;
DataRow drLocation1 = null;
DataRow[] AreaCod = null;
DataSet ds = ClsList.AreaCode_GetAll(); // .AreaCode_GetAll();
dtTemp = ds.Tables[0];
int loopcount = dtTemp.Rows.Count;
AreaCod = dtTemp.Select("Areacode = '" + phoneNumber.ToString().Substring(0, 3) + "'");
if (AreaCod.Count() > 0)
{
condition = 1;
}
else
{
condition = 0;
}
if (condition == 1)
{
DataRow[] t1;
t1 = dtTimezone.Select("TimezoneName = '" + dtTemp.Rows[0][1].ToString() + "'");
Int64 gotIt = Int64.Parse(t1[0][1].ToString());
//Int64 SstID = Int64.Parse(drStateCountry[0][0].ToString());
//Validation from Location Table
//Kanhaiya
try
{
if (drStateCountry.Length > 0)
{
MaxLocationID = Int64.Parse(drStateCountry[0][0].ToString());
}
else
{
MaxLocationID = 0;
if (ZipNotinDB.Count() > 0)
{
if (ZipNotinDB.Contains(ZipCode))
{
}
else
{
ZipNotinDB.Add(ZipCode);
}
}
else
{
ZipNotinDB.Add(ZipCode);
}
}
}
catch (Exception ex)
{
}
if (MaxLocationID == 0)
{
notmatchedZipcode++;
}
else
{
dtLead.Rows.Add(LeadMaxcount, phoneNumber, Convert.ToInt64(cmbFormat[0]), Convert.ToDateTime(dr1[0].ToString()), VMuktiAPI.VMuktiInfo.CurrentPeer.ID, null, null, false, Convert.ToDateTime(dr1.GetValue(0)), VMuktiAPI.VMuktiInfo.CurrentPeer.ID, true, 1, ListID, MaxLocationID, 0, "Fresh", false);
int Colcnt = 0;
for (Colcnt = 0; Colcnt < TotalColm; Colcnt++)
{
Int64 fieldID = Convert.ToInt32(dsCustomeFieldDetails.Tables[0].Rows[Colcnt]["CustomFieldID"]); ;
int StartPositionTmp = Convert.ToInt32(dsCustomeFieldDetails.Tables[0].Rows[Colcnt]["StartPosition"]);
int LengthTmp = Convert.ToInt32(dsCustomeFieldDetails.Tables[0].Rows[Colcnt]["Length"]);
// string Row = FileValueArray.ElementAt(Rowcnt);
string Value = Row.Substring(StartPositionTmp - 1, LengthTmp);
dtLeadDetail.Rows.Add(MaxLeadDetail, LeadMaxcount, fieldID, Value);
MaxLeadDetail = MaxLeadDetail + 1;
}
MaxLocationID = MaxLocationID + 1;
LeadMaxcount = LeadMaxcount + 1;
Row = "";
}
}
else
{
NoAreaCode++;
}
dr1.Close();
} //While End
}
catch (Exception ex)
{
VMuktiHelper.ExceptionHandler(ex, "InsertDNCTextFile()", "ImportExe:Window1.xaml.cs");
}
finally
{
sr.Close();
fs.Close();
sr = null;
fs = null;
GC.Collect(0, GCCollectionMode.Forced);
//GC.SuppressFinalize(this);
}
SqlBulkCopy sqlBulkLocation = new SqlBulkCopy(VMuktiAPI.VMuktiInfo.MainConnectionString);
//SqlBulkCopy sqlBulkLocation = new SqlBulkCopy("Data Source=192.168.1.186\\SQLExpress;Initial Catalog=VMukti;User ID=sa;PassWord=mahavir",SqlBulkCopyOptions.FireTriggers);
sqlBulkLocation.DestinationTableName = "Location";
sqlBulkLocation.BulkCopyTimeout = 1000;
sqlBulkLocation.WriteToServer(dtLocation);
sqlBulkLocation.Close();
SqlBulkCopy sqlBulkLead = new SqlBulkCopy(VMuktiAPI.VMuktiInfo.MainConnectionString);
//SqlBulkCopy sqlBulkLocation = new SqlBulkCopy("Data Source=192.168.1.186\\SQLExpress;Initial Catalog=VMukti;User ID=sa;PassWord=mahavir",SqlBulkCopyOptions.FireTriggers);
sqlBulkLead.DestinationTableName = "Leads";
sqlBulkLead.BulkCopyTimeout = 1000;
sqlBulkLead.WriteToServer(dtLead);
sqlBulkLead.Close();
SqlBulkCopy sqlBulkLeadDetail = new SqlBulkCopy(VMuktiAPI.VMuktiInfo.MainConnectionString);
//SqlBulkCopy sqlBulkLocation = new SqlBulkCopy("Data Source=192.168.1.186\\SQLExpress;Initial Catalog=VMukti;User ID=sa;PassWord=mahavir", SqlBulkCopyOptions.FireTriggers);
sqlBulkLeadDetail.DestinationTableName = "LeadDetail";
sqlBulkLeadDetail.BulkCopyTimeout = 1000;
sqlBulkLeadDetail.WriteToServer(dtLeadDetail);
sqlBulkLeadDetail.Close();
ResultArray = new int[] { Rowcnt, badNumbers, dtLead.Rows.Count, repeatNumbers, NoAreaCode,notmatchedZipcode };
dtLocation = null;
dtLead = null;
dtLeadDetail = null;
return ResultArray;
}
else
{
ResultArray = new int[] { 0 };
return ResultArray;
}
}
catch (Exception ex)
{
VMuktiHelper.ExceptionHandler(ex, "InsertDNCTextFile(Outer)", "ImportExe:Window1.xaml.cs");
ResultArray = new int[] { 0 };
return ResultArray;
}
}
public int[] TotalCount(Int64 LeadFormatID, int ListID, string SelectedTimeZone, string FilterType, int TotalTimeZone, int CountryCode)
{
DataSet dsCustomeFieldDetails = new DataSet();
DataSet StateCountry = ClsList.StateCountry_GetAll();
DataTable dtTemp = new DataTable();
//DataRow[] drCollection = null;
if (FilterType == "TIMEZONE" || FilterType == "SITE")
{
DataSet ds = ClsList.AreaCode_GetAll(SelectedTimeZone, "in");
dtTemp = ds.Tables[0];
//drCollection = dtTemp.Select("TimezoneName IN (" + strTimezone + ")");
}
ClsImportLeadsDataService clsReterive = new ClsImportLeadsDataService();
dsCustomeFieldDetails = clsReterive.ReteriveCustomeFieldDetails(LeadFormatID);
DataRow[] drPhoneNumber = dsCustomeFieldDetails.Tables[0].Select("CustomFieldID = 1");
int phoneStartpos = Convert.ToInt32(drPhoneNumber[0]["StartPosition"]);
int phonelen = Convert.ToInt32(drPhoneNumber[0]["Length"]);
DataRow[] drStateID = dsCustomeFieldDetails.Tables[0].Select("CustomFieldID = 2");
int StateStartpos = Convert.ToInt32(drStateID[0]["StartPosition"]);
int Statelen = Convert.ToInt32(drStateID[0]["Length"]);
DataRow[] drZipID = dsCustomeFieldDetails.Tables[0].Select("CustomFieldID = 3");
int ZipStartpos = Convert.ToInt32(drZipID[0]["StartPosition"]);
int Ziplen = Convert.ToInt32(drZipID[0]["Length"]);
int[] ResultArray;
if (drPhoneNumber.Count() > 0)
{
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.None, 2 << 18, FileOptions.SequentialScan);
DataTable dtTimezone = new DataTable();
DataRow[] drStateCountry = null;
DataSet dsTimeZone = ClsList.TimeZone_GetAll();
dtTimezone = dsTimeZone.Tables[0];
Int64 LeadMaxcount = ClsList.GetMaxIDLeads();
Int64 MaxLeadDetail = ClsList.GetMaxLeadDetailID();
Int64 MaxLocationID = ClsList.GetMaxLocationID();
createDataTable();
int TotalColm = dsCustomeFieldDetails.Tables[0].Rows.Count;
StreamReader sr = new StreamReader(fs);
// int cntFile = sr.ReadToEnd().Split('\n').Count();
// string[] FileValueArray = sr.ReadToEnd().Split('\n') ;
//int[] Fields = new int[TotalColm];
/// int[] StartPosition = new int[TotalColm];
// int[] Length = new int[TotalColm];
// fs.Close();
// sr.Close();
/* for (int Colcnt = 0; Colcnt < TotalColm; Colcnt++)
{
Fields[Colcnt] = Convert.ToInt32(dsCustomeFieldDetails.Tables[0].Rows[Colcnt]["CustomFieldID"]);
StartPosition[Colcnt] = Convert.ToInt32(dsCustomeFieldDetails.Tables[0].Rows[Colcnt]["StartPosition"]);
Length[Colcnt] = Convert.ToInt32(dsCustomeFieldDetails.Tables[0].Rows[Colcnt]["Length"]);
}*/
SqlConnection conn = new SqlConnection(VMuktiAPI.VMuktiInfo.MainConnectionString);
SqlCommand cmd = new SqlCommand("select getdate()", conn);
conn.Open();
SqlDataReader dr1;
int Rowcnt = 0;
//string Row = "";
int badNumbers = 0;
// Byte badNumberFlag = 0;
// Byte RepeatFlag =0;
Byte AreaCodeFlag = 0;
int repeatNumbers = 0;
int NoAreaCode = 0;
DataTable dtPhoneNumber = new DataTable();
DataSet ds1 = ClsList.PhoneNumbers_GetAll(ListID);
dtPhoneNumber = ds1.Tables[0];
try
{
while (sr.EndOfStream != true)
//for (Rowcnt = 0; Rowcnt < FileValueArray.Count(); Rowcnt++)
{
Rowcnt = Rowcnt + 1;
string Row = sr.ReadLine();
//FileValueArray.ElementAt(Rowcnt);
Int64 phoneNumber;
try
{
phoneNumber = Convert.ToInt64(Row.Substring(phoneStartpos - 1, phonelen).Replace("-", "").Replace(" ", ""));
phoneNumber = Int64.Parse((Math.Pow(10 * CountryCode, (phoneNumber.ToString().Length))).ToString()) + phoneNumber;
}
catch (Exception exp)
{
badNumbers++;
// badNumberFlag = 1;
continue;
}
DataRow[] drRepeat = dtPhoneNumber.Select("PhoneNo = " + phoneNumber);
if (drRepeat.Length > 0)
{
repeatNumbers++;
// RepeatFlag = 1;
continue;
}
dtPhoneNumber.Rows.Add(phoneNumber);
dr1 = cmd.ExecuteReader();
dr1.Read();
//if (badNumberFlag == 0 || RepeatFlag == 0)
// {
string ZipCode = Row.Substring(ZipStartpos - 1, Ziplen).Replace(" ", "");
//Int64 finalzipcode = String.IsNullOrEmpty(ZipCode) ? null : Int64.Parse(ZipCode);
drStateCountry = StateCountry.Tables[0].Select("zipcode='" + ZipCode.Substring(0, 5) + "'");
if (FilterType == "TIMEZONE")
{
//int loopcount = drCollection.Length;
int loopcount = dtTemp.Rows.Count;
DataRow drLocation;
// DataRow[] AreaCod;
//AreaCod = dtTemp.Select("Areacode = '" + dtTrendWestLead.Rows[i][0].ToString() + "'");
for (int t = 0; t < loopcount; t++)
{
if (phoneNumber.ToString().Substring(1, 3) == dtTemp.Rows[t][0].ToString())
//if (dtTrendWestLead.Rows[i][15].ToString() == dtTemp[t][0].ToString())
{
DataRow[] t1;
t1 = dtTimezone.Select("TimezoneName = '" + dtTemp.Rows[t][1] + "'");
Int64 gotIt = Int64.Parse(t1[0][1].ToString());
if (drStateCountry.Length > 0)
{
drLocation = dtLocation.NewRow();
drLocation["ID"] = MaxLocationID;
drLocation["TimeZoneID"] = gotIt;
drLocation["CountryID"] = Convert.ToInt64(drStateCountry[0][1].ToString());
drLocation["StateID"] = Convert.ToInt64(drStateCountry[0][0].ToString());
drLocation["AreaCodeID"] = Convert.ToInt64(dtTemp.Rows[t][0].ToString());
drLocation["ZipCodeID"] = Convert.ToInt64(drStateCountry[0][2].ToString());
dtLocation.Rows.Add(drLocation);
}
else
{
drLocation = dtLocation.NewRow();
drLocation["ID"] = MaxLocationID;
drLocation["TimeZoneID"] = gotIt;
drLocation["CountryID"] = 0;
drLocation["StateID"] = 0;
drLocation["AreaCodeID"] = Convert.ToInt64(dtTemp.Rows[t][0].ToString());
drLocation["ZipCodeID"] = 0;
dtLocation.Rows.Add(drLocation);
}
AreaCodeFlag = 1;
break;
// varcount++;
}
}
}
if (AreaCodeFlag == 1)
{
AreaCodeFlag = 0;
dtLead.Rows.Add(LeadMaxcount, phoneNumber, LeadFormatID, Convert.ToDateTime(dr1[0].ToString()), VMuktiAPI.VMuktiInfo.CurrentPeer.ID, null, null, false, Convert.ToDateTime(dr1.GetValue(0)), VMuktiAPI.VMuktiInfo.CurrentPeer.ID, false, 0, ListID, MaxLocationID, 0, "Fresh", false);
int Colcnt = 0;
for (Colcnt = 0; Colcnt < TotalColm; Colcnt++)
{
Int64 fieldID = Convert.ToInt32(dsCustomeFieldDetails.Tables[0].Rows[Colcnt]["CustomFieldID"]); ;
int StartPositionTmp = Convert.ToInt32(dsCustomeFieldDetails.Tables[0].Rows[Colcnt]["StartPosition"]);
int LengthTmp = Convert.ToInt32(dsCustomeFieldDetails.Tables[0].Rows[Colcnt]["Length"]);
// string Row = FileValueArray.ElementAt(Rowcnt);
string Value = Row.Substring(StartPositionTmp - 1, LengthTmp);
dtLeadDetail.Rows.Add(MaxLeadDetail, LeadMaxcount, fieldID, Value);
MaxLeadDetail = MaxLeadDetail + 1;
}
MaxLocationID = MaxLocationID + 1;
LeadMaxcount = LeadMaxcount + 1;
Row = "";
}
else
{
NoAreaCode++;
}
dr1.Close();
// }
// else
// {
// badNumberFlag = 0;
// RepeatFlag = 0;
//}
}
}
catch (Exception ex)
{
VMuktiHelper.ExceptionHandler(ex, "TotalCount()", "ClsTextFileInsert.cs");
}
finally
{
sr.Close();
fs.Close();
sr = null;
fs = null;
GC.Collect(0, GCCollectionMode.Forced);
//GC.SuppressFinalize(this);
}
SqlBulkCopy sqlBulkLocation = new SqlBulkCopy(VMuktiAPI.VMuktiInfo.MainConnectionString);
//SqlBulkCopy sqlBulkLocation = new SqlBulkCopy("Data Source=192.168.1.186\\SQLExpress;Initial Catalog=VMukti;User ID=sa;PassWord=mahavir",SqlBulkCopyOptions.FireTriggers);
sqlBulkLocation.DestinationTableName = "Location";
sqlBulkLocation.BulkCopyTimeout = 1000;
sqlBulkLocation.WriteToServer(dtLocation);
sqlBulkLocation.Close();
SqlBulkCopy sqlBulkLead = new SqlBulkCopy(VMuktiAPI.VMuktiInfo.MainConnectionString);
//SqlBulkCopy sqlBulkLocation = new SqlBulkCopy("Data Source=192.168.1.186\\SQLExpress;Initial Catalog=VMukti;User ID=sa;PassWord=mahavir",SqlBulkCopyOptions.FireTriggers);
sqlBulkLead.DestinationTableName = "Leads";
sqlBulkLead.BulkCopyTimeout = 1000;
sqlBulkLead.WriteToServer(dtLead);
sqlBulkLead.Close();
SqlBulkCopy sqlBulkLeadDetail = new SqlBulkCopy(VMuktiAPI.VMuktiInfo.MainConnectionString);
//SqlBulkCopy sqlBulkLocation = new SqlBulkCopy("Data Source=192.168.1.186\\SQLExpress;Initial Catalog=VMukti;User ID=sa;PassWord=mahavir",SqlBulkCopyOptions.FireTriggers);
sqlBulkLeadDetail.DestinationTableName = "LeadDetail";
sqlBulkLeadDetail.BulkCopyTimeout = 1000;
sqlBulkLeadDetail.WriteToServer(dtLeadDetail);
sqlBulkLeadDetail.Close();
ResultArray = new int[] { Rowcnt, badNumbers, dtLead.Rows.Count, repeatNumbers, NoAreaCode };
dtLocation = null;
dtLead = null;
dtLeadDetail = null;
return ResultArray;
}
else
{
ResultArray = new int[] { 0 };
return ResultArray;
}
}