public void Save(Model.Web.AHCModel model)
{
string query = string.Format("exec wfmpcp_SaveWeeklyAHDatapoint_sp {0},{1},{2},'{3}','{4}','{5}','{6}'", model.CampaignID,
model.LobID,
model.DatapointID,
model.Data,
model.Date,
model.ModifiedBy,
model.DateModified);
//if( model.IsUpdate == false )
// query = "";//TODO: Insert Query Here
_context.ExecuteTSQL(query);
}
public void Create(long campaignID, string siteIDs)
{
try
{
string deact = string.Format("UPDATE SiteCampaign SET Active=0 WHERE CampaignID={0}", campaignID);
_context.ExecuteTSQL(deact);
//create new
if (!string.IsNullOrEmpty(siteIDs))
{
foreach (var siteid in siteIDs.TrimEnd(',').Split(',').ToArray())
{
SiteCampaign siteCampaign = new SiteCampaign()
{
SiteID = Convert.ToInt64(siteid),
CampaignID = campaignID,
Active = true
};
_context.Add <SiteCampaign>(siteCampaign);
_context.SaveChanges();
}
}
}
catch (Exception ex)
{
throw new ArgumentException("Unexpected error encountered. Please contact your system administrator.", ex.InnerException);
}
}
public void CreateWeeklyDatapoints(long siteID, long campaignID, long lobID)
{
string query = string.Format("EXEC [dbo].[wfmpcp_CreateWeeklyDatapoints_sp] {0},{1},{2}", siteID, campaignID, lobID);
_context.ExecuteTSQL(query);
}
public void Deactivate(string employeeID)
{
_context.ExecuteTSQL(string.Format("UPDATE UserRole SET Active=0 WHERE EmployeeID='{0}'", employeeID));
}
public bool ExcelRead(string fileName, string column, int columns)
{
//check excel reader if null
if (_exr != null)
{
//dispose excel reader
_exr.Dispose();
//set excel reader to null
_exr = null;
}
//create new instance of excel reader
_exr = new ExcelReader();
//set excel filename
_exr.ExcelFilename = fileName;
//read headers
_exr.Headers = false;
_exr.MixedData = false;
//get set sheet Name to string array
//_exr = new ExcelReader();
string[] sheetNames = _exr.GetExcelSheetNames();
StringBuilder sb = new StringBuilder();
foreach (string sheet in sheetNames)
{
if (sheet.Length < 50)
{
//replace invalid character from sheetname
newSheet = sheet.Replace("$", "")
.Replace("'", "");
#region PCP
//check if data table is null
if (_xlsTable == null)
{
//set new instance for datatable
_xlsTable = new DataTable();
}
//set excel reader connection open
_exr.KeepConnectionOpen = true;
//set excel sheet Name to read
_exr.SheetName = newSheet.ToString();
//set excel sheet range
//_exr.SheetRange = Settings.Column + ":" + Settings.Row;
_exr.SheetRange = string.Format("{0}:{1}", ConfigurationManager.AppSettings["WFMStartColumn"]
, ConfigurationManager.AppSettings["WFMEndColumn"]);
var lastColumn = new String(ConfigurationManager.AppSettings["WFMEndColumn"].Where(Char.IsLetter).ToArray());
var colIndex = this.ExcelColumnNameToNumber(lastColumn) - 3;
//get record from excel sheet
_xlsTable = _exr.GetTable();
int count = _xlsTable.Rows.Count;
DataRow dateDataRow = _xlsTable.Rows[0];
List <DateTime> dates = new List <DateTime>();
#region setup SiteID,CampaignID,LoBID
string[] splittedSheet = newSheet.Split('>');
long siteID = long.Parse(splittedSheet[0]);
long campaignID = long.Parse(splittedSheet[1]);
long lobID = long.Parse(splittedSheet[2]);
#endregion setup SiteID,CampaignID,LoBID
#region Truncate table first
string qryDelete = string.Format("DELETE FROM StagingWeeklyAHDatapoint WHERE SiteID={0} AND CampaignID={1} AND LoBID={2}", siteID, campaignID, lobID);
_context.ExecuteTSQL(qryDelete);
#endregion Truncate table first
#region Setup Excel data
for (int i = 1; i < count; i++)
{
DataRow row = _xlsTable.Rows[i];
#region Loop Column
for (int j = 0; j <= colIndex; j++)
{
string dtString = this.FormattedDateString(dateDataRow[j].ToString());
DateTime date = DateTime.Parse(dtString);
if (date.DayOfWeek == DayOfWeek.Monday)
{
//insert data
string data = row[j].ToString().Trim();
if (string.IsNullOrEmpty(data))
{
data = "0";
}
data = data.Replace("%", "").Replace(" ", "").Replace(",", "");
int week = 0;
week = this.GetWeekOfYear(date);
dates.Add(date);
var model = new Model.WeeklyDatapointViewModel()
{
SiteID = siteID,
CampaignID = campaignID,
LoBID = lobID,
DatapointID = i,
Week = week,
Data = data,
Date = date,
CreatedBy = "WFM PCP Uploader",
DateCreated = DateTime.Now
};
_stagingWeeklyDatapointService.Save(model);
}
}
#endregion Loop Column
}
#endregion Setup Excel data
#region Save to WeeklyAHDatapoint
foreach (var dt in dates.OrderBy(x => x.Date).Distinct())
{
#region Create Dataset
//DataTable datatable = this.WeeklyDtpDatatable;
DataTable datatable = new DataTable();
//dataset.Tables.Add( "AHCData" );
datatable.Columns.Add("DatapointID", typeof(long));
datatable.Columns.Add("SiteID", typeof(long));
datatable.Columns.Add("CampaignID", typeof(long));
datatable.Columns.Add("LoBID", typeof(long));
datatable.Columns.Add("Date", typeof(DateTime));
//datatable.Columns.Add( "Date", typeof( string ) );
datatable.Columns.Add("DataValue", typeof(string));
datatable.Columns.Add("UserName", typeof(string));
datatable.Columns.Add("DateModified", typeof(DateTime));
DataRow datarow;
#endregion Create Dataset
string b = dt.ToString("yyyy-MM-dd");
//get staging data
var stagingDatapoints = _context.AsQueryable <StagingWeeklyDatapoint>()
.Where(x => x.Date == dt && x.SiteID == siteID && x.CampaignID == campaignID && x.LoBID == lobID)
.ToList();
foreach (var sDatapoint in stagingDatapoints)
{
string value = sDatapoint.Data.Replace("%", "")
.Replace("#VALUE!", "")
.Replace("-", "").Trim();
datarow = datatable.NewRow();
datarow["DatapointID"] = sDatapoint.DatapointID;
datarow["SiteID"] = sDatapoint.SiteID;
datarow["CampaignID"] = sDatapoint.CampaignID;
datarow["LoBID"] = sDatapoint.LoBID;
datarow["Date"] = dt;
datarow["DataValue"] = value;
datarow["UserName"] = sDatapoint.CreatedBy;
datarow["DateModified"] = sDatapoint.DateCreated;
datatable.Rows.Add(datarow);
var bb = datatable;
}
_achService.Save(datatable);
}
#endregion Save to WeeklyAHDatapoint
isSuccess = true;
#endregion PCP
#region Log
this.WriteLogs(ref sb, fileName, siteID.ToString(), campaignID.ToString(), lobID.ToString());
//sb.AppendLine( WriteLogs( fileName, siteID.ToString(), campaignID.ToString(), lobID.ToString() ) );
//sb.AppendLine( );
#endregion Log
}
}
if (sb != null)
{
string filename = string.Format("Success_{0}.txt", (DateTime.Now).ToString("MM.dd.yyyy.HH.mm"));
string filelocation = ""; // AppSettings.Setting<string>("WFMUpload.Logs") + filename;
File.WriteAllText(filelocation, sb.ToString());
}
return(true);
}
public void Deactivate(long lobID)
{
string qry = string.Format("UPDATE SiteCampaignLoB SET Active=0 WHERE LoBID={0}", lobID);
_context.ExecuteTSQL(qry);
}