internal void LoadPartDescriptionInformation()
{
if (string.IsNullOrEmpty(this.PartNumber))
{
return;
}
IDbConnection cnn = DatabaceFactory.GetPISConnection();
IDbCommand cmd = null;
IDbDataParameter dp = null;
DbDataAdapter da = null;
cmd = DatabaceFactory.CreateCommand(this.GetPartDescriptionSQL(), DatabaseType.SQLServer, cnn);
dp = cmd.CreateParameter();
dp.ParameterName = "PN";
dp.Value = this.PartNumber;
cmd.Parameters.Add(dp);
da = DatabaceFactory.CreateAdapter(cmd, DatabaseType.SQLServer);
DataSet DsModel = new DataSet();
da.Fill(DsModel);
this.WriteToAttributePartDescription(DsModel.Tables[0]);
}
internal void LoadPartDetail()
{
//希望能增加外部傳入參數的方式來指定連線字串
IDbConnection cnn = DatabaceFactory.GetPISConnection();
StringBuilder sql = new StringBuilder();
IDbCommand cmd = DatabaceFactory.CreateCommand(sql.ToString(), DatabaseType.SQLServer, cnn);
IDbDataParameter dp = cmd.CreateParameter();
dp.ParameterName = "PN";
dp.Value = this.PartNumber;
cmd.Parameters.Add(dp);
//Get Specs and Descriptions
sql.Clear();
sql.Append(this.GetPartSpecsSQL());
sql.Append(" ; ");
sql.Append(this.GetPartDescriptionSQL());
cmd.CommandText = sql.ToString();
DbDataAdapter da = DatabaceFactory.CreateAdapter(cmd, DatabaseType.SQLServer);
DataSet dsPart = new DataSet();
da.Fill(dsPart);
//======================Product Spec==========================================
this.WriteToAttributePartSpecs(dsPart.Tables[0]);
//======================Product Description==========================================
this.WriteToAttributePartDescription(dsPart.Tables[1]);
}
/// <summary>
/// Load only model master information
/// </summary>
/// <param name="modelname"></param>
internal void LoadModelPublish()
{
if (string.IsNullOrEmpty(this.Model_Name))
{
return;
}
IDbConnection cnn = DatabaceFactory.GetPISConnection();
IDbCommand cmd = null;
IDbDataParameter dp = null;
DbDataAdapter da = null;
cmd = DatabaceFactory.CreateCommand(this.GetPublishSQL(), DatabaseType.SQLServer, cnn);
dp = cmd.CreateParameter();
dp.ParameterName = "model_name";
dp.Value = this.Model_Name;
cmd.Parameters.Add(dp);
da = DatabaceFactory.CreateAdapter(cmd, DatabaseType.SQLServer);
DataSet DsModel = new DataSet();
da.Fill(DsModel);
this.WriteToAttributePublish(DsModel.Tables[0]);
}
/// <summary>
///
/// </summary>
/// <param name="category_id"></param>
/// <param name="model_id"></param>
/// <returns></returns>
internal string GetModelNameByCategory_ModelID(string category_id, string model_id)
{
if (string.IsNullOrEmpty(model_id))
{
return("");
}
StringBuilder sql = new StringBuilder();
sql.Append(" Select m.model_name,m.model_id ");
sql.Append(" From Category_Model c inner join Model m on c.model_name=m.model_name ");
sql.Append(" Where c.category_id=@category_id ");
sql.Append(" And m.model_id=@model_id ");
IDbConnection cnn = DatabaceFactory.GetPISConnection();
IDbCommand cmd = null; IDbDataParameter dp = null; DbDataAdapter da = null;
cmd = DatabaceFactory.CreateCommand(sql.ToString(), DatabaseType.SQLServer, cnn);
dp = cmd.CreateParameter();
dp.ParameterName = "category_id";
dp.Value = category_id;
cmd.Parameters.Add(dp);
dp = cmd.CreateParameter();
dp.ParameterName = "model_id";
dp.Value = model_id;
cmd.Parameters.Add(dp);
da = DatabaceFactory.CreateAdapter(cmd, DatabaseType.SQLServer);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt != null && dt.Rows.Count > 0)
{
return((string)dt.Rows[0]["model_name"]);
}
return("");
}
internal void LoadModels(bool IsCompleteMode = false)
{
IDbConnection cnn = DatabaceFactory.GetPISConnection();
StringBuilder sql = new StringBuilder();
//優化這一段，如果已查過一次model資訊，在model建構子中就不用再查一次model資訊
sql.Append(this.GetPartModelByPISSQL());
//sql.AppendLine(string.Format(" Select distinct m.Model_name, mp.part_no as PART_NO, m.MODEL_ID "));
//sql.AppendLine(string.Format(" From model m "));
//sql.AppendLine(string.Format(" left join Category_Model cm on cm.Model_name=m.Model_name "));
//sql.AppendLine(string.Format(" left join CATALOG_CATEGORY cc on cc.Category_id=cm.Category_id "));
//sql.AppendLine(string.Format(" left join Model_Publish mpu on mpu.model_name =m.model_name and mpu.Site_ID ='ACL' "));
//sql.AppendLine(string.Format(" left join model_product mp on m.model_name=mp.Model_name and mp.relation='product' and mp.PART_NO = @PN "));
//sql.AppendLine(string.Format(" left join product on product.part_no=mp.Part_no "));
//sql.AppendLine(string.Format(" Where cc.CATALOGID IN ('{0}', '{1}') ", new string[] { "1-2JKBQD", "1-2MLAX2" }));
//sql.AppendLine(string.Format(" And mpu.Active_FLG ='Y'"));
//sql.AppendLine(string.Format(" And mp.STATUS<>'deleted' "));
//sql.AppendLine(string.Format(" AND (PRODUCT.PART_NO =@PN or m.Model_name= substring(@PN,0, charindex('-',@PN,charindex('-',@PN)+1)) "));
//sql.AppendLine(string.Format(" or m.MODEL_NAME =@PN "));
//sql.AppendLine(string.Format(" ) "));
//sql.AppendLine(string.Format(" Order by m.Model_name DESC "));
IDbCommand cmd = DatabaceFactory.CreateCommand(sql.ToString(), DatabaseType.SQLServer, cnn);
IDbDataParameter dp = cmd.CreateParameter();
dp.ParameterName = "PN";
dp.Value = this.PartNumber;
cmd.Parameters.Add(dp);
DbDataAdapter da = DatabaceFactory.CreateAdapter(cmd, DatabaseType.SQLServer);
DataTable dt = new DataTable("Model");
da.Fill(dt);
if (dt == null || dt.Rows.Count == 0)
{
//If part is not hooked to model in PIS, then get model and part relationship from SAP(PRODUCT_LOGISTICS_NEW)
sql.Clear();
sql.Append(this.GetPartModelBySAPSQL());
//sql.AppendLine(string.Format(" Select distinct top 1 m.Model_name,ISNULL(mp.part_no,pln.PART_NO) as PART_NO ,m.MODEL_ID "));
//sql.AppendLine(string.Format(" from dbo.model m "));
//sql.AppendLine(string.Format(" left join dbo.Category_Model cm on cm.Model_name=m.Model_name "));
//sql.AppendLine(string.Format(" left join dbo.CATALOG_CATEGORY cc on cc.Category_id=cm.Category_id "));
//sql.AppendLine(string.Format(" left join Model_Publish mpu on mpu.model_name =m.model_name and mpu.Site_ID ='ACL' "));
//sql.AppendLine(string.Format(" left join PRODUCT_LOGISTICS_NEW pln on pln.MODEL_NO =m.MODEL_NAME "));
//sql.AppendLine(string.Format(" and pln.PART_NO = @PN "));
//sql.AppendLine(string.Format(" left join model_product mp on m.model_name=mp.Model_name and mp.relation='product' and mp.PART_NO = @PN "));
//sql.AppendLine(string.Format(" left join product on product.part_no=mp.Part_no "));
//sql.AppendLine(string.Format(" Where cc.CATALOGID IN ('{0}', '{1}') ", new string[] { "1-2JKBQD", "1-2MLAX2" }));
//sql.AppendLine(string.Format(" And mp.STATUS<>'deleted' "));
//sql.AppendLine(string.Format(" AND (PRODUCT.PART_NO =@PN or m.Model_name= substring(@PN,0, charindex('-',@PN,charindex('-',@PN)+1)) "));
//sql.AppendLine(string.Format(" or pln.PART_NO =@PN or m.MODEL_NAME =@PN "));
//sql.AppendLine(string.Format(" ) "));
//sql.AppendLine(string.Format(" order by m.Model_name DESC "));
cmd.CommandText = sql.ToString();
dt = new DataTable("Model");
da.Fill(dt);
}
foreach (DataRow _row in dt.Rows)
{
Model _model = new Model((string)_row["Model_name"]);
if (IsCompleteMode)
{
_model.LoadCompleteModelInformation();
}
else
{
_model.LoadBasicModelInformation();
}
this._Models.Add(_model);
}
}
/// <summary>
/// Load complete model information
/// </summary>
/// <param name="modelname"></param>
internal void LoadCompleteModelInformation()
{
if (string.IsNullOrEmpty(this.Model_Name))
{
return;
}
IDbConnection cnn = DatabaceFactory.GetPISConnection();
IDbCommand cmd = null;
IDbDataParameter dp = null;
DbDataAdapter da = null;
StringBuilder sql = new StringBuilder();
//Frank 其它語系待處理
sql.Clear();
sql.AppendLine(this.GetModelSQL());
//Model in different Langs
sql.AppendLine(";");
sql.AppendLine(this.GetModel_LangSQL());
//Model literatures
sql.AppendLine(";");
sql.AppendLine(this.GetLiteraturesSQL());
//Model Features
sql.AppendLine(";");
sql.AppendLine(this.GetFeaturesSQL());
//Model Specs
sql.AppendLine(";");
sql.AppendLine(this.GetModelSpecsSQL());
cmd = DatabaceFactory.CreateCommand(sql.ToString(), DatabaseType.SQLServer, cnn);
dp = cmd.CreateParameter();
dp.ParameterName = "model_name";
dp.Value = this.Model_Name;
cmd.Parameters.Add(dp);
da = DatabaceFactory.CreateAdapter(cmd, DatabaseType.SQLServer);
DataSet DsModel = new DataSet();
da.Fill(DsModel);
//=====================Model=========================================
if (DsModel.Tables[0] != null)
{
this.WriteToAttributeModel(DsModel.Tables[0]);
}
//=====================Model_Langs=========================================
//Frank 其它語系待處理
if (DsModel.Tables[1] != null)
{
this.WriteToAttributeModel_Lang(DsModel.Tables[1]);
}
//=====================Literatures=====================================
if (DsModel.Tables[2] != null)
{
this.WriteToAttributeLiteratures(DsModel.Tables[2]);
}
//======================Features==========================================
if (DsModel.Tables[3] != null)
{
this.WriteToAttributeFeatures(DsModel.Tables[3]);
}
//======================Features==========================================
if (DsModel.Tables[4] != null)
{
this.WriteToAttributeModelSpecs(DsModel.Tables[4]);
}
}