private void Btn_Test_Click(object sender, EventArgs e)
{
string start_Date = Start_Date.Value.ToString("yyyy/MM/dd");
DB_SQL_Connection newConn;
//Requirements_Functions reqFuntion;
DataSet ds3;
DataRow dRow3;
int MaxRows3;
int inc3 = 0;
// Open Staff Groups as a dataset
newConn = new DB_SQL_Connection();
newConn.SqlQuery = "SELECT * FROM [Allocation] WHERE [aDate] = '" + start_Date + "'";
ds3 = newConn.GetConnection;
MaxRows3 = ds3.Tables[0].Rows.Count;
inc3 = 0;
dRow3 = ds3.Tables[0].Rows[inc3];
//string output = JsonConvert.SerializeObject(ds3);
Console.WriteLine("Starting");
JsonSerializer serializer = new JsonSerializer();
serializer.NullValueHandling = NullValueHandling.Ignore;
using (StreamWriter sw = new StreamWriter(@"C:\Users\Andy\json.txt"))
using (JsonWriter writer = new JsonTextWriter(sw))
{
serializer.Serialize(writer, ds3);
}
Console.WriteLine("Finished");
Console.WriteLine("Press Enter when ready to unpack:");
Console.ReadLine();
DataSet dataSet = JsonConvert.DeserializeObject <DataSet>(File.ReadAllText(@"C:\Users\Andy\json.txt"));
DataTable dataTable = dataSet.Tables["Table1"];
//Console.WriteLine(dataTable.Rows.Count);
int MaxRows4 = dataSet.Tables[0].Rows.Count;
int inc4 = 0;
DataRow dRow4;
while (inc4 < MaxRows4)
{
dRow4 = dataSet.Tables[0].Rows[inc4];
Console.WriteLine(dRow4["Id"] + " - " + dRow4["Group"]);
inc4++;
}
}
public void Requirement_Run(string startDate)
{
string wcDate = startDate;
try
{
// Clear Staff Groups table
DB_SQL_Connection newConnection = new DB_SQL_Connection();
newConnection.ExecuteQuery("ZeroStaffNeed");
Console.WriteLine("Week Commencing Date: " + wcDate);
DateTime start_Date = DateTime.Parse(wcDate);
DateTime endDate = start_Date.AddDays(7);
for (DateTime nDate = start_Date; nDate.Date <= endDate.Date; nDate = nDate.AddDays(1))
{ // run through each date (option to multi-thread)
Console.WriteLine("Current Date: " + nDate.ToString("yyyy/MM/dd"));
//double[] timeSlots = { 7, 7.5, 8, 8.5, 9, 9.5, 10, 10.5, 11, 11.5, 12, 12.5, 13, 13.5,
// 14, 14.5, 15, 15.5, 16, 16.5, 17, 17.5, 18, 18.5, 19, 19.5, 20, 20.5, 21, 21.5, 22 };
double[] timeSlots = { 8, 8.5, 9, 9.5, 10, 10.5, 11, 11.5, 12, 12.5, 13, 13.5,
14, 14.5, 15, 15.5, 16, 16.5, 17, 17.5, 18, 18.5, 19, 19.5 };
Parallel.ForEach(timeSlots, thisSlot =>
//foreach (double thisSlot in timeSlots)
{ // run through each slot (option to multi-thread)
DB_SQL_Connection connectionObject;
//Requirements_Functions reqFuntion;
DataSet dataSet0;
DataRow dataRow0;
int maxRows0;
int increment0 = 0;
DataSet dataSet1;
DataRow dataRow1;
int maxRows1;
int increment1 = 0;
int increment2 = 0;
string forcastLine = "ADL";
int forcastNeed;
string groupName;
int minForGroup;
int maxForGroup;
int allocated;
int groupTotal;
int initialAllocation = 0;
int actualAllocation = 0;
int sumLine = 0;
int groupSum = 0;
int allocate = 0;
double nSlot = thisSlot;
// add DB Connection Class : Once Only
connectionObject = new DB_SQL_Connection();
Console.WriteLine("Connection Open ! ");
// Open Forecast need table to cycle through
Console.WriteLine("Open Forecast Table for " + nDate.ToString("yyyy/MM/dd") + " : " + nSlot);
dataSet0 = connectionObject.ReturnQuery("GetForecast", "nSlot", nSlot.ToString());
maxRows0 = dataSet0.Tables[0].Rows.Count;
// Open Staff Groups as a dataset
Console.WriteLine("Open Staff Groups Table for " + nDate.ToString("yyyy/MM/dd") + " : " + nSlot);
dataSet1 = connectionObject.ReturnQuery("GetStaffGroups", "nSlot", nSlot.ToString());
maxRows1 = dataSet1.Tables[0].Rows.Count;
// Fill in Total Need for ratio calcs on each staffGroup
increment1 = 0;
while (increment1 < maxRows1)
{
dataRow1 = dataSet1.Tables[0].Rows[increment1];
groupName = dataRow1["Group"].ToString().Trim();
groupSum = 0;
increment0 = 0;
while (increment0 < maxRows0)
{
dataRow0 = dataSet0.Tables[0].Rows[increment0];
forcastLine = dataRow0["Line"].ToString().Trim();
forcastNeed = int.Parse(dataRow0["Need"].ToString().Trim());
if (groupName.Contains(forcastLine))
{
groupSum = groupSum + forcastNeed;
}
increment0++;
}
dataRow1["Total"] = groupSum;
dataRow1["Allocated"] = (int)dataRow1["Max"] * 0.5;
connectionObject.UpdateDatabase(dataSet1);
increment1++;
}
//reqFuntion = new Requirements_Functions();
//reqFuntion.do_calcs(dataSet0, dataSet1);
// Start checking allocation of forecast to staff groups
increment0 = 0;
while (increment0 < maxRows0)
{
dataRow0 = dataSet0.Tables[0].Rows[increment0];
forcastLine = dataRow0["Line"].ToString().Trim();
forcastNeed = int.Parse(dataRow0["Need"].ToString().Trim());
increment1 = 0;
sumLine = 0;
while (increment1 < maxRows1)
{
dataRow1 = dataSet1.Tables[0].Rows[increment1];
groupName = dataRow1["Group"].ToString().Trim();
sumLine = sumLine + int.Parse(dataRow1[forcastLine].ToString().Trim());
increment1++;
}
// Divide forecast Need by Count of groups containing Forcast line.
// forcast need must deduct proportion already allocated from other staff groups.
initialAllocation = (forcastNeed - sumLine);
increment1 = 0;
while (increment1 < maxRows1)
{
dataRow1 = dataSet1.Tables[0].Rows[increment1];
groupName = dataRow1["Group"].ToString().Trim();
minForGroup = int.Parse(dataRow1["Min"].ToString().Trim());
maxForGroup = int.Parse(dataRow1["Max"].ToString().Trim());
allocated = int.Parse(dataRow1["Allocated"].ToString().Trim());
groupTotal = int.Parse(dataRow1["Total"].ToString().Trim());
//if (groupName.Contains(forcastLine) && int.Parse(dataRow1[forcastLine].ToString()) == 0)
if (groupName.Contains(forcastLine))
{
// Cycle through and check if amount is within min/max
// Allocate number
dataRow1 = dataSet1.Tables[0].Rows[increment1];
float ratioMax = maxForGroup * ((float)forcastNeed / (float)groupTotal);
if ((int)ratioMax < initialAllocation + int.Parse(dataRow1[forcastLine].ToString().Trim()))
{
actualAllocation = (int)ratioMax;
}
else
{
actualAllocation = initialAllocation + int.Parse(dataRow1[forcastLine].ToString().Trim());
}
float allocationAmount = actualAllocation / ((float)forcastNeed / (float)groupTotal);
if ((int)allocationAmount > allocated)
{
allocate = (int)allocationAmount;
}
else
{
allocate = allocated;
}
dataRow1["allocated"] = allocate;
initialAllocation = initialAllocation - actualAllocation;
}
//connectionObject.UpdateDatabase(dataSet1);
increment1++;
}
connectionObject.UpdateDatabase(dataSet1);
// Calculate other fields based on Allocated
increment2 = 0;
while (increment2 < maxRows0)
{
dataRow0 = dataSet0.Tables[0].Rows[increment2];
forcastLine = dataRow0["Line"].ToString().Trim();
forcastNeed = int.Parse(dataRow0["Need"].ToString().Trim());
int increment3 = 0;
while (increment3 < maxRows1)
{
dataRow1 = dataSet1.Tables[0].Rows[increment3];
groupName = dataRow1["Group"].ToString().Trim();
allocated = int.Parse(dataRow1["Allocated"].ToString().Trim());
groupTotal = int.Parse(dataRow1["Total"].ToString().Trim());
if (groupName.Contains(forcastLine))
{
// Cycle through and check if amount is within min/max
// Allocate number
if ((float)forcastNeed == 0)
{
forcastNeed = 1;
}
if ((float)groupTotal == 0)
{
groupTotal = 1;
}
dataRow1[forcastLine] = allocated * ((float)forcastNeed / (float)groupTotal);
}
increment3++;
}
increment2++;
}
connectionObject.UpdateDatabase(dataSet1);
// Loop to next Need Line
increment0++;
}
Console.WriteLine("Finished updates for " + nDate.ToString("yyyy/MM/dd") + " : " + nSlot);
});
//}
}
// Output to Screen
/*connectionObject.SqlQuery = "Select * from [Staff_Groups]";
* DataSet ds0 = connectionObject.GetConnection;
* int MaxRows0 = ds0.Tables[0].Rows.Count;
* int inc0 = 0;
* Console.WriteLine("Output Table");
* Console.WriteLine("Staff Group".PadRight(22) + "Alloc" + "ADL".PadLeft(7)
+ "PYE".PadLeft(7) + "SAH".PadLeft(7) + "TCH".PadLeft(7));
+ while (inc0 < MaxRows0)
+ {
+ DataRow dRow0 = ds0.Tables[0].Rows[inc0];
+ Console.WriteLine(dRow0.ItemArray.GetValue(1).ToString().PadRight(18)
+ " - " + dRow0.ItemArray.GetValue(4).ToString().PadLeft(4)
+ " - " + dRow0.ItemArray.GetValue(5).ToString().PadLeft(4)
+ " - " + dRow0.ItemArray.GetValue(6).ToString().PadLeft(4)
+ " - " + dRow0.ItemArray.GetValue(7).ToString().PadLeft(4)
+ " - " + dRow0.ItemArray.GetValue(8).ToString().PadLeft(4)
+ " - " + dRow0.ItemArray.GetValue(9).ToString().PadLeft(4));
+ inc0++;
+ }*/
// Output to table
Requirements_Functions outputObject = new Requirements_Functions();
outputObject.Output_Function(wcDate);
// Output to json text file
}
catch (Exception error)
{
Console.WriteLine(error.Message);
String exDetail = String.Format("Exception message: {0}{1}Exception Source: {2}{1}Exception StackTrace: {3}{1}",
error.Message, Environment.NewLine, error.Source, error.StackTrace);
Console.WriteLine(exDetail);
}
// Tidy up
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
public void ScheduleRun()
{
// Required once for connection
DB_SQL_Connection objConnect;
// Required per Recordset queries
DataSet ds0;
DataRow dRow0;
int MaxRows0;
int inc0 = 0;
DataSet ds1;
DataRow dRow1;
int MaxRows1;
int inc1 = 0;
DataSet ds2;
DataRow dRow2;
int MaxRows2;
int inc2 = 0;
// Misc Variables
string priority_LOB = null;
string core_LOB = null;
try
{
Console.WriteLine("Connection Open ! ");
// Once Only
objConnect = new DB_SQL_Connection();
// Start of Code Run
ds0 = objConnect.ReturnQuery("GetTest");
MaxRows0 = ds0.Tables[0].Rows.Count;
Console.WriteLine("Input Table");
while (inc0 < MaxRows0)
{
dRow1 = ds0.Tables[0].Rows[inc0];
Console.WriteLine(dRow1.ItemArray.GetValue(1).ToString()
+ " - " + dRow1.ItemArray.GetValue(2).ToString()
+ " - " + dRow1.ItemArray.GetValue(3).ToString()
+ " - " + dRow1.ItemArray.GetValue(4).ToString()
+ " - " + dRow1.ItemArray.GetValue(5).ToString()
+ " - " + dRow1.ItemArray.GetValue(6).ToString());
inc0++;
}
Console.WriteLine("Open Priority Table");
inc1 = 0;
ds1 = objConnect.ReturnQuery("GetLOBPriority");
MaxRows1 = ds1.Tables[0].Rows.Count;
while (inc1 < MaxRows1)
{
dRow1 = ds1.Tables[0].Rows[inc1];
priority_LOB = dRow1.ItemArray.GetValue(2).ToString().Trim();
// Open next table
inc2 = 0;
ds2 = objConnect.ReturnQuery("GetGroups");
MaxRows2 = ds2.Tables[0].Rows.Count;
while (inc2 < MaxRows2)
{
Console.WriteLine("For " + priority_LOB + " cycle through " + inc2 + " of " + MaxRows2);
dRow2 = ds2.Tables[0].Rows[inc2];
core_LOB = dRow2.ItemArray.GetValue(4).ToString().Trim();
if (core_LOB == priority_LOB)
{
// How to Update a Row
DataRow row = ds2.Tables[0].Rows[inc2];
row["Sim_LOB"] = priority_LOB;
}
inc2++;
}
// Update changes in Dataset to Database
objConnect.UpdateDatabase(ds2);
// move to next LOB
inc1++;
}
//run execute only query
DB_SQL_Connection newConn = new DB_SQL_Connection();
newConn.ExecuteQuery("UpdateSimToConfirm");
Console.WriteLine("Output");
ds0 = objConnect.ReturnQuery("GetTest");
MaxRows0 = ds0.Tables[0].Rows.Count;
inc0 = 0;
while (inc0 < MaxRows0)
{
dRow0 = ds0.Tables[0].Rows[inc0];
Console.WriteLine(dRow0.ItemArray.GetValue(1).ToString()
+ " - " + dRow0.ItemArray.GetValue(2).ToString()
+ " - " + dRow0.ItemArray.GetValue(3).ToString()
+ " - " + dRow0.ItemArray.GetValue(4).ToString()
+ " - " + dRow0.ItemArray.GetValue(5).ToString()
+ " - " + dRow0.ItemArray.GetValue(6).ToString());
inc0++;
}
// End of Code Run
}
catch (Exception err)
{
Console.WriteLine(err.Message);
}
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
public void Output_Function(string wcDate)
{
DateTime start_Date = DateTime.Parse(wcDate);
DateTime endDate = start_Date.AddDays(7);
for (DateTime nDate = start_Date; nDate.Date <= endDate.Date; nDate = nDate.AddDays(1))
{ // run through each date (option to multi-thread)
// Add output to Allocation Table
DB_SQL_Connection newConn = new DB_SQL_Connection();
//DB_SQL_Connection connectionObject;
DataSet ds3;
DataRow dRow3;
int MaxRows3;
int inc3 = 0;
DataSet ds4;
DataRow dRow4;
int MaxRows4;
int inc4 = 0;
string timeslot4 = "";
string allocated4 = "";
string insertString = "";
string currentGroup = "";
string cDate = "";
string xDate = "";
DB_SQL_Connection newConnection = new DB_SQL_Connection();
newConnection.ExecuteQuery("DELETE FROM [Allocation]");
newConn.SqlQuery = "Select [Group] from [Groups]";
ds3 = newConn.GetConnection;
MaxRows3 = ds3.Tables[0].Rows.Count;
inc3 = 0;
newConn.SqlQuery = "Select [Group], [rDate], [Allocated], [TimeSlot] from [Staff_Groups]";
ds4 = newConn.GetConnection;
MaxRows4 = ds4.Tables[0].Rows.Count;
inc4 = 0;
while (inc3 < MaxRows3)
{
dRow3 = ds3.Tables[0].Rows[inc3];
currentGroup = dRow3["Group"].ToString().Trim();
timeslot4 = "[Group], [aDate]";
allocated4 = "'" + currentGroup + "', '" + nDate.ToString("yyyy/MM/dd") + "'";
inc4 = 0;
while (inc4 < MaxRows4)
{
dRow4 = ds4.Tables[0].Rows[inc4];
cDate = dRow4["rDate"].ToString().Trim();
xDate = nDate.ToString();
Console.WriteLine("nDate: " + nDate);
Console.WriteLine("cDate: " + cDate);
Console.WriteLine("xDate: " + xDate);
Console.WriteLine("cGroup: " + currentGroup);
Console.WriteLine("gGroup: " + dRow4["Group"].ToString().Trim());
if (currentGroup == dRow4["Group"].ToString().Trim() && cDate == xDate)
{
timeslot4 = timeslot4 + ", [" + double.Parse(dRow4["TimeSlot"].ToString().Trim()) + "]";
allocated4 = allocated4 + ", '" + dRow4["Allocated"].ToString().Trim() + "'";
}
inc4++;
}
insertString = "INSERT INTO [Allocation] (" + timeslot4 + ")" +
" VALUES (" + allocated4 + ")";
//Console.WriteLine(insertString);
newConn.ExecuteQuery(insertString);
inc3++;
}
}
}
