public ResponseOpList GetOpsForStoreID(string storeID)
{
ResponseOpList theResponse = new ResponseOpList();
if (storeID == null || storeID.Equals(""))
{
theResponse.statusCode = 3;
theResponse.statusDescription = "Missing Store ID";
}
else
{
openDataConnection();
SqlCommand cmdGet = new SqlCommand("GetOpsForStore", theConnection);
cmdGet.Parameters.AddWithValue("@storeID", storeID);
cmdGet.CommandType = System.Data.CommandType.StoredProcedure;
try
{
theReader = cmdGet.ExecuteReader();
if (theReader.HasRows)
{
theResponse.ops = new List<Op>();
while (theReader.Read())
{
Op thisOp = new Op();
thisOp.storeID = (int)theReader["StoreID"];
thisOp.storeNumber = theReader["StoreNumber"].ToString();
thisOp.area = theReader["Area"].ToString();
thisOp.division = theReader["Division"].ToString();
thisOp.region = theReader["Region"].ToString();
thisOp.district = theReader["District"].ToString();
thisOp.areaName = theReader["AreaName"].ToString();
thisOp.rdOutlookName = theReader["RDOutlookName"].ToString();
thisOp.rdEmailAddress = theReader["RDEmailAddress"].ToString();
thisOp.divisionName = theReader["DivisionName"].ToString();
thisOp.dvpOutlookname = theReader["DVPOutlookName"].ToString();
thisOp.dvpEmailAddress = theReader["DVPEmailAddress"].ToString();
thisOp.regionName = theReader["RegionName"].ToString();
thisOp.rdOutlookName = theReader["RDOutlookName"].ToString();
thisOp.rdEmailAddress = theReader["RDEmailAddress"].ToString();
thisOp.divisionName = theReader["DivisionName"].ToString();
thisOp.dmOutlookName = theReader["DMOutlookName"].ToString();
thisOp.dmEmailAddress = theReader["DMEmailAddress"].ToString();
thisOp.rvpOutlookName = theReader["RVPOutlookName"].ToString();
thisOp.rvpEmailAddress = theReader["RVPEmailAddress"].ToString();
theResponse.ops.Add(thisOp);
}
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 4;
theResponse.statusDescription = "There are no ops for the store ID " + storeID;
}
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message;
}
theReader.Close();
closeDataConnection();
}
return theResponse;
}
public ResponseOpList GetAllOpsWithRange(string startingIndex, string endingIndex)
{
ResponseOpList theResponse = new ResponseOpList();
openDataConnection();
SqlCommand cmdGetAllOps = new SqlCommand("GetAllOps", theConnection);
cmdGetAllOps.CommandType = System.Data.CommandType.StoredProcedure;
theReader = cmdGetAllOps.ExecuteReader();
int numRecords = 0;
if (theReader.HasRows)
{
List<Op> listOfOps = new List<Op>();
while (theReader.Read())
{
Op thisOp = new Op();
thisOp.storeID = (int)theReader["StoreID"];
thisOp.storeNumber = theReader["StoreNumber"].ToString();
thisOp.area = theReader["Area"].ToString();
thisOp.division = theReader["Division"].ToString();
thisOp.region = theReader["Region"].ToString();
thisOp.district = theReader["District"].ToString();
thisOp.areaName = theReader["AreaName"].ToString();
thisOp.rdOutlookName = theReader["RDOutlookName"].ToString();
thisOp.rdEmailAddress = theReader["RDEmailAddress"].ToString();
thisOp.divisionName = theReader["DivisionName"].ToString();
thisOp.dvpOutlookname = theReader["DVPOutlookName"].ToString();
thisOp.dvpEmailAddress = theReader["DVPEmailAddress"].ToString();
thisOp.regionName = theReader["RegionName"].ToString();
thisOp.rdOutlookName = theReader["RDOutlookName"].ToString();
thisOp.rdEmailAddress = theReader["RDEmailAddress"].ToString();
thisOp.divisionName = theReader["DivisionName"].ToString();
thisOp.dmOutlookName = theReader["DMOutlookName"].ToString();
thisOp.dmEmailAddress = theReader["DMEmailAddress"].ToString();
thisOp.rvpEmailAddress = theReader["RVPEmailAddress"].ToString();
thisOp.rvpOutlookName = theReader["RVPOutlookName"].ToString();
listOfOps.Add(thisOp);
numRecords++;
}
theResponse.ops = new List<Op>();
int startIndex = Int32.Parse(startingIndex);
int endIndex = Int32.Parse(endingIndex);
endIndex = startIndex + endIndex;
if (startIndex <= 0)
{
startIndex = 1;
}
if (startIndex > 0 && endIndex >= startIndex)
{
if (endIndex > numRecords)
{
endIndex = numRecords;
}
for (int i = startIndex; i <= endIndex; i++)
{
theResponse.ops.Add(listOfOps[i - 1]);
}
theResponse.numberOfRecords = numRecords;
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "The starting or ending index did not fall within the data range";
}
}
else
{
theResponse.statusCode = 4;
theResponse.statusDescription = "There are no Ops defined";
}
closeDataConnection();
return theResponse;
}
public Response AddOp(Op anOp)
{
Response theResponse = new Response();
if (anOp == null)
{
theResponse.statusCode = 2;
theResponse.statusDescription = "Expected Op Model is missing";
}
else
{
if (anOp.storeID == null || anOp.storeID == 0)
{
theResponse.statusDescription = "Store ID not provided";
}
if (anOp.area == null || anOp.area.Equals(""))
{
theResponse.statusDescription = "Area not provided";
}
if (anOp.division == null || anOp.division.Equals(""))
{
theResponse.statusDescription = "Division not provided";
}
if (anOp.region == null || anOp.region.Equals(""))
{
theResponse.statusDescription = "Region not provided";
}
if (anOp.district == null || anOp.district.Equals(""))
{
theResponse.statusDescription = "District not provided";
}
if (theResponse.statusDescription.Equals(""))
{
openDataConnection();
SqlCommand cmdAddOp = new SqlCommand("AddOpToHierarchy", theConnection);
cmdAddOp.Parameters.AddWithValue("@storeID", anOp.storeID.ToString());
cmdAddOp.Parameters.AddWithValue("@division", anOp.division);
cmdAddOp.Parameters.AddWithValue("@region", anOp.region);
cmdAddOp.Parameters.AddWithValue("@area", anOp.area);
cmdAddOp.Parameters.AddWithValue("@district", anOp.district);
cmdAddOp.Parameters.AddWithValue("@divisionName", anOp.divisionName == null ? DBNull.Value.ToString() : anOp.divisionName);
cmdAddOp.Parameters.AddWithValue("@dvpOutlookName", anOp.dvpOutlookname == null ? DBNull.Value.ToString() : anOp.dvpOutlookname);
cmdAddOp.Parameters.AddWithValue("@dvpEmailAddress", anOp.dvpEmailAddress == null ? DBNull.Value.ToString() : anOp.dvpEmailAddress);
cmdAddOp.Parameters.AddWithValue("@regionName", anOp.regionName == null ? DBNull.Value.ToString() : anOp.regionName);
cmdAddOp.Parameters.AddWithValue("@rvpOutlookName", anOp.rvpOutlookName == null ? DBNull.Value.ToString() : anOp.rvpOutlookName);
cmdAddOp.Parameters.AddWithValue("@rvpEmailAddress", anOp.rvpEmailAddress == null ? DBNull.Value.ToString() : anOp.rvpEmailAddress);
cmdAddOp.Parameters.AddWithValue("@areaName", anOp.areaName == null ? DBNull.Value.ToString() : anOp.areaName);
cmdAddOp.Parameters.AddWithValue("@rdOutlookName", anOp.rdOutlookName == null ? DBNull.Value.ToString() : anOp.rdOutlookName);
cmdAddOp.Parameters.AddWithValue("@rdEmailAddress", anOp.rdEmailAddress == null ? DBNull.Value.ToString() : anOp.rdEmailAddress);
cmdAddOp.Parameters.AddWithValue("@districtName", anOp.districtName == null ? DBNull.Value.ToString() : anOp.districtName);
cmdAddOp.Parameters.AddWithValue("@dmOutlookName", anOp.dmOutlookName == null ? DBNull.Value.ToString() : anOp.dmOutlookName);
cmdAddOp.Parameters.AddWithValue("@dmEmailAddress", anOp.dmEmailAddress == null ? DBNull.Value.ToString() : anOp.dmEmailAddress);
cmdAddOp.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffected = 0;
try
{
numRowsAffected = cmdAddOp.ExecuteNonQuery();
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message;
}
if (numRowsAffected > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
closeDataConnection();
}
else
{
theResponse.statusCode = 2;
theResponse.statusDescription = "Op model missing";
}
}
return theResponse;
}
public ResponseOpList GetAllOps()
{
ResponseOpList theResponse = new ResponseOpList();
openDataConnection();
SqlCommand cmdGetAllOps = new SqlCommand("GetAllOps", theConnection);
cmdGetAllOps.CommandType = System.Data.CommandType.StoredProcedure;
theReader = cmdGetAllOps.ExecuteReader();
if (theReader.HasRows)
{
theResponse.ops = new List<Op>();
while (theReader.Read())
{
Op thisOp = new Op();
thisOp.storeID = (int)theReader["StoreID"];
thisOp.storeNumber = theReader["StoreNumber"].ToString();
thisOp.area = theReader["Area"].ToString();
thisOp.division = theReader["Division"].ToString();
thisOp.region = theReader["Region"].ToString();
thisOp.district = theReader["District"].ToString();
thisOp.areaName = theReader["AreaName"].ToString();
thisOp.rdOutlookName = theReader["RDOutlookName"].ToString();
thisOp.rdEmailAddress = theReader["RDEmailAddress"].ToString();
thisOp.divisionName = theReader["DivisionName"].ToString();
thisOp.dvpOutlookname = theReader["DVPOutlookName"].ToString();
thisOp.dvpEmailAddress = theReader["DVPEmailAddress"].ToString();
thisOp.regionName = theReader["RegionName"].ToString();
thisOp.rdOutlookName = theReader["RDOutlookName"].ToString();
thisOp.rdEmailAddress = theReader["RDEmailAddress"].ToString();
thisOp.divisionName = theReader["DivisionName"].ToString();
thisOp.dmOutlookName = theReader["DMOutlookName"].ToString();
thisOp.dmEmailAddress = theReader["DMEmailAddress"].ToString();
thisOp.rvpOutlookName = theReader["RVPOutlookName"].ToString();
thisOp.rvpEmailAddress = theReader["RVPEmailAddress"].ToString();
thisOp.districtName = theReader["DistrictName"].ToString();
theResponse.ops.Add(thisOp);
}
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 4;
theResponse.statusDescription = "There are no Ops defined";
}
closeDataConnection();
return theResponse;
}
public Response UploadOpsDotNet(string fileName, string username)
{
Response theResponse = new Response();
string currentPath = HttpContext.Current.Server.MapPath("~");
long currentTime = DateTime.Now.ToFileTimeUtc();
//string fileName = "ops_" + currentTime;
string finalPath = currentPath + "\\uploads\\" + fileName;
//FileStream fileToUpload = new FileStream(finalPath, FileMode.Create);
//MultipartParser parser = new MultipartParser(fileStream);
//if (parser.Success)
//{
// fileToUpload.Write(parser.FileContents, 0, parser.FileContents.Length);
// fileToUpload.Close();
// fileToUpload.Dispose();
//}
//else
//{
// theResponse.statusCode = 6;
// theResponse.statusDescription = "Unable to parse input data";
// return theResponse;
//}
int recordsFound = 0;
int recordsAdded = 0;
int recordsUpdated = 0;
List<string> feedback = new List<string>();
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + finalPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;Connect Timeout=12000; ReadOnly=False\"";
//string connectionString = connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + finalPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
List<Op> opsToBeUpdated = new List<Op>();
try
{
OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dtExcelRecords = new DataTable();
con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
cmd.CommandTimeout = 6000;
OleDbDataReader oleReader;
oleReader = cmd.ExecuteReader();
if (oleReader.HasRows)
{
List<Op> ops = new List<Op>();
while (oleReader.Read())
{
if (oleReader.FieldCount < 18)
{
continue;
}
if (oleReader[0].ToString().Equals("Transaction Type"))
{
continue;
}
if (oleReader[0].ToString().Equals(""))
{
break;
}
if (oleReader[0].ToString().ToUpper().Equals("ADD") || oleReader[0].ToString().ToUpper().Equals("UPDATE"))
{
recordsFound++;
string thisStoreNumber = oleReader[17].ToString();
if (!doesStoreExist(thisStoreNumber))
{
feedback.Add("The Store Number " + thisStoreNumber + " does not exist in the database");
continue;
}
int thisStoreID = getStoreIDForStoreNumber(thisStoreNumber);
Op thisOp = new Op();
thisOp.storeID = thisStoreID;
thisOp.division = oleReader[1].ToString();
thisOp.divisionName = oleReader[2].ToString();
thisOp.dvpOutlookname = oleReader[3].ToString();
thisOp.dvpEmailAddress = oleReader[4].ToString();
thisOp.region = oleReader[5].ToString();
thisOp.regionName = oleReader[6].ToString();
thisOp.rvpOutlookName = oleReader[7].ToString();
thisOp.rvpEmailAddress = oleReader[8].ToString();
thisOp.area = oleReader[9].ToString();
thisOp.areaName = oleReader[10].ToString();
thisOp.rdOutlookName = oleReader[11].ToString();
thisOp.rdEmailAddress = oleReader[12].ToString();
thisOp.district = oleReader[13].ToString();
thisOp.districtName = oleReader[14].ToString();
thisOp.dmOutlookName = oleReader[15].ToString();
thisOp.dmEmailAddress = oleReader[16].ToString();
if (oleReader[0].ToString().ToUpper().Equals("ADD") && !doesStoreExistInOps(thisOp.storeID.ToString()))
{
ops.Add(thisOp);
}
else if (oleReader[0].ToString().Trim().ToUpper().Equals("UPDATE"))
{
opsToBeUpdated.Add(thisOp);
}
}
}
for (int i = 0, l = ops.Count; i < l; i++)
{
Response opCreationResponse = AddOp(ops[i]);
if (opCreationResponse.statusCode == 0)
{
recordsAdded++;
}
}
for (int i = 0; i < opsToBeUpdated.Count; i++)
{
Response opUpdateResponse = UpdateOp(opsToBeUpdated[i]);
if (opUpdateResponse.statusCode == 0)
{
recordsUpdated++;
}
}
}
oleReader.Close();
}
catch (Exception _exception)
{
theResponse.statusCode = 6;
theResponse.statusDescription = _exception.Message;
return theResponse;
}
if (recordsFound > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "Found " + recordsFound + " Op hierarchy records in the file.<br />Added " + recordsAdded + " records to the database.<br />Updated " + recordsUpdated + " records in the database";
}
else
{
theResponse.statusCode = 2;
theResponse.statusDescription = "No records found in the excel file";
}
if (feedback.Count > 0)
{
string emailString = "<ul>";
theResponse.statusDescription += "<br /><br /><p>Feedback:</p><ul>";
for (int i = 0, l = feedback.Count; i < l; i++)
{
theResponse.statusDescription += "<li>" + feedback[i] + "</li>";
emailString += "<li>" + feedback[i] + "</li>";
}
theResponse.statusDescription += "</ul>";
emailString += "</ul>";
SendEmailForUploadErrors("Ops Hierarchy", username, emailString, "");
}
return theResponse;
}
