public static DataTable GetDefaultSalesFromSiebel(string AccountRowID)
{
var dt = new DataTable();
if (!string.IsNullOrEmpty(AccountRowID))
{
String sql = String.Format(@" select b.id_sap as SALES_CODE, a.PRIMARY_SALES_EMAIL as EMAIL from SIEBEL_ACCOUNT a inner join EAI_IDMAP b on a.PRIMARY_SALES_EMAIL = b.id_email inner join EZ_EMPLOYEE c on b.id_email = c.EMAIL_ADDR where a.ROW_ID = '{0}' order by b.id_sap", AccountRowID);
dt = DBUtil.dbGetDataTable("MY", sql);
}
return(dt);
}
public static DataTable GetSalesInfo(string Email)
{
var DT = new DataTable();
if (!string.IsNullOrEmpty(Email))
{
string _sql = " select top 1 (isnull(FirstName,'') + ' ' + isnull(LastName,'')) as FULL_NAME,isnull(CellPhone,'') as TEL,isnull(EMAIL_ADDRESS,'') as EMAIL from SIEBEL_CONTACT ";
_sql += string.Format(" where EMAIL_ADDRESS='{0}' ", Email.Trim().Replace("'", "''"));
DT = DBUtil.dbGetDataTable("MY", _sql);
}
return(DT);
}
public static DataTable GetSalesInfoBySalesCode(string salesCode)
{
var DT = new DataTable();
if (!string.IsNullOrEmpty(salesCode))
{
string _sql = " select top 1 (isnull(FIRST_NAME,'') + ' ' + isnull(LAST_NAME,'')) as FULL_NAME,isnull(TEL,'') as TEL,isnull(EMAIL,'') as EMAIL from SAP_EMPLOYEE ";
_sql += string.Format(" where SALES_CODE ='{0}' ", salesCode.Trim().Replace("'", "''"));
DT = DBUtil.dbGetDataTable("MY", _sql);
}
return(DT);
}
public static DataTable GetDefaultSalesFromSAP(string ERPID)
{
var dt = new DataTable();
if (!string.IsNullOrEmpty(ERPID))
{
String sql = String.Format(@" select b.id_sap as SALES_CODE, b.id_email as EMAIL from SAP_COMPANY_EMPLOYEE a inner join EAI_IDMAP b on a.SALES_CODE = b.id_sap inner join EZ_EMPLOYEE c on b.id_email = c.EMAIL_ADDR
where a.COMPANY_ID = '{0}' and a.PARTNER_FUNCTION = 'VE'", ERPID);
dt = DBUtil.dbGetDataTable("MY", sql);
}
return(dt);
}
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);
}