public static string setMarketPlace(string model_id, string model_name, string category_id)
{
string request = "";
var _sql = new StringBuilder();
if (!string.IsNullOrEmpty(model_name) && !string.IsNullOrEmpty(category_id))
{
try
{
_sql.Clear();
_sql.AppendLine(string.Format("DELETE FROM Category_Model where model_name='{0}' ", model_name));
_sql.AppendLine(" and Category_id in('7cb22a45-ffb1-4bf6-928e-4e743bf87c15','5e4cbeae-f003-41d1-b9cf-1dc0baefa4ec','fed9a697-c31c-4fee-b112-74ad00b2815f','3a7cf93c-0bd4-4fa3-93b1-1345682be707')");
var _request1 = SqlProvider.dbExecuteNoQuery("PIS", _sql.ToString());
var dt_seq = SqlProvider.dbGetDataTable("PIS", string.Format("select isnull(max(SEQ),'') as seq from Category_Model where Category_id='{0}'", category_id));
var dt_ID = SqlProvider.dbGetDataTable("PIS", "select isnull(max(ID),'') as ID from Category_Model");
int ID = string.IsNullOrEmpty(dt_ID.Rows[0][0].ToString()) ? 0 + 1 : Convert.ToInt32(dt_ID.Rows[0][0]) + 1;
int seq = string.IsNullOrEmpty(dt_seq.Rows[0][0].ToString()) ? 0 + 1 : Convert.ToInt32(dt_seq.Rows[0][0]) + 1;
_sql.Clear();
_sql.AppendLine(string.Format("INSERT INTO Category_Model VALUES ({0},'{1}','{2}','{3}'", ID, model_id, model_name, category_id));
_sql.AppendLine(string.Format(",{0},GETDATE(),'*****@*****.**','')", seq));
var _request = SqlProvider.dbExecuteNoQuery("PIS", _sql.ToString());
}
catch (Exception e)
{
request = "setMarketPlace failed : " + e.ToString();
}
}
else
{
request = "setMarketPlace is failed : model name: " + model_name + " & category id: " + category_id;
}
return(request);
}
public static DataTable getPartNo(string ModelName, string partno)
{
var _sql = new StringBuilder();
if (!string.IsNullOrEmpty(partno))
{
_sql.AppendLine("SELECT PART_NO,isnull(PRODUCT_DESC,'') as [DESC_PLM],isnull(PRODUCT_DESC,'') as [DESC_COM] ");
_sql.AppendLine(",'inactive' as ACTIVE,isnull(STATUS,'') AS SAP_STATUS ");
_sql.AppendLine(" FROM [PIS].[dbo].[PRODUCT_LOGISTICS_NEW] ");
_sql.AppendLine(" where part_no='" + partno + "' and ORG_ID='TW01' ");
}
else
{
_sql.AppendLine("select a.PART_NO,isnull(c.PRODUCT_DESC,'') as [DESC_PLM],a.desc_com as [DESC_COM],c.STATUS as SAP_STATUS, ");
_sql.AppendLine(" CASE when a.status is null Then 'active' when a.status = '' Then 'active' else rtrim(a.status) end as ACTIVE ");
_sql.AppendLine("from model_product a ");
_sql.AppendLine("left join PIS.dbo.PRODUCT_LOGISTICS_NEW c on c.part_no=a.part_no and c.ORG_ID='TW01' ");
_sql.AppendLine(" where a.model_name='" + ModelName + "' and a.relation = 'product' and a.status <> 'deleted' ");
_sql.AppendLine("order by a.seq_num ");
}
DataTable _dt = SqlProvider.dbGetDataTable("PIS", _sql.ToString());
return(_dt);
}
public static DataTable GetBBWebPrice(String PartNo)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT PartNo,isnull(L1Price,0) as L1Price ");
sql.AppendLine(" FROM BBESTORE_WEB_PRICE ");
sql.AppendLine(" WHERE PartNo='" + PartNo + "' ");
return(SqlProvider.dbGetDataTable("MY", sql.ToString()));
}
/// <summary>
/// Get AKR's GP approver by sales eamil
/// </summary>
/// <param name="Sales_email"></param>
/// <returns></returns>
public static String getAKRGP_Approver(string Sales_email)
{
DataTable dt = SqlProvider.dbGetDataTable("EQ", string.Format("SELECT Sales_email,isnull(Report_to,'') as [Approver] FROM [dbo].[AKR_Sales_List] WHERE [Sales_email]='{0}'", Sales_email));
String approver = string.Empty;
if (dt != null && dt.Rows.Count > 0)
{
approver = dt.Rows[0]["Approver"].ToString();
}
return(approver);
}
public static string ReOrderBySeq(string GUID)
{
UpdateDBResult res = new UpdateDBResult();
String select_str = String.Empty;
try
{
select_str = " DECLARE @Child hierarchyid " +
" SELECT @Child = HIE_ID FROM CBOM_CATALOG_V2 " +
" WHERE ID = '" + GUID + "' " +
" SELECT * FROM CBOM_CATALOG_V2 " +
" WHERE HIE_ID.GetAncestor(1) = @Child " +
" ORDER BY SEQ_NO ";
DataTable dt = SqlProvider.dbGetDataTable("CBOMV2", select_str);
if (dt != null && dt.Rows.Count > 0)
{
int seq = 0;
String str1 = String.Empty;
List <String> str2 = new List <string>();
foreach (DataRow d in dt.Rows)
{
str1 += " WHEN '" + d["CATALOG_NAME"].ToString() + "' THEN '" + seq.ToString() + "' ";
str2.Add("'" + d["CATALOG_NAME"].ToString() + "'");
seq++;
}
String update_str = " UPDATE CBOM_CATALOG_V2 " +
" SET SEQ_NO = CASE CATALOG_NAME " +
str1 +
" ELSE SEQ_NO " +
" END " +
" WHERE CATALOG_NAME IN " + "(" + String.Join(", ", str2.ToArray()) + ")" + "; ";
SqlProvider.dbExecuteNoQuery("CBOMV2", update_str);
res.IsUpdated = true;
}
else
{
res.IsUpdated = false;
res.ServerMessage = "Children nodes not found.";
}
}
catch (Exception ex)
{
res.IsUpdated = false;
res.ServerMessage = ex.Message;
}
return(Newtonsoft.Json.JsonConvert.SerializeObject(res));
}
public static List <CBOM_CATALOG_RECORD> GetCBOMCatalogTreeByRootId(string OrgId)
{
String str = " DECLARE @ID hierarchyid " +
" SELECT @ID = HIE_ID " +
" FROM CBOM_CATALOG_V2 WHERE ID = '" + OrgId + "_Root'" +
" SELECT IsNull(cast(HIE_ID.GetAncestor(1) as nvarchar(100)),'') as PAR_HIE_ID, " +
" HIE_ID.GetLevel() AS [LEVEL], ID AS [ID], ID AS [VIRTUAL_ID], " +
" HIE_ID.ToString() AS [HIE_ID], CATALOG_NAME , CATALOG_TYPE, " +
" CATALOG_DESC, SEQ_NO, ORG " +
" FROM CBOM_CATALOG_V2 WHERE HIE_ID.IsDescendantOf(@ID) = 1 " +
" ORDER BY HIE_ID.GetLevel() ";
DataTable dtCatalogTree = SqlProvider.dbGetDataTable("CBOMV2", str);
List <CBOM_CATALOG_RECORD> CBOMCatalogRecords = dtCatalogTree.DataTableToList <CBOM_CATALOG_RECORD>();
return(CBOMCatalogRecords);
}
/// <summary>
///
/// </summary>
/// <param name="email"></param>
/// <returns></returns>
public static DataTable GetEmployeeDetail(String email)
{
//DataTable _dt = new DataTable();
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT A.[EMAIL_ADDR],A.[FST_NAME],A.[LST_NAME],A.[LOCAL_NAME] ");
sql.AppendLine(" ,A.[EMPLR_ID],A.[EXTENSION],isnull(B.SALES_CODE,'') as Sales_Code ");
sql.AppendLine(" FROM EZ_EMPLOYEE A LEFT JOIN SAP_EMPLOYEE B ");
sql.AppendLine(" ON A.EMAIL_ADDR=B.EMAIL ");
sql.AppendLine(" WHERE A.EMAIL_ADDR='" + email + "' ");
return(SqlProvider.dbGetDataTable("MY", sql.ToString()));
//return _dt;
}
public static string getMarketPlace(string model_name)
{
string request = "";
var _sql = new StringBuilder();
if (!string.IsNullOrEmpty(model_name))
{
_sql.AppendLine("select isnull(Category_id,'') as Category_id from Category_Model ");
_sql.AppendLine("where Category_id in('7cb22a45-ffb1-4bf6-928e-4e743bf87c15','5e4cbeae-f003-41d1-b9cf-1dc0baefa4ec','fed9a697-c31c-4fee-b112-74ad00b2815f','3a7cf93c-0bd4-4fa3-93b1-1345682be707') ");
_sql.AppendLine(string.Format("and model_name='{0}' ", model_name));
}
DataTable _dt = SqlProvider.dbGetDataTable("PIS", _sql.ToString());
if (_dt.Rows.Count > 0)
{
request = _dt.Rows[0][0].ToString();
}
return(request);
}
/// <summary>
/// ICC 2015/9/22 Get minimun order qty datatable. Use order ID and org ID paramete.
/// </summary>
/// <param name="order_id"></param>
/// <param name="plant"></param>
/// <param name="org_id"></param>
/// <returns>DataTable</returns>
public static DataTable getBelowMOQLineItem(string order_id, string org_id)
{
//ICC 2015/9/21 Modify minimun order qty sql. Change SAP_PRODUCT_ABC to SAP_PRODUCT_ORG, and use org id to get min_ord_qty
StringBuilder sql = new StringBuilder();
//sql.AppendLine(" select b.PART_NO,b.TotalQty,c.MIN_LOT_SIZE from ");
sql.AppendLine(" select b.PART_NO, b.TotalQty, c.MIN_ORDER_QTY from ");
sql.AppendLine(" ( ");
sql.AppendLine(" select a.PART_NO,SUM(a.QTY) as TotalQty ");
sql.AppendLine(" From ORDER_DETAIL a ");
sql.AppendLine(" where a.ORDER_ID='"+ order_id + "' ");
sql.AppendLine(" group by a.PART_NO ");
//sql.AppendLine(" ) b inner join SAP_PRODUCT_ABC c on b.PART_NO=c.PART_NO ");
//sql.AppendLine(" where c.PLANT='" + plant + "' ");
//sql.AppendLine(" and c.MIN_LOT_SIZE>0 ");
//sql.AppendLine(" and b.TotalQty<c.MIN_LOT_SIZE ");
sql.AppendLine(" ) b inner join SAP_PRODUCT_STATUS c on b.PART_NO = c.PART_NO");
sql.AppendFormat(" where c.SALES_ORG = '{0}' ", org_id);
sql.AppendLine(" and c.MIN_ORDER_QTY > 0 ");
sql.AppendLine(" and b.TotalQty < c.MIN_ORDER_QTY ");
return(SqlProvider.dbGetDataTable("MY", sql.ToString()));
}
// 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)));
}
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);
}