Example #1
0
        //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);
        }
Example #2
0
        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);
        }
Example #3
0
 protected virtual void Dispose(bool disposing)
 {
     if (!this.disposedValue)
     {
         if (disposing)
         {
             _instant = null;
         }
     }
     this.disposedValue = true;
 }
Example #4
0
        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;
            }
        }
Example #5
0
        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;
            }
        }
Example #6
0
        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);
        }