public static DataTable GetSiebelAccountList(SiebelAccountSearch search, List <string> SiebelRBU)
{
// Ryan 20171113 Add to select with RBU parameter
//ICC 2018/2/9 Change parameter. Use new class to search Siebel DB (Country, City, Zip code, State)
string _sql = " select TOP 500 a.ROW_ID AS ROW_ID, a.NAME as COMPANYNAME, IsNull(b.ATTRIB_05,'') as ERPID, ";
_sql += " IsNull(d.COUNTRY,'') as COUNTRY, IsNull(d.CITY,'') as CITY, Isnull(a.LOC,'') as LOCATION, ";
_sql += " IsNull(c.NAME, '') as RBU, IsNull(d.STATE,'') as STATE,IsNull(d.PROVINCE,'') as PROVINCE, ";
_sql += " IsNull(a.CUST_STAT_CD,'') as STATUS ,IsNull(d.ADDR,'') as ADDRESS, IsNull(d.ZIPCODE,'') as ZIPCODE, IsNull(d.ADDR_LINE_2,'') as ADDRESS2,";
_sql += " (SELECT TOP 1 isnull(EMAIL_ADDR,'') FROM S_CONTACT WHERE ROW_ID=(SELECT TOP 1 PR_EMP_ID from S_POSTN where ROW_ID = (SELECT TOP 1 PR_POSTN_ID FROM S_ORG_EXT WHERE ROW_ID=a.ROW_ID)) and EMAIL_ADDR is not null) AS priSales, ";
_sql += " IsNull(a.BASE_CURCY_CD,'') as CURRENCY ";
_sql += " from S_ORG_EXT a left join S_ORG_EXT_X b on a.ROW_ID=b.ROW_ID ";
_sql += " left join S_PARTY c on a.BU_ID=c.ROW_ID ";
_sql += " left join S_ADDR_PER d on a.PR_ADDR_ID=d.ROW_ID where 1 = 1 ";
if (SiebelRBU != null && SiebelRBU.Count > 0)
{
SiebelRBU = SiebelRBU.Select(d => d = "'" + d + "'").ToList();
_sql += " and c.NAME in (" + String.Join(",", SiebelRBU) + ") ";
}
if (!string.IsNullOrEmpty(search.AccountName))
{
_sql += string.Format(" and Upper(ISNULL(a.NAME,'')) like Upper(N'%{0}%') ", search.AccountName.Trim().ToUpper().Replace("'", "''").Replace("*", "%"));
}
if (!string.IsNullOrEmpty(search.ERPID))
{
_sql += string.Format(" and Upper(ISNULL(b.ATTRIB_05,'')) like Upper(N'%{0}%') ", search.ERPID.Trim().ToUpper().Replace("'", "''").Replace("*", "%"));
}
if (!string.IsNullOrEmpty(search.PrimarySales))
{
_sql += " AND a.PR_POSTN_ID IN (SELECT ROW_ID from S_POSTN WHERE S_POSTN.PR_EMP_ID IN(SELECT ROW_ID FROM S_CONTACT WHERE Upper(S_CONTACT.EMAIL_ADDR) like '%" + search.PrimarySales.ToUpper() + "%')) ";
}
if (!string.IsNullOrEmpty(search.AccountStatus))
{
_sql += string.Format(" and Upper(a.CUST_STAT_CD) = Upper(N'{0}') ", search.AccountStatus.Trim().ToUpper().Replace("'", "''").Replace("*", "%"));
}
if (!string.IsNullOrEmpty(search.Country))
{
_sql += string.Format(" and Upper(d.COUNTRY) like '%{0}%' ", search.Country.Trim().ToUpper().Replace("'", "''").Replace("*", "%"));
}
if (!string.IsNullOrEmpty(search.City))
{
_sql += string.Format(" and Upper(d.CITY) like '%{0}%' ", search.City.Trim().ToUpper().Replace("'", "''").Replace("*", "%"));
}
if (!string.IsNullOrEmpty(search.ZipCode))
{
_sql += string.Format(" and Upper(d.ZIPCODE) like '%{0}%' ", search.ZipCode.Trim().ToUpper().Replace("'", "''").Replace("*", "%"));
}
if (!string.IsNullOrEmpty(search.State))
{
_sql += string.Format(" and Upper(d.STATE) like '%{0}%' ", search.State.Trim().ToUpper().Replace("'", "''").Replace("*", "%"));
}
if (!string.IsNullOrEmpty(search.AccountRowId))
{
_sql += string.Format(" and Upper(ISNULL(a.ROW_ID,'')) like Upper(N'%{0}%') ", search.AccountRowId.Trim().ToUpper().Replace("'", "''").Replace("*", "%"));
}
_sql += "order by a.ROW_ID ";
DataTable SiebelAccount = DBUtil.dbGetDataTable("CRM", _sql);
return(SiebelAccount);
}
public static List <SelectListItem> GetORGList(string ERPID, string SiebelRBU)
{
var ORGList = new List <SelectListItem>();
var SAPList = new List <SAP_DIMCOMPANY>();
SAPList = MyAdvantechContext.Current.SAP_DIMCOMPANY.Where(d => d.COMPANY_ID.Equals(ERPID)).ToList();
if (SAPList != null)
{
if (SAPList.Count > 1)
{
// Ryan 20170927 For ACN - Check if SAP_COMPANY_ORG has been setup, if not, do insert sql immediately.
//Alex 20180629 Move ACN set org function to Myadvantech API
if (SAPList.Where(d => d.ORG_ID.StartsWith("CN")).Any())
{
//if (Convert.ToInt32(SqlProvider.dbExecuteScalar("MY", "select count(*) as count from SAP_COMPANY_ORG where COMPANY_ID = '" + ERPID + "'")) == 0)
//{
// SqlProvider.dbExecuteNoQuery("MY", String.Format("insert into SAP_COMPANY_ORG values ('{0}','CN10','CN10','1')", ERPID));
// SqlProvider.dbExecuteNoQuery("MY", String.Format("insert into SAP_COMPANY_ORG values ('{0}','CN30','CN30','0')", ERPID));
//}
Advantech.Myadvantech.DataAccess.SAPDAL.SetACNMultiOrg(ERPID);
}
string _sql = " select isnull(ORG_ID,'') as ORG_ID,isnull(ORG_NAME,'') as ORG_NAME,IS_DEFAULT from SAP_COMPANY_ORG ";
_sql += string.Format(" where COMPANY_ID = '{0}' and ORG_ID not in {1} ", ERPID.Trim().Replace("'", "''"), WebConfigurationManager.AppSettings["InvalidOrg"]);
DataTable ORG = DBUtil.dbGetDataTable("MY", _sql);
if (ORG != null && ORG.Rows.Count > 0)
{
for (int i = 0; i <= ORG.Rows.Count - 1; i++)
{
ORGList.Add(new SelectListItem()
{
Text = ORG.Rows[i][1].ToString(),
Value = ORG.Rows[i][0].ToString(),
Selected = (bool)ORG.Rows[i][2]
});
}
}
else
{
ORGList.Add(new SelectListItem()
{
Text = SAPList.FirstOrDefault().ORG_ID.ToString(),
Value = SAPList.FirstOrDefault().ORG_ID.ToString(),
Selected = true
});
}
}
else if (SAPList.Count == 1)
{
ORGList.Add(new SelectListItem()
{
Text = SAPList[0].ORG_ID,
Value = SAPList[0].ORG_ID
});
}
else
{
//Default value
ORGList.Add(new SelectListItem()
{
Text = GetDefaultOrgByRBU(SiebelRBU),
Value = GetDefaultOrgByRBU(SiebelRBU)
});
}
}
else
{
//Default value
ORGList.Add(new SelectListItem()
{
Text = GetDefaultOrgByRBU(SiebelRBU),
Value = GetDefaultOrgByRBU(SiebelRBU)
});
}
return(ORGList);
}
