public void FillProductModels()
{
var count = 1;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand("sp_SearchProduct", connection)
{
CommandType = CommandType.StoredProcedure
};
adapter = new SqlDataAdapter(command);
dataSet = new DataSet();
connection.Open();
command.Parameters.AddWithValue("@Model", SelectedModel);
command.Parameters.AddWithValue("@Color", SelectedColor);
command.Parameters.AddWithValue("@SizeMemory", SelectedMemory);
command.Parameters.AddWithValue("@Provider", SelectedProvider);
command.Parameters.AddWithValue("@DateSupply", DateSupply);
command.Parameters.AddWithValue("@PartNo", PartNo);
adapter.Fill(dataSet);
foreach (DataRow dr in dataSet.Tables[0].Rows)
{
ProductModels.Add(new ProductModel
{
NumberRow = count++,
Model = dr[0].ToString(),
SizeMemory = Convert.ToInt32(dr[1]),
Color = dr[2].ToString(),
QuantityInStock = Convert.ToInt32(dr[3].ToString()),
RetailPrice = Convert.ToDouble(dr[4].ToString()),
PartNo = dr[5].ToString(),
QuantityDelivered = Convert.ToInt32(dr[6].ToString()),
Price = Convert.ToDouble(dr[7].ToString()),
Cost = Convert.ToDouble(dr[8].ToString()),
AmountOfVAT = Convert.ToDouble(dr[9].ToString()),
CostWithVAT = Convert.ToDouble(dr[10].ToString()),
DateSupply = Convert.ToDateTime(dr[11]).ToShortDateString(),
ProviderName = Convert.ToString(dr[12].ToString())
});
}
}
catch (Exception ex)
{
}
finally
{
dataSet = null;
adapter.Dispose();
connection.Close();
connection.Dispose();
}
}
private void Initialize()
{
List <string> Product_List = ProductID.Split(',').ToList();
Db2HoldReason.Conditions = string.Format("where lot_type='Production' and prodspec_id in ('{2}') and claim_time between '{0}' and '{1}'", StartTime.ToString("yyyy-MM-dd HH:mm:ss"), EndTime.ToString("yyyy-MM-dd HH:mm:ss"), string.Join("','", Product_List));
var Models = Db2HoldReason.GetEntities().EntityList;
//if (Models.Count == 0) return;
//lotCount
int lotCount = Models.Select(s => s.Lot_ID).Distinct().Count();
//所有Hold数据
var HoldModels = Models.Where(w => w.Hold_Type != "" && w.Ope_Category.Substring(w.Ope_Category.Length - 4) == "Hold");
//5码的Hold数据
var HoldModels_5 = HoldModels.Where(w => w.Hold_Reason_Code.Length == 5);
//4码的hold数据
var HoldModels_4 = HoldModels.Where(w => w.Hold_Reason_Code.Length == 4);
List <FRPD> PD_List = new List <FRPD>();
if (HoldModels_4.Count() > 0)
{
PDCatcher.Conditions = string.Format("where pd_id in ('{0}')", string.Join("','", HoldModels_4.Select(s => s.PD_ID)));
PD_List = PDCatcher.GetEntities().EntityList.ToList();
}
var Departments = DepartmentCatcher.GetEntities().EntityList;
foreach (var department in Departments)
{
//对每个部门计算该部门对应的hold list
var list5 = HoldModels_5.Where(w => w.Hold_Reason_Code.Substring(0, 1) == department.Code_ID);
var list4 = HoldModels_4.Where(w => PD_List.Where(p => p.Department == department.Code_ID).DefaultIfEmpty().Select(s => s.PD_ID).Contains(w.PD_ID));
var list = list4.Union(list5);
List <ReqRpt014DepartmentTableEntity> l = new List <ReqRpt014DepartmentTableEntity>();
if (list.Count() == 0) //如果没有元素，则不进行查找
{
DepartmentModels.Add(new ReqRpt014DepartmentTableModel(l)
{
Department = department.Description
});
continue;
}
//如果存在元素，则进行下去
var ReasonList = list.GroupBy(g => g.Hold_Reason_Code).Select(s => new { Hold_Reason_Code = s.Key, Hold_Count = s.Count() });
foreach (var item in ReasonList)
{
ReqRpt014DepartmentTableEntity tableEntity = new ReqRpt014DepartmentTableEntity()
{
HoldCode = item.Hold_Reason_Code, HoldRate = item.Hold_Count / lotCount, LotCount = list.Where(w => w.Hold_Reason_Code == item.Hold_Reason_Code).Select(s => s.Lot_ID).Distinct().Count()
};
l.Add(tableEntity);
}
DepartmentModels.Add(new ReqRpt014DepartmentTableModel(l)
{
Department = department.Description
});
}
//by product部分
foreach (string prod in Product_List)
{
var list = HoldModels.Where(s => s.ProdSpec_ID == prod);
List <ReqRpt014ProductTableEntity> l = new List <ReqRpt014ProductTableEntity>();
if (list is null) //如果没有元素，则不进行查找
{
ProductModels.Add(new ReqRpt014ProductTableModel(l)
{
ProductID = prod
});
continue;
}
//如果存在元素，则进行下去
var ReasonList = list.GroupBy(g => g.Hold_Reason_Code).Select(s => new { Hold_Reason_Code = s.Key, HoldCount = s.Count() });
foreach (var item in ReasonList)
{
ReqRpt014ProductTableEntity tableEntity = new ReqRpt014ProductTableEntity()
{
HoldCode = item.Hold_Reason_Code, HoldRate = item.HoldCount / lotCount, LotCount = list.Where(w => w.Hold_Reason_Code == item.Hold_Reason_Code).Select(s => s.Lot_ID).Distinct().Count()
};
l.Add(tableEntity);
}
ProductModels.Add(new ReqRpt014ProductTableModel(l)
{
ProductID = prod
});
}
}
