//------------------------------------------------------------- // // //------------------------------------------------------------- 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); }