public string getProductType(string PartNO)
{
IDbConnection connMY = DatabaceFactory.CreateConnection(ConfigurationManager.ConnectionStrings["MY"].ConnectionString, DatabaseType.SQLServer);
string sql = string.Format("select top 1 isnull(PRODUCT_TYPE,'') as PRODUCT_TYPE from SAP_PRODUCT where PART_NO =@PART_NO");
IDbCommand cmd = DatabaceFactory.CreateCommand(sql, DatabaseType.SQLServer, connMY);
IDbDataParameter dp = cmd.CreateParameter(); dp.ParameterName = "PART_NO"; dp.Value = PartNO; cmd.Parameters.Add(dp);
connMY.Open();
object retObj = null;
try
{
retObj = cmd.ExecuteScalar();
}
catch (Exception ex)
{ }
finally
{
connMY.Close();
}
if (retObj != null && !string.IsNullOrEmpty(retObj.ToString()))
{
return(retObj.ToString().Trim());
}
return(string.Empty);
}
public static bool LogQuote2Order(string OrderID, string QuoteID, ref string Msg)
{
if (string.IsNullOrEmpty(OrderID) || string.IsNullOrEmpty(QuoteID))
{
return(false);
}
StringBuilder sql = new StringBuilder();
sql.Append(" select top 1 ORDER_ID, ORDER_NO,PO_NO,CREATED_DATE,CREATED_BY ");
sql.Append(" from ORDER_MASTER where ORDER_ID =@ORDER_ID ");
IDbDataParameter dp = null;
IDbConnection connMY = DatabaceFactory.CreateConnection(ConfigurationManager.ConnectionStrings["MY"].ConnectionString, DatabaseType.SQLServer);
IDbCommand cmd = DatabaceFactory.CreateCommand(sql.ToString(), DatabaseType.SQLServer, connMY);
dp = cmd.CreateParameter();
dp.ParameterName = "ORDER_ID";
dp.Value = OrderID;
cmd.Parameters.Add(dp);
DbDataAdapter da = DatabaceFactory.CreateAdapter(cmd, DatabaseType.SQLServer);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt != null && dt.Rows.Count > 0)
{
sql.Clear();
sql.Append(" INSERT INTO [QUOTE_TO_ORDER_LOG] ([QUOTEID] ,[SO_NO],[PO_NO] ,[ORDER_DATE] ,[ORDER_BY]) ");
sql.Append(" VALUES ( @QUOTEID ,@SO_NO,@PO_NO ,@ORDER_DATE ,@ORDER_BY) ");
IDbConnection connEQ = DatabaceFactory.CreateConnection(ConfigurationManager.ConnectionStrings["EQ"].ConnectionString, DatabaseType.SQLServer);
cmd = DatabaceFactory.CreateCommand(sql.ToString(), DatabaseType.SQLServer, connEQ);
dp = cmd.CreateParameter(); dp.ParameterName = "QUOTEID"; dp.Value = QuoteID; cmd.Parameters.Add(dp);
dp = cmd.CreateParameter(); dp.ParameterName = "SO_NO"; dp.Value = dt.Rows[0]["ORDER_NO"] ?? ""; cmd.Parameters.Add(dp);
dp = cmd.CreateParameter(); dp.ParameterName = "PO_NO"; dp.Value = dt.Rows[0]["PO_NO"] ?? ""; cmd.Parameters.Add(dp);
dp = cmd.CreateParameter(); dp.ParameterName = "ORDER_DATE"; dp.Value = dt.Rows[0]["CREATED_DATE"] ?? ""; cmd.Parameters.Add(dp);
dp = cmd.CreateParameter(); dp.ParameterName = "ORDER_BY"; dp.Value = dt.Rows[0]["CREATED_BY"] ?? ""; cmd.Parameters.Add(dp);
int retInt = -1;
connEQ.Open();
try
{
retInt = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Msg = ex.ToString();
}
finally
{
connEQ.Close();
connMY.Close();
}
if (retInt > 0)
{
return(true);
}
}
return(false);
}
public void getOfficeGroupByEripIdOrg(string AccountRowId, string EripId, string org, out string officecode, out string groupcode, ref string Msg)
{
officecode = string.Empty; groupcode = string.Empty; EripId = EripId.Trim().ToUpper();
DataTable dt = new DataTable();
IDbConnection connMY = DatabaceFactory.CreateConnection(ConfigurationManager.ConnectionStrings["MY"].ConnectionString, DatabaseType.SQLServer);
IDbConnection connEQ = DatabaceFactory.CreateConnection(ConfigurationManager.ConnectionStrings["EQ"].ConnectionString, DatabaseType.SQLServer);
if (!string.IsNullOrEmpty(EripId))
{
string sql = string.Format("SELECT top 1 isnull(SalesOffice,'') as OfficeCode, isnull(SalesGroup,'') as GroupCode from sap_dimcompany where org_ID='{0}' AND company_id='{1}'", org, EripId);
IDbCommand cmd = DatabaceFactory.CreateCommand(sql, DatabaseType.SQLServer, connMY);
DbDataAdapter da = DatabaceFactory.CreateAdapter(cmd, DatabaseType.SQLServer);
connMY.Open();
da.Fill(dt);
if (dt != null && dt.Rows.Count == 1)
{
if (!string.IsNullOrEmpty(dt.Rows[0]["OfficeCode"].ToString()))
{
officecode = dt.Rows[0]["OfficeCode"].ToString();
}
if (!string.IsNullOrEmpty(dt.Rows[0]["GroupCode"].ToString()))
{
groupcode = dt.Rows[0]["GroupCode"].ToString();
}
}
connMY.Close();
}
if (string.IsNullOrEmpty(officecode) || string.IsNullOrEmpty(groupcode))
{
StringBuilder sb = new StringBuilder();
sb.Append(" SELECT TOP 1 a.ROW_ID, ISNULL(b.ATTRIB_05, N'') AS ERP_ID, a.NAME AS ACCOUNT_NAME, ");
sb.Append(" a.CUST_STAT_CD AS ACCOUNT_STATUS, ISNULL(a.MAIN_FAX_PH_NUM, N'') AS FAX_NUM, ");
sb.Append(" ISNULL(a.MAIN_PH_NUM, N'') AS PHONE_NUM, ISNULL(a.OU_TYPE_CD, N'') AS OU_TYPE_CD, ISNULL(a.URL, N'') ");
sb.Append(" AS URL, ISNULL(b.ATTRIB_34, N'') AS BusinessGroup, ISNULL(a.OU_TYPE_CD, N'') AS ACCOUNT_TYPE, ");
sb.Append(" ISNULL(c.NAME, N'') AS RBU, ISNULL");
sb.Append(" ((SELECT EMAIL_ADDR");
sb.Append(" FROM S_CONTACT");
sb.Append(" WHERE (ROW_ID IN");
sb.Append(" (SELECT PR_EMP_ID");
sb.Append(" FROM S_POSTN");
sb.Append(" WHERE (ROW_ID IN");
sb.Append(" (SELECT PR_POSTN_ID");
sb.Append(" FROM S_ORG_EXT");
sb.Append(" WHERE (ROW_ID = a.ROW_ID)))))), N'') AS PRIMARY_SALES_EMAIL, ");
sb.Append(" a.PAR_OU_ID AS PARENT_ROW_ID, ISNULL(b.ATTRIB_09, N'N') AS MAJORACCOUNT_FLAG, ISNULL(a.CMPT_FLG, ");
sb.Append(" N'N') AS COMPETITOR_FLAG, ISNULL(a.PRTNR_FLG, N'N') AS PARTNER_FLAG, ISNULL(d.COUNTRY, N'') ");
sb.Append(" AS COUNTRY, ISNULL(d.CITY, N'') AS CITY, ISNULL(d.ADDR, N'') AS ADDRESS, ISNULL(d.STATE, N'') AS STATE, ");
sb.Append(" ISNULL(d.ZIPCODE, N'') AS ZIPCODE, ISNULL(d.PROVINCE, N'') AS PROVINCE, ISNULL");
sb.Append(" ((SELECT TOP (1) NAME");
sb.Append(" FROM S_INDUST");
sb.Append(" WHERE (ROW_ID = a.X_ANNIE_PR_INDUST_ID)), N'N/A') AS BAA, b.CREATED, ");
sb.Append(" b.LAST_UPD AS LAST_UPDATED, ISNULL");
sb.Append(" ((SELECT TOP (1) e.NAME");
sb.Append(" FROM S_PARTY AS e INNER JOIN");
sb.Append(" S_POSTN AS f ON e.ROW_ID = f.OU_ID");
sb.Append(" WHERE (f.ROW_ID IN");
sb.Append(" (SELECT PR_POSTN_ID");
sb.Append(" FROM S_ORG_EXT AS S_ORG_EXT_2");
sb.Append(" WHERE (ROW_ID = a.ROW_ID)))), N'') AS PriOwnerDivision, ");
sb.Append(" a.PR_POSTN_ID AS PriOwnerRowId, ISNULL");
sb.Append(" ((SELECT TOP (1) NAME");
sb.Append(" FROM S_POSTN AS f");
sb.Append(" WHERE (ROW_ID IN");
sb.Append(" (SELECT PR_POSTN_ID");
sb.Append(" FROM S_ORG_EXT AS S_ORG_EXT_1");
sb.Append(" WHERE (ROW_ID = a.ROW_ID)))), N'') AS PriOwnerPosition, CAST('' AS nvarchar(10)) ");
sb.Append(" AS LOCATION, CAST('' AS nvarchar(10)) AS ACCOUNT_TEAM, ISNULL(d.ADDR_LINE_2, N'') AS ADDRESS2, ");
sb.Append(" ISNULL(b.ATTRIB_36, N'') AS ACCOUNT_CC_GRADE, ISNULL(a.BASE_CURCY_CD, N'') AS CURRENCY");
sb.Append(" FROM S_ORG_EXT AS a LEFT OUTER JOIN");
sb.Append(" S_ORG_EXT_X AS b ON a.ROW_ID = b.ROW_ID LEFT OUTER JOIN");
sb.Append(" S_PARTY AS c ON a.BU_ID = c.ROW_ID LEFT OUTER JOIN");
sb.Append(" S_ADDR_ORG AS d ON a.PR_ADDR_ID = d.ROW_ID");
sb.Append(" WHERE (a.ROW_ID = @RID)");
IDbDataParameter dp = null;
IDbConnection connCRM = DatabaceFactory.CreateConnection(ConfigurationManager.ConnectionStrings["CRM"].ConnectionString, DatabaseType.SQLServer);
IDbCommand cmd = DatabaceFactory.CreateCommand(sb.ToString(), DatabaseType.SQLServer, connCRM);
dp = cmd.CreateParameter(); dp.ParameterName = "RID"; dp.Value = AccountRowId; cmd.Parameters.Add(dp);
connCRM.Open();
DbDataAdapter da = DatabaceFactory.CreateAdapter(cmd, DatabaseType.SQLServer);
dt.Clear();
da.Fill(dt);
if (dt != null && dt.Rows.Count == 1)
{
//'JJ 2014/2/13：Company_id如果空白時需要Office和Sales Group因為gpblock_logic這個table需要知道office和sales Group才能知道要appove給誰
//'由Sales的Emal去SIEBEL_POSITION的PRIMARY_POSITION_NAME查出字串如：AFR/Embedded/AOnline-eSales-SEU-Agnes_Iglesias，PRIMARY_POSITION_NAME 鎖定Embedded或iA
//'再由字串split(1)分出來Sales Group是EC Aonline(Embedded是EC Aonline、iA是iA Aonline)
//'由SIEBEL_POSITION join SIEBEL_CONTACT，再由SIEBEL_CONTACT的OrgID就能知道office
//'如果查不到就由317(EC Aonline)的[email protected]和[email protected]來appove
string UserEmail = HttpContext.Current.User.Identity.Name;
string sql = string.Format("SELECT TOP 1 a.PRIMARY_POSITION_NAME + ',' + b.OrgID FROM SIEBEL_POSITION a left join SIEBEL_CONTACT b on a.CONTACT_ID = b.ROW_ID WHERE a.EMAIL_ADDR =@UserEmail and(a.PRIMARY_POSITION_NAME like '%Embedded%' or a.PRIMARY_POSITION_NAME like '%iA%')");
cmd = DatabaceFactory.CreateCommand(sql, DatabaseType.SQLServer, connMY);
dp = cmd.CreateParameter(); dp.ParameterName = "UserEmail"; dp.Value = UserEmail; cmd.Parameters.Add(dp);
connMY.Open();
object retObj = null;
try
{
retObj = cmd.ExecuteScalar();
}
catch (Exception ex)
{ Msg += ex.ToString(); }
finally
{
connMY.Close();
}
//例：ABN/iA/AOnline-Manager-NEU-Wenyu_Lai,AUK
if ((retObj != null) && !string.IsNullOrEmpty(retObj.ToString()))
{
string sOffice = retObj.ToString().Split(new char[] { ',' })[1];
//例：AUK
string sGroup = retObj.ToString().Split(new char[] { ',' })[0].Split(new char[] { '/' })[1];
//例：iA
//轉換Sales Group Name
if (sGroup == "Embedded")
{
sGroup = "EC Aonline";
}
else if (sGroup == "iA")
{
sGroup = "iA Aonline";
}
//找出office code
sql = "SELECT TOP 1 OFFICE_CODE from gpblock_logic where OFFICE_NAME=@sOffice and active=1 AND TYPE='GP' and group_name=@sGroup";
cmd = DatabaceFactory.CreateCommand(sql, DatabaseType.SQLServer, connEQ);
dp = cmd.CreateParameter(); dp.ParameterName = "sOffice"; dp.Value = sOffice; cmd.Parameters.Add(dp);
IDbDataParameter dpgroup = cmd.CreateParameter(); dpgroup.ParameterName = "sGroup"; dpgroup.Value = sGroup; cmd.Parameters.Add(dpgroup);
connEQ.Open();
retObj = null;
try
{
retObj = cmd.ExecuteScalar();
}
catch (Exception ex)
{ Msg += ex.ToString(); }
finally
{
connEQ.Close();
}
if ((retObj != null) && !string.IsNullOrEmpty(retObj.ToString()))
{
officecode = retObj.ToString();
//找出Group code
sql = "SELECT TOP 1 GROUP_CODE from gpblock_logic where OFFICE_NAME=@sOffice and active=1 AND TYPE='GP' and group_name=@sGroup";
cmd = DatabaceFactory.CreateCommand(sql, DatabaseType.SQLServer, connEQ);
dp = cmd.CreateParameter(); dp.ParameterName = "sOffice"; dp.Value = sOffice; cmd.Parameters.Add(dp);
dpgroup = cmd.CreateParameter(); dpgroup.ParameterName = "sGroup"; dpgroup.Value = sGroup; cmd.Parameters.Add(dpgroup);
connEQ.Open();
retObj = null;
try
{
retObj = cmd.ExecuteScalar();
}
catch (Exception ex)
{ Msg += ex.ToString(); }
finally
{
connEQ.Close();
}
///////
if ((retObj != null) && !string.IsNullOrEmpty(retObj.ToString()))
{
groupcode = retObj.ToString();
}
else
{
groupcode = "317";
//找不到就都帶317 (EC Aonline)
}
}
else
{
//都沒有就由Account RBU去查office code，而group code就固定是317 (EC Aonline)
object R = new object();
sql = "SELECT TOP 1 OFFICE_CODE from gpblock_logic where OFFICE_NAME=@OFFICE_NAME and active=1 AND TYPE='GP' and group_name='317'";
cmd = DatabaceFactory.CreateCommand(sql, DatabaseType.SQLServer, connEQ);
dp = cmd.CreateParameter(); dp.ParameterName = "OFFICE_NAME"; dp.Value = dt.Rows[0]["RBU"]; cmd.Parameters.Add(dp);
connEQ.Open();
try
{
R = cmd.ExecuteScalar();
}
catch (Exception ex)
{ }
finally
{
connEQ.Close();
}
if ((R != null) && !string.IsNullOrEmpty(R.ToString()))
{
officecode = R.ToString();
groupcode = "317"; //找不到就都帶317 (EC Aonline)
}
}
}
}
connCRM.Close();
}
//JJ 2014/2/25 都找不到預設就帶ADL
if (string.IsNullOrEmpty(officecode))
{
officecode = "3000";
}
//JJ 2014/2/25 都找不到預設就帶EC Aonline
if (string.IsNullOrEmpty(groupcode))
{
groupcode = "317";
}
}
