private static void initializeItemParameters(SqlInt32 style_color_id, SalesAndReceiptPlan salesAndReceiptPlan)
{
string query = "SELECT Weeks_Of_Cover, Pres_Min FROM AP_Style_Color WHERE Id = @style_color_id";
salesAndReceiptPlan.WOC = 0;
salesAndReceiptPlan.PresMin = 1;
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
using (SqlCommand cmd = new SqlCommand(query, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(
new SqlParameter
{
ParameterName = "@style_color_id",
SqlDbType = System.Data.SqlDbType.Int,
Direction = System.Data.ParameterDirection.Input,
Value = style_color_id,
});
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
salesAndReceiptPlan.WOC = reader.IsDBNull(0) ? (byte)0 : reader.GetByte(0);
salesAndReceiptPlan.PresMin = reader.IsDBNull(1) ? (byte)1 : reader.GetByte(1);
}
}
}
}
}
private static void initializeBohAndPlanStart(SqlInt32 style_color_id, SalesAndReceiptPlan salesAndReceiptPlan)
{
// salesAndReceiptPlan.PlanStartWeek = -1;
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
using (SqlCommand cmd = new SqlCommand("dbo.Get_BOH_And_Plan_Start", connection))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddRange(new SqlParameter[]
{
new SqlParameter
{
ParameterName = "@style_color_id",
SqlDbType = System.Data.SqlDbType.Int,
Direction = System.Data.ParameterDirection.Input,
Value = style_color_id
},
new SqlParameter
{
ParameterName = "@plan_start_week",
SqlDbType = System.Data.SqlDbType.Int,
Direction = System.Data.ParameterDirection.Output,
},
new SqlParameter
{
ParameterName = "@beginning_on_hand",
SqlDbType = System.Data.SqlDbType.Int,
Direction = System.Data.ParameterDirection.Output,
}
});
connection.Open();
cmd.ExecuteNonQuery();
// salesAndReceiptPlan.PlanStartWeek = (int)cmd.Parameters["@plan_start_week"].Value;
salesAndReceiptPlan.BOH = (int)cmd.Parameters["@beginning_on_hand"].Value;
if (salesAndReceiptPlan.BOH == 0 && salesAndReceiptPlan.Receipts != null)
{
salesAndReceiptPlan.Receipts[0].FlowType = 'I';
}
}
}
// salesAndReceiptPlan.InitializePlan();
}
private static void initializeNodeSalesPlans(SqlInt32 style_color_id, SalesAndReceiptPlan salesAndReceiptPlan)
{
int numWeeks = 0;
salesAndReceiptPlan.MdStartWeekIndex = -1;
List <RetailWeek> weeksList = new List <RetailWeek>();
List <NodeSalesPlan> pList = new List <NodeSalesPlan>();
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
using (SqlCommand cmd = new SqlCommand("[dbo].[Get_Node_Sales_Plan]", connection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(
new SqlParameter
{
ParameterName = "@style_color_id",
SqlDbType = System.Data.SqlDbType.Int,
Direction = System.Data.ParameterDirection.Input,
Value = style_color_id,
});
connection.Open();
NodeSalesPlan previousNode = null;
List <NodeWeekSalesPlan> planList = null;
bool firstTime = true;
decimal cumulativeSalesPlan = 0;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int gradeId = reader.GetInt32(0);
int climateId = reader.GetInt32(1);
if (previousNode == null || previousNode.GradeId != gradeId || previousNode.ClimateId != climateId)
{
if (previousNode != null)
{
int planLength = planList.Count;
if (planLength == numWeeks)
{
previousNode.Plans = planList.ToArray();
}
else
{
previousNode.Plans = new NodeWeekSalesPlan[numWeeks];
planList.ToArray().CopyTo(previousNode.Plans, numWeeks - planLength);
}
pList.Add(previousNode);
firstTime = false;
cumulativeSalesPlan = 0;
}
planList = new List <NodeWeekSalesPlan>();
previousNode = new NodeSalesPlan()
{
GradeId = gradeId, ClimateId = climateId, Plans = null
};
}
int week = reader.GetInt32(2);
bool isFullPriceWeek = reader.GetBoolean(3);
if (firstTime)
{
if (numWeeks == 0)
{
salesAndReceiptPlan.FirstSalesWeek = week;
}
if (salesAndReceiptPlan.MdStartWeekIndex < 0 && !isFullPriceWeek)
{
salesAndReceiptPlan.MdStartWeekIndex = numWeeks;
}
numWeeks++;
weeksList.Add(new RetailWeek {
Week = week, IsFullPriceWeek = isFullPriceWeek
});
}
short storeCount = 0;
if (!reader.IsDBNull(4))
{
storeCount = reader.GetInt16(4);
}
decimal salesPlanU = 0;
if (!reader.IsDBNull(5))
{
cumulativeSalesPlan += salesPlanU = reader.GetDecimal(5);
if (isFullPriceWeek)
{
salesAndReceiptPlan.TotalFpSalesPlanU += salesPlanU;
}
else
{
salesAndReceiptPlan.TotalMdSalesPlanU += salesPlanU;
}
}
planList.Add(new NodeWeekSalesPlan {
StoreCount = storeCount, IsFullPriceWeek = isFullPriceWeek, SalesPlanU = salesPlanU, CumulativeSalesPlanU = cumulativeSalesPlan
});
}
}
if (previousNode != null)
{
int planLength = planList.Count;
if (planLength == numWeeks)
{
previousNode.Plans = planList.ToArray();
}
else
{
previousNode.Plans = new NodeWeekSalesPlan[numWeeks];
planList.ToArray().CopyTo(previousNode.Plans, numWeeks - planLength);
}
pList.Add(previousNode);
}
}
}
if (numWeeks > 0)
{
salesAndReceiptPlan.Weeks = weeksList.ToArray();
salesAndReceiptPlan.NodeSalesPlans = pList.ToArray();
Dictionary <int, int> weekIndex = new Dictionary <int, int>();
for (int i = 0; i < numWeeks; i++)
{
weekIndex.Add(salesAndReceiptPlan.Weeks[i].Week, i);
}
salesAndReceiptPlan.WeekIndex = weekIndex;
}
else
{
salesAndReceiptPlan.NodeSalesPlans = null;
}
salesAndReceiptPlan.NumWeeks = numWeeks;
if (salesAndReceiptPlan.MdStartWeekIndex < 0)
{
salesAndReceiptPlan.MdStartWeekIndex = numWeeks;
}
}
private static void writeOutput(SqlInt32 style_color_id, SalesAndReceiptPlan salesAndReceiptPlan, SqlBoolean verbose, bool debug = false)
{
string[] storedProcs = verbose.IsTrue ?
new string[] { "Save_Consolidated_Receipts", "Save_Consolidated_Node_Receipts", "Save_Node_Receipt_Needs_Debug" } :
new string[] { "Save_Consolidated_Receipts", "Save_Consolidated_Node_Receipts" };
int numStoredProcs = verbose ? 3 : 2;
SqlPipe sqlP = SqlContext.Pipe;
for (int i = 0; i < numStoredProcs; i++)
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
using (SqlCommand cmd = new SqlCommand(storedProcs[i], connection))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
if (debug)
{
sqlP.Send(storedProcs[i] + " started");
}
DataTable dt = null;
switch (i)
{
case 0:
if (debug)
{
sqlP.Send("Style_Color_Id, Receipt Type, Week");
foreach (Receipt receipt in salesAndReceiptPlan.Receipts)
{
sqlP.Send(style_color_id.ToString() + "," + receipt.ReceiptType + "," + receipt.Week);
}
}
dt = salesAndReceiptPlan.GetConsolidatedReceiptsDataTable();
break;
case 1:
dt = salesAndReceiptPlan.GetConsolidatedNodeReceiptsDataTable();
break;
case 2:
dt = salesAndReceiptPlan.GetReceiptNeedsDebugDataTable();
break;
}
cmd.Parameters.AddRange(new SqlParameter[]
{
new SqlParameter
{
ParameterName = "@style_color_id",
SqlDbType = System.Data.SqlDbType.Int,
Direction = System.Data.ParameterDirection.Input,
Value = style_color_id
},
new SqlParameter
{
ParameterName = "@data",
SqlDbType = System.Data.SqlDbType.Structured,
TypeName = dt.TableName,
Direction = System.Data.ParameterDirection.Input,
Value = dt
}
});
connection.Open();
cmd.ExecuteNonQuery();
if (debug)
{
sqlP.Send(storedProcs[i] + " completed");
}
}
}
}
if (debug)
{
sqlP.Send(style_color_id.ToString() + " processed.");
}
}
private static void initializeReceipts(SqlInt32 style_color_id, SalesAndReceiptPlan salesAndReceiptPlan, bool debug = false)
{
int earliestReceiptWeek = -1;
Dictionary <int, int> receiptPlan = new Dictionary <int, int>();
Dictionary <int, int> purchaseOrders = new Dictionary <int, int>();
Dictionary <int, int> overridesPlan = new Dictionary <int, int>();
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
using (SqlCommand cmd = new SqlCommand("dbo.Get_Receipts", connection))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(
new SqlParameter
{
ParameterName = "@style_color_id",
SqlDbType = System.Data.SqlDbType.Int,
Direction = System.Data.ParameterDirection.Input,
Value = style_color_id
});
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int week = reader.GetInt32(0);
string receipt_type = reader.GetString(1);
int units = reader.GetInt32(2);
switch (receipt_type)
{
case "BuyTool":
receiptPlan.Add(week, units);
break;
case "PO":
purchaseOrders.Add(week, units);
break;
case "Override":
overridesPlan.Add(week, units);
break;
default:
throw new Exception("Unrecognized receipt_type from dbo.Get_Receipts - " + receipt_type);
}
if (week < earliestReceiptWeek || earliestReceiptWeek < 0)
{
earliestReceiptWeek = week;
}
}
}
}
}
SqlPipe sqlP = SqlContext.Pipe;
if (debug)
{
sqlP.Send("Step 0");
}
salesAndReceiptPlan.FirstReceiptWeek = earliestReceiptWeek;
int totalReceiptU = 0;
int validReceiptsCount = 0;
Dictionary <int, Receipt> receiptDict = new Dictionary <int, Receipt>();
foreach (KeyValuePair <int, int> kvp in purchaseOrders)
{
if (kvp.Value > 0)
{
receiptDict.Add(kvp.Key, new Receipt {
Week = kvp.Key, Qty = kvp.Value, ReceiptType = 'P', FlowType = 'F'
});
totalReceiptU += kvp.Value;
validReceiptsCount++;
}
}
if (debug)
{
sqlP.Send("Step 1");
}
foreach (KeyValuePair <int, int> kvp in overridesPlan)
{
if (!receiptDict.ContainsKey(kvp.Key))
{
receiptDict.Add(kvp.Key, new Receipt {
Week = kvp.Key, Qty = kvp.Value, ReceiptType = 'O', FlowType = 'F'
});
if (kvp.Value > 0)
{
totalReceiptU += kvp.Value;
validReceiptsCount++;
}
}
}
if (debug)
{
sqlP.Send("Step 2");
}
foreach (KeyValuePair <int, int> kvp in receiptPlan)
{
if (!receiptDict.ContainsKey(kvp.Key) && kvp.Value > 0)
{
receiptDict.Add(kvp.Key, new Receipt {
Week = kvp.Key, Qty = kvp.Value, ReceiptType = 'T', FlowType = 'F'
});
totalReceiptU += kvp.Value;
validReceiptsCount++;
}
}
if (debug)
{
sqlP.Send("totalReceiptU " + totalReceiptU);
}
salesAndReceiptPlan.TotalReceiptU = totalReceiptU;
Receipt[] receipts = null;
if (validReceiptsCount > 0)
{
if (debug)
{
sqlP.Send("Step 3 - receipt Count = " + validReceiptsCount + ", receiptDict.count = " + receiptDict.Count);
}
receipts = new Receipt[validReceiptsCount];
int i = 0;
foreach (Receipt r in receiptDict.Values)
{
if (r.Qty > 0)
{
receipts[i++] = r;
}
}
Array.Sort <Receipt>(receipts, (x, y) => x.Week.CompareTo(y.Week));
if (debug)
{
sqlP.Send("Step 4");
}
if (debug)
{
sqlP.Send("Receipt Type, Week");
foreach (Receipt receipt in receipts)
{
sqlP.Send(receipt.ReceiptType.ToString() + "," + receipt.Qty);
}
}
}
salesAndReceiptPlan.Receipts = receipts;
if (debug)
{
sqlP.Send("Completed GetReceipts");
}
}
public static void Calc_Node_Receipt_Plans(SqlInt32 style_color_id, SqlBoolean verbose)
{
bool debug = false;
SalesAndReceiptPlan salesAndReceiptPlan = new SalesAndReceiptPlan {
MdStartWeekIndex = -1
};
bool success = false;
initializeReceipts(style_color_id, salesAndReceiptPlan);
SqlPipe sqlP = SqlContext.Pipe;
if (debug)
{
sqlP.Send("Step = 1");
}
if (salesAndReceiptPlan.FirstReceiptWeek > 0)
{
initializeBohAndPlanStart(style_color_id, salesAndReceiptPlan);
if (debug)
{
sqlP.Send("Step = 2");
}
initializeNodeSalesPlans(style_color_id, salesAndReceiptPlan);
if (debug)
{
sqlP.Send("Step = 3");
}
if (salesAndReceiptPlan.NodeSalesPlans != null)
{
if (debug)
{
sqlP.Send("Step = 4");
}
initializeItemParameters(style_color_id, salesAndReceiptPlan);
if (debug)
{
sqlP.Send("Step = 5");
}
success = salesAndReceiptPlan.CalcNodeReceipts();
if (success)
{
if (debug)
{
sqlP.Send("Step = 6");
}
writeOutput(style_color_id, salesAndReceiptPlan, verbose);
}
}
}
/*
* SqlPipe sqlP = SqlContext.Pipe;
* sqlP.Send("numWeeks = " + numWeeks);
* foreach (int week in weeks)
* {
* sqlP.Send("Week = " + week);
* }
* sqlP.Send("Grade ID, Climate ID, Store Count, Is Full Price Week, Sales Plan, Cum Sales Plan, Receipt Needs, Receipt Plan");
*
* foreach (NodePlan nodePlan in plans)
* {
* foreach (Plan plan in nodePlan.Plans)
* {
* sqlP.Send(nodePlan.GradeId + "," + nodePlan.ClimateId + "," + plan.StoreCount + "," + plan.IsFullPriceWeek
+ "," + plan.SalesPlan + "," + plan.CumulativeSalesPlan + "," + plan.ReceiptNeed + "," + plan.ReceiptPlan);
+ }
+ }
*/
// sqlP.Send("Echo: GradeId =" + pl.GradeId + ", ClimateID = " + pl.ClimateId);
}
