//-------------------------------------------------------------
//
//
//-------------------------------------------------------------
private void ImportData(SqlCommand command, List <ImportFieldRow> list)
{
int iCount = campaignFields.Count;
if (iCount <= 0)
{
CreateCampaignFieldList(command);
}
for (int i = 0; i < list.Count; i++)
{
ImportFieldRow importRow = (ImportFieldRow)list[i];
sqlCols = string.Empty;
sqlValues = string.Empty;
strPhoneNumber = string.Empty;
createImportFieldList(importRow);
if (!isPhoneNumberInSystem(command))
{
command.CommandText = "INSERT INTO [dbo].[MasterDNC](" + sqlCols + ") values(" + sqlValues + ")";
SysLog.Write("RAINYDAY", command.CommandText);
command.ExecuteNonQuery();
importStats.LeadsImported++;
}
else
{
if (ruleAllowsUpdates())
{
if (ruleUpdatesResetAllFields())
{
ClearColumnData(command, strPhoneNumber);
}
command.CommandText = string.Format("Update [dbo].[MasterDNC] set {0} where PhoneNum = '{1}'", strUpdate, strPhoneNumber);
SysLog.Write("RAINYDAY", command.CommandText);
command.ExecuteNonQuery();
importStats.LeadsUpdated++;
}
else if (ruleSavesDuplicates())
{
File.AppendAllText(sDupFilePath, sqlValues);
importStats.LeadsDuplicate++;
}
}
}
}
//-------------------------------------------------------------
//
//
//-------------------------------------------------------------
private void createImportFieldList(ImportFieldRow importRow)
{
ImportParams importParams = (ImportParams)Session["importparams"];
List <ImportField> fieldList = importRow.ImportFieldsList;
for (int j = 0; j < fieldList.Count; j++)
{
ImportField field = (ImportField)fieldList[j];
string value = "";
field.FieldValue = field.FieldValue.Replace("'", "''");
switch (field.FieldType.ToLower())
{
case "decimal":
case "money":
value = field.FieldValue.Trim() == "" ? "null" : field.FieldValue;
Double dValue = 0;
if (value != null && value != "null")
{
try
{
//-------------------------------------
// We check to see if it is a proper
// value being passed, if not we reset it.
//-------------------------------------
dValue = Convert.ToDouble(value);
}
catch
{
field.FieldValue = "null";
value = "null";
}
}
break;
case "integer":
value = field.FieldValue.Trim() == "" ? "null" : field.FieldValue;
Int16 iValue = 0;
if (value != null && value != "null")
{
try
{
//-------------------------------------
// We check to see if it is a proper
// value being passed, if not we reset it.
//-------------------------------------
iValue = Convert.ToInt16(value);
}
catch
{
field.FieldValue = "null";
value = "null";
}
}
break;
case "date":
value = field.FieldValue.Trim() == "" ? "null" : "'" + field.FieldValue + "'";
break;
case "boolean":
value = field.FieldValue.Trim() == "" ? "null" : field.FieldValue;
if (value != null && value != "null" &&
(value.ToUpper() == "TRUE" || value != "0"))
{
value = "1";
}
else
{
value = "0";
}
break;
default:
value = field.FieldValue.Trim() == "" ? "null" : "'" + field.FieldValue + "'";
break;
}
if (field.FieldName != "NeverCallFlag" || importParams.exceptionType == 1)
{
if (sqlCols == string.Empty)
{
sqlCols = field.FieldName;
sqlValues = value;
strUpdate = String.Format("{0}={1}", field.FieldName, value);
}
else
{
sqlCols += "," + field.FieldName;
sqlValues += "," + value;
strUpdate += ", " + String.Format("{0}={1}", field.FieldName, value);
}
}
if (field.FieldName == "PhoneNum")
{
strPhoneNumber = field.FieldValue;
}
}// end for loop
forceNeverCallFlag();
}
//-----------------------------------------------------------------
//
//
//-----------------------------------------------------------------
private void SetFieldValue(ImportFieldRow row, string field, string value, string datatype)
{
List <ImportField> list = row.ImportFieldsList;
switch (datatype.ToLower())
{
case "integer":
if (value.Trim() != "" && (!IsInt(value)))
{
value = value.Trim().ToLower();
if (value == "true" || value == "yes")
{
value = "1";
}
else if (value == "false" || value == "no")
{
value = "0";
}
else
{
throw new Exception("BadInputData");
}
}
break;
case "boolean":
if (value.Trim() != "")
{
value = value.Trim().ToLower();
if (value == "true" || value == "yes")
{
value = "1";
}
else if (value == "false" || value == "no")
{
value = "0";
}
else if (IsBool(value))
{
value = Convert.ToBoolean(value) ? "1" : "0";
}
else if (IsInt(value))
{
value = Convert.ToBoolean(Convert.ToInt32(value)) ? "1" : "0";
}
else
{
throw new Exception("BadInputData");
}
}
break;
case "date":
if (value.Trim() != "" && (!IsDateTime(value)))
{
throw new Exception("BadInputData");
}
break;
case "decimal":
case "money":
if (value.Trim() != "" && (!IsDouble(value)))
{
throw new Exception("BadInputData");
}
break;
}
ImportField impField = new ImportField();
impField.FieldName = field;
impField.FieldValue = value; //Server.UrlEncode(value);
impField.FieldType = datatype;
list.Add(impField);
}
//------------------------------------------------------------------
//
//
//------------------------------------------------------------------
private List <ImportFieldRow> ReadCSVData(string Filepath, bool IsHeadrer, char Delimiter)
{
List <ImportFieldRow> list = new List <ImportFieldRow>();
DataSet ds = GetCsvDataSet(Filepath, IsHeadrer);
string value = "";
// bool bCheck = false;
string strFieldName = "";
int iFieldLength = 0;
foreach (DataRow dr in ds.Tables[0].Rows)
{
try
{
importStats.TotalLeads += 1;
ImportFieldRow row = new ImportFieldRow();
for (int i = 0; i < dataMap.Count; i++)
{
value = dr[(int)dataMapIdx[i]].ToString().Trim();
string[] columnsData = dataMap[i].ToString().Split(':');
//---------------------------------------------------------
// Making sure the length does not exceed database
// column size.
//---------------------------------------------------------
strFieldName = columnsData[0];
iFieldLength = iGetFieldSize(strFieldName);
if (iFieldLength != -1 && value.Length > iFieldLength)
{
value = value.Substring(0, iFieldLength);
}
if (strFieldName.Equals("phonenum", StringComparison.InvariantCultureIgnoreCase))
{
if (string.IsNullOrEmpty(value))
{
throw new Exception("NumberEmpty");
}
// 2012-06-13 Dave Pollastrini
// Remove all non-digit characters from phone number.
value = Regex.Replace(value, @"[^\d]", "");
//------------------------------------------------------
// Added to remove dashes, etc.
//------------------------------------------------------
//value = value.Replace("-", "");
//value = value.Replace(" ", "");
//value = value.Replace(".", "");
//value = value.Replace(")", "");
//value = value.Replace("(", "");
//bCheck = CheckForSpChars(value);
//if (bCheck == true)
//{
// throw new Exception("NumberHasSpecialChars");
//}
//-----------------------------------------------------
// Ignoring the area code if necessary.
// Only 7 digit numbers are allowed.
//-----------------------------------------------------
// 2012-06-13 Dave Pollastrini
// Cannot simply "ignore" area code on allow7 and number is 10 digits. Number may
// be "do not call" in local area code. Also, the same numbers with different area
// codes would essentially be overwritten by the last similar number in the import.
// Per James, numbers in import file will always be either 7 or 10 digits, and the
// import process should support import as EITHER (mutually exclusive) 7 OR 10 digits
// per campaign.
switch (value.Length)
{
case 7:
if (!allowSevenDigitNums)
{
throw new Exception("NumberInvalidLength");
}
break;
case 10:
if (!allowTenDigitNums)
{
throw new Exception("NumberInvalidLength");
}
break;
default:
throw new Exception("NumberInvalidLength");
}
//if (allowSevenDigitNums && value.Length != 7)
//{
// // value = value.Substring(3, 7);
// throw new Exception("NumberInvalidLength");
//}
//else if (allowTenDigitNums && value.Length != 10)
//{
// throw new Exception("NumberInvalidLength");
//}
//if (!((allowSevenDigitNums && value.Length == 7) ||
// (allowTenDigitNums && value.Length == 10)))
//{
// throw new Exception("NumberInvalidLength");
//}
}
//value = Format(value);
SetFieldValue(row, columnsData[0], value, columnsData[1]);
}
list.Add(row);
}
catch (Exception ex)
{
if (ex.Message == "NumberEmpty")
{
importStats.LeadsBlankPhoneNumber += 1;
}
else if (ex.Message == "NumberHasSpecialChars")
{
importStats.LeadsSPCharPhoneNumber += 1;
}
else if (ex.Message == "BadInputData")
{
importStats.LeadsBadData += 1;
}
else if (ex.Message == "NumberInvalidLength")
{
importStats.LeadsInvalidNumberLength += 1;
}
}
}
return(list);
}
