myNewPaste

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MysqlBasicClass;
using System.Collections;
namespace Communication.Sockets.Core
{

    public class DatabaseUtils
    {
        public string tableName1 = "PSXCONF_USERS";
        public string tableName2 = "PSXCONF_MEMBER";
        public string tableName3 = "PSXCONF_DEPARTMENT";
        public string tableName4 = "PSXCONF_PLAN";
        public string tableName5 = "PSXCONF_PLAN_MEMBER";

        ManagerTables mysqlData = new ManagerTables();

        public void connectMysql(string ip)
        {
            mysqlData.connectMysql(ip);
        }
        public DatabaseUtils()
        {

        }

        public string process(string message)
        {
            string operation = TextFollowing(message, "OPERATION:");

            if(operation.Equals("ADDOPERATOR") || operation.Equals("ADDCONTACTMEMBER") || operation.Equals("ADDPLAN") || operation.Equals("ADDPLANMEMBER"))
                return Add(message);
            else if (operation.Equals("REMOVEOPERATOR") || operation.Equals("REMOVECONTACTMEMBER") || operation.Equals("REMOVEPLAN") || operation.Equals("REMOVEPLANMEMBER"))
                return Delete(message);
            //else if (operation.Equals("UPDATEOPERATOR") || operation.Equals("UPDATECONTACTMEMBER") || operation.Equals("UPDATEPLAN") || operation.Equals("UPDATEPLANMEMBER"))
            //    return Update(message);
            else if (operation.Equals("GETOPERATOR") || operation.Equals("GETCONTACT") || operation.Equals("GETALLPLANS"))
                return Get(message);

            return null;
        }


        public string Add(string message)
        {
            string tableName = TextFollowing(message, "TABLENAME:");
            string operation = TextFollowing(message, "OPERATION:");
            int ret = 0;
            if (operation.Equals("ADDOPERATOR"))
            {
                string id = TextFollowing(message, "ID:");
                string name = TextFollowing(message, "NAME:");
                string password = TextFollowing(message, "PASSWORD:");
                string operatorrole = TextFollowing(message, "OPERATORROLE:");

                operatorrole = "2";

                if (id.Equals("") || name.Equals("") || password.Equals("") || operatorrole.Equals(""))
                {
                    string retStr = "ADDOPERATORACK\r\n"
                                      + "STATUS:FAILED\r\n";
                    return retStr;
                }

                CONF_USERS tmpUser = new CONF_USERS();
                tmpUser.id = id;

                CONF_USERS[] tmpUserArr = mysqlData.GetConfUser(tmpUser);

                if(tmpUserArr.Length > 0)
                {
                    string retStr = "ADDOPERATORACK\r\n"
                                     + "STATUS:FAILED\r\n";
                    return retStr;
                }


                CONF_USERS confUser = new CONF_USERS();
                confUser.id = id;
                confUser.name = name;
                confUser.password = password;
                confUser.operatorrole = operatorrole;

                ret = mysqlData.AddConfUser(confUser);

                if(ret > 0)
                {
                    string retStr = "ADDOPERATORACK\r\n"
                                        + "STATUS:OK\r\n";
                    return retStr;
                }
                else
                {
                    string retStr = "ADDOPERATORACK\r\n"
                                      + "STATUS:FAILED\r\n";
                    return retStr;
                }

            }
            else if (operation.Equals("ADDCONTACTMEMBER"))
            {
                //string id = TextFollowing(message, "ID:");
                string membername = TextFollowing(message, "MEMBERNAME:");
                string membernumber = TextFollowing(message, "MEMBERNUMBER:");
                string membertypeid = TextFollowing(message, "MEMBERTYPEID:");
                membertypeid = "5";
                if (membername.Equals("") || membernumber.Equals("") || membertypeid.Equals("") || membernumber.Contains(';'))
                {
                    string retStr = "ADDCONTACTMEMBERACK\r\n"
                                      + "STATUS:FAILED\r\n";
                    return retStr;
                }

                CONF_MEMBER tmpConfMember = new CONF_MEMBER();
                tmpConfMember.membernumber = membernumber;

                CONF_MEMBER[] tmpConfMemberArr = mysqlData.GetConfMember(tmpConfMember);

                if (tmpConfMemberArr.Length > 0)
                {
                    string retStr = "ADDCONTACTMEMBERACK\r\n"
                                     + "STATUS:FAILED\r\n";
                    return retStr;
                }


                CONF_MEMBER confMember = new CONF_MEMBER();
                //confMember.id = id;
                confMember.membername = membername;
                confMember.membernumber = membernumber;
                confMember.membertypeid = membertypeid;
                confMember.treenodeid = "0";
                ret = mysqlData.AddConfMember(confMember);



                CONF_MEMBER[] confMemberArr = mysqlData.GetConfMember(confMember);


                if (confMemberArr.Length == 0)
                {
                    string retStr = "ADDCONTACTMEMBERACK\r\n"
                                + "STATUS:FAILED\r\n";

                    return retStr;
                }

                string id = confMemberArr[0].id;

                CONF_DEPARTMENT confDepartment = new CONF_DEPARTMENT();
                confDepartment.nodename = membername;
                confDepartment.nodetype = "1";
                confDepartment.memberid = id;
                confDepartment.pnode = "1";

                ret = mysqlData.AddConDepartment(confDepartment);

                if(ret > 0)
                {
                    string retStr = "ADDCONTACTMEMBERACK\r\n"
                                + "NUMBERS:" + confMemberArr[0].membernumber + "\r\n"
                                + "STATUS:OK\r\n";

                    return retStr;
                }
                else
                {
                    string retStr = "ADDCONTACTMEMBERACK\r\n"
                                + "STATUS:FAILED\r\n";

                    return retStr;
                }

            }
            else if (operation.Equals("ADDPLAN"))
            {
                string userid = TextFollowing(message, "USERID:");
               // string planid = TextFollowing(message, "PLANID:");
                string plangroupid = TextFollowing(message, "PLANGROUPID:");
                string planname = TextFollowing(message, "PLANNAME:");
                plangroupid = "1";
                if (userid.Equals("") || plangroupid.Equals("") || planname.Equals(""))
                {
                    string retStr = "ADDPLANACK\r\n"
                                      + "STATUS:FAILED\r\n";
                    return retStr;
                }
                CONF_PLAN_GROUP confPlanGroup = new CONF_PLAN_GROUP();
                confPlanGroup.id = "1";

                CONF_PLAN_GROUP[] confPlanGroupArr = mysqlData.getPlanGroup(confPlanGroup);

                if (confPlanGroupArr.Length == 0)
                {
                    string retStr = "ADDPLANACK\r\n"
                                     + "STATUS:FAILED\r\n";
                    return retStr;
                }



                CONF_PLAN tmpPlan = new CONF_PLAN();
                tmpPlan.planname = planname;
                tmpPlan.userid = userid;
                tmpPlan.plangroupid = plangroupid;
                CONF_PLAN[] tmpPlans = mysqlData.GetConfPlan(tmpPlan);

                if (tmpPlans.Length > 0)
                {
                    string retStr = "ADDPLANACK\r\n"
                                     + "STATUS:FAILED\r\n";
                    return retStr;
                }


                CONF_PLAN confPlan = new CONF_PLAN();
                confPlan.userid = userid;
               // confPlan.planid = planid;
                confPlan.plangroupid = plangroupid;
              //  confPlan.planname = "";

                CONF_PLAN[] planArr = mysqlData.GetConfPlan(confPlan);

                ret = 0;

                if (planArr.Length > 0)
                {
                    foreach(var plan in planArr)
                    {
                        if (plan.Equals(tmpPlan))
                            continue;

                        CONF_PLAN_MEMBER confPlanMember = new CONF_PLAN_MEMBER();
                        confPlanMember.planid = plan.id;

                        CONF_PLAN_MEMBER[] confPlanMemberArr = mysqlData.GetConfPlanMember(confPlanMember);

                        if (confPlanMemberArr.Length == 0)
                        {
                            ret = mysqlData.UpdateConfPlan2(tmpPlan, plan);
                            break;
                        }
                    }


                }

                if (ret > 0)
                {
                    string retStr = "ADDPLANACK\r\n"
                            + "STATUS:OK\r\n";

                    return retStr;
                }


                else
                {
                    string retStr = "ADDPLANACK\r\n"
                                + "STATUS:FAILED\r\n";

                    return retStr;
                }
            }
            else if (operation.Equals("ADDPLANMEMBER"))
            {
                string planname = TextFollowing(message, "PLANNAME:");
                string membersStr = TextFollowing(message, "MEMBERS:");
                string userid = TextFollowing(message, "USERID:");


                if (planname.Equals("") || membersStr.Equals(""))
                {
                    string retStr = "ADDPLANMEMBERACK\r\n"
                              + "STATUS:FAILED\r\n";

                    return retStr;
                }




                string[] membersArr = membersStr.Split(';');

                CONF_PLAN confPlan = new CONF_PLAN();
                confPlan.planname = planname;
                confPlan.userid = userid;

                CONF_PLAN[] confPlanArr = mysqlData.GetConfPlan(confPlan);

                if (confPlanArr.Length == 0)
                {
                    string retStr = "ADDPLANMEMBERACK\r\n"
                              + "STATUS:FAILED\r\n";

                    return retStr;
                }

                int flag = 0;

                foreach (var memberNumber in membersArr)
                {
                    CONF_MEMBER confMember = new CONF_MEMBER();
                    confMember.membernumber = memberNumber;

                    CONF_MEMBER[] confMemberArr = mysqlData.GetConfMember(confMember);


                    if (confMemberArr.Length == 0)
                    {
                        flag = 1;
                        continue;
                    }


                      string memberid = confMemberArr[0].id;
                      CONF_PLAN_MEMBER confPlanMember = new CONF_PLAN_MEMBER();
                      confPlanMember.planid = confPlanArr[0].id;
                      confPlanMember.memberid = memberid;
                      CONF_PLAN_MEMBER[] confPlanMemberArr = mysqlData.GetConfPlanMember(confPlanMember);

                      if (confPlanMemberArr.Length > 0)
                      {
                          flag = 1;
                          continue;

                       }

                    //if (confPlanMemberArr.Length > 0)
                    //{
                    //    string retStr = "ADDPLANMEMBERACK\r\n"
                    //           + "STATUS:FAILED\r\n";

                    //    return retStr;
                    //}




                    ret = mysqlData.AddConfPlanMember(confPlanMember);

                    //if(ret <= 0)
                    //{
                    //    string retStr = "ADDPLANMEMBERACK\r\n"
                    //             + "STATUS:FAILED\r\n";

                    //    return retStr;
                    //}
                }

                if (flag == 1)
                {
                    string retString = "ADDPLANMEMBERACK\r\n"
                              + "STATUS:FAILED\r\n";

                    return retString;

                }
                else
                {
                    string retString = "ADDPLANMEMBERACK\r\n"
                           + "STATUS:OK\r\n";

                    return retString;
                }


            }

            return null;
        }

        public string Delete(string message)
        {
            int ret = 0;
            string tableName = TextFollowing(message, "TABLENAME:");
            string operation = TextFollowing(message, "OPERATION:");



            if (operation.Equals("REMOVEOPERATOR"))
            {
                string id = TextFollowing(message, "ID:");

                if (id.Equals(""))
                {
                    string retStr = "REMOVEOPERATORACK\r\n"
                        + "STATUS:FAILED\r\n";

                    return retStr;
                }

                CONF_USERS confUser = new CONF_USERS();
                confUser.id = id;



                ret = mysqlData.DeleteConfUser(confUser);

                if(ret > 0)
                {
                    string retStr = "REMOVEOPERATORACK\r\n"
                              + "STATUS:OK\r\n";

                    return retStr;
                }
                else
                {
                    string retStr = "REMOVEOPERATORACK\r\n"
                              + "STATUS:FAILED\r\n";

                    return retStr;
                }


            }
            else if (operation.Equals("REMOVECONTACTMEMBER"))
            {
                string membernumber = TextFollowing(message, "MEMBERNUMBER:");


                if (membernumber.Equals("") || membernumber.Contains(';'))
                {
                    string retStr = "REMOVECONTACTMEMBERACK\r\n"
                        + "STATUS:FAILED\r\n";

                    return retStr;
                }

                CONF_MEMBER confMember = new CONF_MEMBER();
                confMember.membernumber = membernumber;

                CONF_MEMBER[] confMemberArr = mysqlData.GetConfMember(confMember);


                if (confMemberArr.Length == 0)
                {
                    string retStr = "REMOVECONTACTMEMBERACK\r\n"
                              + "STATUS:FAILED\r\n";

                    return retStr;
                }


                string id = confMemberArr[0].id;

                ret = mysqlData.DeleteConfMember(confMember);


                if (ret <= 0)
                {
                    string retStr = "REMOVECONTACTMEMBERACK\r\n"
                              + "NUMBERS:" + confMemberArr[0].membernumber + "\r\n"
                              + "STATUS:FAILED\r\n";

                    return retStr;
                }

                CONF_PLAN_MEMBER confPlanMember = new CONF_PLAN_MEMBER();
                confPlanMember.memberid = id;

                CONF_PLAN_MEMBER[] confPlanMemberArr = mysqlData.GetConfPlanMember(confPlanMember);

                if (confPlanMemberArr.Length > 0)
                {
                    mysqlData.DeleteConfPlanMember(confPlanMember);
                }


                CONF_DEPARTMENT confDepartment = new CONF_DEPARTMENT();
                confDepartment.memberid = id;

                ret = mysqlData.DeleteConfDepartment(confDepartment);


                if(ret > 0)
                {
                    string retStr = "REMOVECONTACTMEMBERACK\r\n"
                              + "MEMBER:" + membernumber + "\r\n"
                              + "STATUS:OK\r\n";

                    return retStr;
                }
                else
                {
                    string retStr = "REMOVECONTACTMEMBERACK\r\n"
                              + "STATUS:FAILED\r\n";

                    return retStr;
                }

            }
            else if (operation.Equals("REMOVEPLAN"))
            {
                string userid = TextFollowing(message, "USERID:");
                string planname = TextFollowing(message, "PLANNAME:");
                string plangroupid = "1";
                if (planname.Equals(""))
                {
                    string retStr = "REMOVEPLANACK\r\n"
                        + "STATUS:FAILED\r\n";

                    return retStr;
                }



                CONF_PLAN confPlan = new CONF_PLAN();
                confPlan.planname = planname;
                confPlan.userid = userid;
                confPlan.plangroupid = plangroupid;
                CONF_PLAN[] confPlanArr = mysqlData.GetConfPlan(confPlan);

                if (confPlanArr.Length == 0)
                {
                    string retStr = "REMOVEPLANACK\r\n"
                      + "STATUS:FAILED\r\n";

                    return retStr;
                }



                CONF_PLAN_MEMBER confPlanMember = new CONF_PLAN_MEMBER();
                confPlanMember.planid = confPlanArr[0].id;

                ret = mysqlData.DeleteConfPlanMember(confPlanMember);

                CONF_PLAN newPlan = new CONF_PLAN();
                newPlan.planname = "";
                newPlan.userid = userid;
                newPlan.plangroupid = plangroupid;
                ret = mysqlData.UpdateConfPlan(newPlan, confPlan);

                if(ret > 0)
                {
                    string retStr = "REMOVEPLANACK\r\n"
                          + "PLANNAME:" + planname + "\r\n"
                          + "STATUS:OK\r\n";

                    return retStr;
                }
                else
                {
                    string retStr = "REMOVEPLANACK\r\n"
                       + "STATUS:FAILED\r\n";

                    return retStr;
                }

            }
            else if (operation.Equals("REMOVEPLANMEMBER"))
            {
              //  CONF_PLAN_MEMBER confPlanMember = new CONF_PLAN_MEMBER();


                string retStr = "";
                string membersStr = TextFollowing(message, "MEMBERS:");
                string planname = TextFollowing(message, "PLANNAME:");
                string userid = TextFollowing(message, "USERID:");
                string members = "";


                if (membersStr.Equals("") || planname.Equals(""))
                {
                    retStr = "REMOVEPLANMEMBERACK\r\n"

                        + "STATUS:FAILED\r\n";

                    return retStr;
                }

                string[] membersArr = membersStr.Split(';');


                CONF_PLAN confPlan = new CONF_PLAN();
                confPlan.planname = planname;
                confPlan.userid = userid;

                CONF_PLAN[] confPlanArr = mysqlData.GetConfPlan(confPlan);

                if (confPlanArr.Length == 0)
                {
                    retStr = "REMOVEPLANMEMBERACK\r\n"
                              + "STATUS:FAILED\r\n";

                    return retStr;
                }

                int flag = 0;

                foreach (var member in membersArr)
                {
                    CONF_MEMBER confMember = new CONF_MEMBER();
                    confMember.membernumber = member;

                    CONF_MEMBER[] confMemberArr = mysqlData.GetConfMember(confMember);

                        if (confMemberArr.Length == 0)
                            {
                                flag = 1;
                                continue;
                            }

                    CONF_PLAN_MEMBER confPlanMember = new CONF_PLAN_MEMBER();
                    confPlanMember.memberid = confMemberArr[0].id;
                    confPlanMember.planid = confPlanArr[0].id;



                    ret = mysqlData.DeleteConfPlanMember(confPlanMember);

                    if (ret <= 0)
                    {
                        flag = 1;
                        continue;
                    }

                    if(!string.IsNullOrEmpty(members))
                        members += ";" + member;
                }


                if (flag == 0)
                {
                    retStr = "REMOVEPLANMEMBERACK\r\n"
                    + "MEMBERS:" + members + "\r\n"
                    + "STATUS:OK\r\n";
                }
                else
                {
                    retStr = "REMOVEPLANMEMBERACK\r\n"
                    + "MEMBERS:" + members + "\r\n"
                    + "STATUS:FAILED\r\n";
                }

                return retStr;
            }

            return null;

        }



        public string Get(string message)
        {

            string tableName = TextFollowing(message, "TABLENAME:");
            string operation = TextFollowing(message, "OPERATION:");

            if (operation.Equals("GETOPERATOR"))
            {
            }
            else if (operation.Equals("GETCONTACT"))
            {
                CONF_MEMBER confMember = new CONF_MEMBER();
                CONF_MEMBER[] confMemberArr = mysqlData.GetAllConfMembers(confMember);




                string retStr = "";

                string members = "";
                foreach (CONF_MEMBER tmpConfMember in confMemberArr)
                {
                    string str = "";
                    if (!string.IsNullOrEmpty(members))
                        str += ";";
                    str += tmpConfMember.membernumber;
                    members += str;
                }


                retStr = "GETCONTACTACK\r\n"

                            + "MEMBERS:" + members + "\r\n"
                            + "STATUS:OK\r\n";

                return retStr;



            }
            else if (operation.Equals("GETALLPLANS"))
            {
                string userid = TextFollowing(message, "USERID:");

                CONF_PLAN confPlan = new CONF_PLAN();

                string retStr = "";
                confPlan.userid = userid;

                CONF_PLAN[] confPlanArr = mysqlData.GetConfPlan(confPlan);


                string planStr = "";
                int i = 1;

                foreach (var plan in confPlanArr)
                {
                    string planid = plan.id;
                    string planname = plan.planname;
                    CONF_PLAN_MEMBER confPlanMember = new CONF_PLAN_MEMBER();
                    confPlanMember.planid = planid;

                    CONF_PLAN_MEMBER[] confPlanMemberArr = mysqlData.GetConfPlanMember(confPlanMember);

                    if (confPlanMemberArr.Length == 0)
                        continue;

                    string members = "";
                    foreach (var planmember in confPlanMemberArr)
                    {
                        CONF_MEMBER confMember = new CONF_MEMBER();
                        confMember.id = planmember.memberid;
                        CONF_MEMBER[] confMemberArr = mysqlData.GetConfMember(confMember);

                        string membernumber = confMemberArr[0].membernumber;

                        string str = "";
                        if (!string.IsNullOrEmpty(members))
                            str += ";";
                        str += membernumber;
                        members += str;
                    }

                   planStr += "PLAN" + i.ToString() + ":" + members + "\r\n";
                  // planStr += "PLANID" + i.ToString() + ":" + planid + "\r\n";
                   planStr += "PLANNAME" + i.ToString() + ":"+ planname + "\r\n";
                   i++;
                    //planStr += "PLAN" + planid + ":" + members + "\r\n";
                    //planStr += "PLANNAME:" + planname + "\r\n";
                }

                retStr = "GETALLPLANSACK\r\n"

                                     + planStr
                                     + "STATUS:OK\r\n";

                return retStr;
            }
            else if (operation.Equals("GETPLANMEMBER"))
            {

            }
            return null;
        }

        public string TextFollowing(string searchTxt, string value)
        {
            string separateString = "\r\n";
            if (!String.IsNullOrEmpty(searchTxt) && !String.IsNullOrEmpty(value))
            {
                int index = searchTxt.IndexOf(value);
                if (-1 < index)
                {
                    int separateIndex = searchTxt.IndexOf(separateString, index);
                    if (-1 < separateIndex)
                    {
                        int start = index + value.Length;
                        return searchTxt.Substring(start, separateIndex - start);
                    }
                }
            }
            return null;
        }
    }
}