public GuResult <MSearchOrganization> SearchOrganization(string Alphabetfilter, string Combofilter, string txtFilter, string userid, string lang, string local, string tagstr, Int64 curpage, Int64 maxRec)
{
GuResult <MSearchOrganization> ret = new GuResult <MSearchOrganization>();
ret.result = new MSearchOrganization();
ret.result.MOrganizationList = new List <MOrganization>();
ret.result.TagList = new List <CrmActivitiesTag>();
IDbSimplyTransaction trn = null;
CRM_Controller crmlib = new CRM_Controller();
string VisibleStr = crmlib.GetVisibilityString("O", userid);
// decimal cnt = 0;
Dictionary <string, object> paramList = new Dictionary <string, object>();
paramList.Add("LANG", lang);
paramList.Add("LOCAL", local);
string strfilter = GetStringFilter(Alphabetfilter, Combofilter, txtFilter, tagstr, ref paramList);
string sqlstr = "SELECT * FROM (SELECT ORGANIZE_ID, CASE WHEN :LANG <> :LOCAL THEN NVL(DESCR_OTH,DESCR_LOC) ELSE DESCR_OTH END AS ORGANIZE_NAME, ADDRESS || ( CASE WHEN SUBDISTRICT IS NOT NULL THEN ' ' || SUBDISTRICT ELSE '' END) || ( CASE WHEN DISTRICT IS NOT NULL THEN ' ' || DISTRICT ELSE '' END) || ( CASE WHEN CITY IS NOT NULL THEN ' ' || CASE WHEN :LANG <> :LOCAL THEN NVL(CI.DESC2,CI.DESC1) ELSE CI.DESC1 END ELSE '' END) || ( CASE WHEN STATE IS NOT NULL THEN ' ' || CASE WHEN :LANG <> :LOCAL THEN NVL(PO.DESC2,PO.DESC1) ELSE PO.DESC1 END ELSE '' END) || ( CASE WHEN COUNTRY IS NOT NULL THEN ' ' || CASE WHEN :LANG <> :LOCAL THEN NVL(CO.DESC2,CO.DESC1) ELSE CO.DESC1 END ELSE '' END) || ( CASE WHEN POSTALCODE IS NOT NULL THEN ' ' || POSTALCODE ELSE '' END) AS FULL_ADDRESS, ORGANIZE_URL, PHONE_HOME, PHONE_MOBILE, PHONE_OFFICE, CASE WHEN PHONE_FAX IS NOT NULL THEN 'Fax.' || PHONE_FAX ELSE '' END AS PHONE_FAX, CREATEUSER, CREATEDATE FROM CRM_ORGANIZATION O LEFT OUTER JOIN GENERAL_DESC CI ON CI.GDTYPE = 'CITY' AND O.CITY = CI.GDCODE LEFT OUTER JOIN GENERAL_DESC PO ON PO.GDTYPE = 'STATE' AND O.STATE = PO.GDCODE LEFT OUTER JOIN GENERAL_DESC CO ON CO.GDTYPE = 'COTRY' AND O.COUNTRY = CO.GDCODE WHERE {0} ORDER BY DESCR_LOC, DESCR_OTH, ADDRESS) M WHERE 1= 1 {1} ";
string sqlcntstr = String.Format("SELECT COUNT(*) FROM ( {0} )", string.Format(sqlstr, VisibleStr, strfilter));
string sqlSearchstr = string.Format(sqlstr, VisibleStr, strfilter) + " ORDER BY ORGANIZE_NAME ";
string sqlTags = string.Format("SELECT O.TAG_ID ,O.A_ID ,O.ACTIVITY_CAT ,O.ACTIVITY_ID ,O.TAG_LABEL ,O.VISIBILE_TYPE ,O.VISIBILE_CD FROM CRM_ACTIVITIES_TAG O WHERE {0} AND ACTIVITY_CAT = 'ACCATORG' ", VisibleStr);
try
{
ret.result.totalRec = (int)crmlib.ExecuteScalar(sqlcntstr, paramList, trn);
ret.result.currRec = (int)curpage;
ret.result.maxRec = (int)maxRec;
if (ret.result.totalRec > 0)
{
var dt = crmlib.DoQuery(sqlSearchstr, paramList, trn, (int)curpage, (int)maxRec);
ret.result.MOrganizationList = (List <MOrganization>)dt.GetDTOs <MOrganization>();
var dt2 = crmlib.DoQuery(sqlTags, null, trn, 0, 99999999);
ret.result.TagList = (List <CrmActivitiesTag>)dt2.GetDTOs <CrmActivitiesTag>();
foreach (MOrganization d in ret.result.MOrganizationList)
{
if (!string.IsNullOrEmpty(d.PhoneOffice))
{
d.Phone = d.PhoneOffice;
}
if (!string.IsNullOrEmpty(d.PhoneMobile))
{
if (!string.IsNullOrEmpty(d.Phone))
{
d.Phone += ", " + d.PhoneMobile + " ";
}
else
{
d.Phone = d.PhoneMobile + " ";
}
}
if (!string.IsNullOrEmpty(d.PhoneFax))
{
d.Phone += d.PhoneFax;
}
if (ret.result.TagList != null)
{
var tmp = (from t in ret.result.TagList where t.ActivityId == d.OrganizeId select t).ToList <CrmActivitiesTag>();
string xx = string.Empty;
if (tmp != null && tmp.Count > 0)
{
foreach (CrmActivitiesTag tag in tmp)
{
xx += "#" + tag.TagLabel + " ";
}
d.tagstr = xx;
}
}
}
}
else
{
var dt2 = crmlib.DoQuery(sqlTags, null, trn, 0, 99999999);
ret.result.TagList = (List <CrmActivitiesTag>)dt2.GetDTOs <CrmActivitiesTag>();
ret.MsgText = CRMMessageEnum.MessageEnum.MessageDataResponse.DataDoesNotExisits.ToString();
}
ret.IsComplete = true;
}
catch (Exception ex)
{
ret.IsComplete = false;
ret.MsgText = ex.Message.ToString();
throw ex;
}
ret.IsComplete = true;
return(ret);
}
