// internal DataTable GetModelListByPart(String partNo)
// {
// StringBuilder sql = new StringBuilder();
// sql.Append(" Select model_name ");
// sql.Append(" From model ");
// SqlCommand sqlCmd = new SqlCommand(sql.ToString(),this.sqlConn);
// //sqlCmd.Parameters.AddWithValue
// DataTable _dt = null;
// using (SqlDataReader dr = sqlCmd.ExecuteReader())
// {
// _dt = new DataTable();
// _dt.Load(dr);
// }
// return _dt;
//}
public static DataTable getMainCategoryList(string ModelName)
{
var _sql = new StringBuilder(); var sql = new StringBuilder();
var DT = new DataTable();
_sql.AppendLine("SELECT model_name,Category_id,isnull(MainCategory,'') as MainCategory FROM [Category_Model] ");
_sql.AppendLine("where model_name='" + ModelName + "'");
DataTable _dt = SqlProvider.dbGetDataTable("PIS", _sql.ToString());
DT.TableName = "Main Categories List";
DT.Columns.Add("Path"); DT.Columns.Add("CategoryID"); DT.Columns.Add("Main_Category");
if (_dt.Rows.Count > 0)
{
foreach (DataRow _row in _dt.Rows)
{
string _Path = ""; DataTable dtPath = null;
string _Category = _row[1].ToString();
string _CategoryID = "";
Boolean _MainCategory;
if (_row[2].ToString() == "Y")
{
_MainCategory = true;
}
else
{
_MainCategory = false;
}
for (var i = 0; i <= 6; i++)
{
if (i == 0)
{
_CategoryID = _Category;
}
sql.Clear();
sql.AppendLine(" SELECT isnull([PARENT_CATEGORY_ID],'') as PARENT_CATEGORY_ID,isnull([DISPLAY_NAME],'') as DISPLAY_NAME ");
sql.AppendLine(" FROM [CATEGORY] With(nolock) ");
sql.AppendLine(" where CATEGORY_ID='" + _CategoryID + "' ");
dtPath = SqlProvider.dbGetDataTable("PIS", sql.ToString());
if (dtPath.Rows.Count != 0)
{
if (dtPath.Rows[0][0].ToString() == "root")
{
_Path = dtPath.Rows[0][1].ToString() + " / " + _Path; //抓取Display name
break; //已經到root後，抓取完成就離開
}
else
{
if (string.IsNullOrEmpty(_Path))
{
_Path = dtPath.Rows[0][1].ToString();//抓取Display name
}
else
{
_Path = dtPath.Rows[0][1].ToString() + " / " + _Path; //抓取Display name
}
_CategoryID = dtPath.Rows[0][0].ToString(); //抓取Parent categoryID
}
}
else
{
break;
}
}
if (!string.IsNullOrEmpty(_Path))
{
DataRow newRow_DT = DT.NewRow();
newRow_DT["Path"] = _Path;
newRow_DT["CategoryID"] = _Category;
newRow_DT["Main_Category"] = _MainCategory;
DT.Rows.Add(newRow_DT);
}
}
}
return(DT);
}
/// <summary>
///
/// </summary>
/// <param name="sales_code"></param>
/// <returns></returns>
public static DataTable GetSaleseManager(string sales_code)
{
return(SqlProvider.dbGetDataTable("MY", string.Format(" select id_sap as sales_code,id_rbu,id_email,id_sector from EAI_IDMAP where id_sap = '{0}' and id_fact_zone='North America' and id_sector like '%AOnline%' ", sales_code)));
}
/// <summary>
/// Get Siebel Sales Position by email
/// </summary>
/// <param name="emial"></param>
/// <returns>POSITION</returns>
public static object GetSiebelSalesPositionByEmail(string emial)
{
//return SqlProvider.dbExecuteScalar("MY", string.Format(" select top 1 POSITION from SIEBEL_SALES_HIERARCHY where EMAIL = '{0}' ", emial));
//ICC 2015/11/11 SIEBEL_SALES_HIERARCHY data is not accurate, so change to SIEBEL_POSITION for instead.
return(SqlProvider.dbExecuteScalar("MY", string.Format(" select top 1 PRIMARY_POSITION_NAME from SIEBEL_POSITION where EMAIL_ADDR = '{0}' order by CREATED desc", emial)));
}
public static DataTable getGP_ParameterByMargin(string RBU, decimal margin)
{
DataTable dt = SqlProvider.dbGetDataTable("EQ", string.Format("SELECT [RBU] ,isnull(GP_Percent,0) as [GP_Percent] ,isnull(Approver,'') as [Approver] FROM [dbo].[GP_Parameters] WHERE [RBU]='{0}' and GP_Percent>={1} Order by GP_Percent Desc, SEQ", RBU, margin));
return(dt);
}