//Public Function GetFactoryByID(id As Integer) As DTO.PgFactory
// Dim ret As New DTO.PgFactory
// Dim sqlStr As String = "SELECT * FROM PG_FACTORY WHERE FACTORY_ID = :FACTORYID"
// Dim param As New Dictionary(Of String, Object)
// param.Add("FACTORYID", id)
// Dim dt = DoQuery(sqlStr, Nothing, Nothing, 0, 1)
// If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
// Dim tmp = dt.GetDTOs(Of DTO.PgFactory)()
// ret = tmp.FirstOrDefault
// End If
// Return ret
//End Function
public GuResult <List <MSearchContacts> > ContactsSearch(string txtStr, string filter, string user, string[] tagstr)
{
GuResult <List <MSearchContacts> > ret = new GuResult <List <MSearchContacts> >();
StringBuilder sb = new StringBuilder();
IDbCommand cmd1 = null;
sb.Append("SELECT C.CONTACT_ID, C.NAME_LOC, C.NAME_OTH, C.ORGANIZE_ID, O.DESCR_LOC, O.DESCR_OTH FROM CRM_CONTACTS C LEFT OUTER JOIN CRM_ORGANIZATION O ON C.ORGANIZE_ID = O.ORGANIZE_ID LEFT OUTER JOIN CRM_ACTIVITIES_TAG T ON T.ACTIVITY_CAT = 'ACCATCONT' AND T.A_ID = C.CONTACT_ID");
try
{
Database database = CRM_Controller.GetDB();
using (IDbConnection conn = database.CreateOpenConnection())
{
cmd1.Connection = conn;
cmd1.CommandText = sb.ToString();
cmd1.Parameters.Clear();
}
}
catch (Exception ex)
{
ret.result = null;
ret.IsComplete = false;
ret.MsgText = ex.Message;
throw ex;
}
return(ret);
}
public GuResult <string> DeleteContacts(MCrmContacts Obj, string user, string flag)
{
GuResult <string> ret = new GuResult <string>();
StringBuilder sb = new StringBuilder();
IDbCommand cmd1 = null;
sb.Append("UPDATE CRM_CONTACTS SET CONTACT_ST = 'I',MODIFYUSER = :USER, MODIFYDATE = :MDATE,PROGRAMCODE = :PROGRAMCODE WHERE CONTACT_ID = :CONID");
try
{
Database database = CRM_Controller.GetDB();
using (IDbConnection conn = database.CreateOpenConnection())
{
cmd1.Connection = conn;
cmd1.CommandText = sb.ToString();
cmd1.Parameters.Clear();
}
}
catch (Exception ex)
{
ret.result = null;
ret.IsComplete = false;
ret.MsgText = ex.Message;
throw ex;
}
return(ret);
}
protected virtual void Dispose(bool disposing)
{
if (!this.disposedValue)
{
if (disposing)
{
_instant = null;
}
}
this.disposedValue = true;
}
private void InsertContacts(MCrmContacts Obj, string user, ref GuResult <string> ret)
{
StringBuilder sb = new StringBuilder();
sb.Append("select SEQ_CRM_CONTACTS.nextval from dual");
IDbCommand cmd1 = null;
IDbCommand cmd2 = null;
IDbCommand cmd3 = null;
IDbCommand cmd4 = null;
IDbCommand cmd5 = null;
IDbCommand cmd6 = null;
IDbCommand cmdSeq = null;
IDbTransaction trn = null;
try
{
Database database = CRM_Controller.GetDB();
using (IDbConnection conn = database.CreateOpenConnection())
{
trn = conn.BeginTransaction();
//Get Seq. for CONTACT_ID
cmdSeq = conn.CreateCommand();
cmdSeq.CommandText = sb.ToString();
object tmp = cmdSeq.ExecuteScalar();
if (!(tmp is DBNull))
{
int id;
id = Convert.ToInt32(tmp);
Obj.ContactId = id;
}
//Get Seq. for CONTACT_ID
//Insert CRM_CONTACTS
Obj.Createuser = user;
Obj.Createdate = DateTime.Now;
cmd1 = Obj.InsertCommand(conn);
cmd1.Transaction = trn;
cmd1.ExecuteNonQuery();
//Insert CRM_CONTACTS_Ani
if (Obj.ConAnniversarylst != null && Obj.ConAnniversarylst.Count > 0)
{
foreach (CrmContactsAnniversary a in Obj.ConAnniversarylst)
{
a.ContactId = Obj.ContactId;
a.Createuser = user;
a.Createdate = DateTime.Now;
cmd2 = a.InsertCommand(conn);
cmd2.Transaction = trn;
cmd2.ExecuteNonQuery();
}
}
//Insert CRM_CONTACTS_Link
if (Obj.ConLinklst != null && Obj.ConLinklst.Count > 0)
{
foreach (CrmContactsLink b in Obj.ConLinklst)
{
b.ContactId = Obj.ContactId;
b.Createuser = user;
b.Createdate = DateTime.Now;
cmd3 = b.InsertCommand(conn);
cmd3.Transaction = trn;
cmd3.ExecuteNonQuery();
}
}
//Insert CRM_CONTACTS_Relate
if (Obj.ConRelatelst != null && Obj.ConRelatelst.Count > 0)
{
foreach (CrmContactsRelative c in Obj.ConRelatelst)
{
c.ContactId = Obj.ContactId;
c.Createuser = user;
c.Createdate = DateTime.Now;
cmd4 = c.InsertCommand(conn);
cmd4.Transaction = trn;
cmd4.ExecuteNonQuery();
}
}
//Insert CRM_CONTACTS_Social
if (Obj.ConSociallst != null && Obj.ConSociallst.Count > 0)
{
foreach (CrmContactsSocial d in Obj.ConSociallst)
{
d.ContactId = Obj.ContactId;
d.Createuser = user;
d.Createdate = DateTime.Now;
cmd5 = d.InsertCommand(conn);
cmd5.Transaction = trn;
cmd5.ExecuteNonQuery();
}
}
//Insert CRM_CONTACTS Visibility
if (Obj.ConVisiblelst != null && Obj.ConVisiblelst.Count > 0)
{
foreach (CrmContactsVisibility e in Obj.ConVisiblelst)
{
e.ContactId = Obj.ContactId;
e.Createuser = user;
e.Createdate = DateTime.Now;
cmd6 = e.InsertCommand(conn);
cmd6.Transaction = trn;
cmd6.ExecuteNonQuery();
}
}
trn.Commit();
cmd1.Dispose();
cmd2.Dispose();
cmd3.Dispose();
cmd4.Dispose();
cmd5.Dispose();
cmd6.Dispose();
conn.Close();
ret.result = CRMMessageEnum.MessageEnum.MessageDataResponse.SaveCompleted.ToString();
}
}
catch (Exception ex)
{
if (trn != null)
{
trn.Rollback();
}
ret.result = null;
ret.IsComplete = false;
ret.MsgText = ex.Message;
throw ex;
}
}
private void UpdateContacts(MCrmContacts Obj, string user, ref GuResult <string> ret)
{
StringBuilder sb = new StringBuilder();
sb.Append("select SEQ_CRM_CONTACTS.nextval from dual");
IDbCommand cmd1 = null;
IDbCommand cmd2 = null;
IDbCommand cmd3 = null;
IDbCommand cmd4 = null;
IDbCommand cmd5 = null;
IDbCommand cmd6 = null;
IDbTransaction trn = null;
try
{
Database database = CRM_Controller.GetDB();
using (IDbConnection conn = database.CreateOpenConnection())
{
trn = conn.BeginTransaction();
//Update CRM_CONTACTS
Obj.Modifyuser = user;
Obj.Modifydate = DateTime.Now;
cmd1 = Obj.UpdateCommand(conn, "Createuser,Createdate");
cmd1.Transaction = trn;
cmd1.ExecuteNonQuery();
//Update CRM_CONTACTS_Ani
if (Obj.ConAnniversarylst != null && Obj.ConAnniversarylst.Count > 0)
{
foreach (CrmContactsAnniversary a in Obj.ConAnniversarylst)
{
if (a.EntityState == SsCommon.EntityStateLocal.Added)
{
a.Createuser = user;
a.Createdate = DateTime.Now;
cmd2 = a.InsertCommand(conn);
}
else if (a.EntityState == SsCommon.EntityStateLocal.Modified)
{
a.Modifyuser = user;
a.Modifydate = DateTime.Now;
cmd2 = a.UpdateCommand(conn, "Createuser,Createdate");
}
cmd2.Transaction = trn;
cmd2.ExecuteNonQuery();
}
}
//Insert CRM_CONTACTS_Link
if (Obj.ConLinklst != null && Obj.ConLinklst.Count > 0)
{
foreach (CrmContactsLink b in Obj.ConLinklst)
{
if (b.EntityState == SsCommon.EntityStateLocal.Added)
{
b.Createuser = user;
b.Createdate = DateTime.Now;
cmd3 = b.InsertCommand(conn);
}
else if (b.EntityState == SsCommon.EntityStateLocal.Modified)
{
b.Modifyuser = user;
b.Modifydate = DateTime.Now;
cmd3 = b.UpdateCommand(conn, "Createuser,Createdate");
}
cmd3.Transaction = trn;
cmd3.ExecuteNonQuery();
}
}
//Insert CRM_CONTACTS_Relate
if (Obj.ConRelatelst != null && Obj.ConRelatelst.Count > 0)
{
foreach (CrmContactsRelative c in Obj.ConRelatelst)
{
if (c.EntityState == SsCommon.EntityStateLocal.Added)
{
c.Createuser = user;
c.Createdate = DateTime.Now;
cmd4 = c.InsertCommand(conn);
}
else if (c.EntityState == SsCommon.EntityStateLocal.Modified)
{
c.Modifyuser = user;
c.Modifydate = DateTime.Now;
cmd4 = c.UpdateCommand(conn, "Createuser,Createdate");
}
cmd4.Transaction = trn;
cmd4.ExecuteNonQuery();
}
}
//Insert CRM_CONTACTS_Social
if (Obj.ConSociallst != null && Obj.ConSociallst.Count > 0)
{
foreach (CrmContactsSocial d in Obj.ConSociallst)
{
if (d.EntityState == SsCommon.EntityStateLocal.Added)
{
d.Createuser = user;
d.Createdate = DateTime.Now;
cmd5 = d.InsertCommand(conn);
}
else if (d.EntityState == SsCommon.EntityStateLocal.Modified)
{
d.Modifyuser = user;
d.Modifydate = DateTime.Now;
cmd5 = d.UpdateCommand(conn, "Createuser,Createdate");
}
cmd5.Transaction = trn;
cmd5.ExecuteNonQuery();
}
}
//Insert CRM_CONTACTS Visibility
if (Obj.ConVisiblelst != null && Obj.ConVisiblelst.Count > 0)
{
foreach (CrmContactsVisibility e in Obj.ConVisiblelst)
{
if (e.EntityState == SsCommon.EntityStateLocal.Added)
{
e.Createuser = user;
e.Createdate = DateTime.Now;
cmd6 = e.InsertCommand(conn);
}
else if (e.EntityState == SsCommon.EntityStateLocal.Modified)
{
e.Modifyuser = user;
e.Modifydate = DateTime.Now;
cmd6 = e.UpdateCommand(conn, "Createuser,Createdate");
}
cmd6.Transaction = trn;
cmd6.ExecuteNonQuery();
}
}
trn.Commit();
cmd1.Dispose();
cmd2.Dispose();
cmd3.Dispose();
cmd4.Dispose();
cmd5.Dispose();
cmd6.Dispose();
conn.Close();
ret.result = CRMMessageEnum.MessageEnum.MessageDataResponse.SaveCompleted.ToString();
}
}
catch (Exception ex)
{
if (trn != null)
{
trn.Rollback();
}
ret.result = null;
ret.IsComplete = false;
ret.MsgText = ex.Message;
throw ex;
}
}
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);
}
