Exemple #1
0
    public static int deleteRecord(Magazine obj)
    {
        string strSQL = "delete from magazine where magazineId='" + obj.magazineId + "'";
        int    iRet   = DBOper.execNonQueryBySQLText(strSQL);

        return(iRet);
    }
Exemple #2
0
    public static int insertshenqingRecord(yuyuehuiyi obj)
    {
        string strSQL = "insert into shenqinghuiyi(huiyiname,shijian,shenhe) values('" + obj.huiyiname + "','" + obj.shijian + "','" + "0" + "')";
        int    iRet   = DBOper.execNonQueryBySQLText(strSQL);

        return(iRet);
    }
Exemple #3
0
    //public static int delRecord(yuyuehuiyi obj)
    //{
    //    string strSQL = "delete from shenqinghuiyi where huiyiid='" + obj.huiyiid + "'";
    //    int iRet = DBOper.execNonQueryBySQLText(strSQL);
    //    return iRet;
    //}
    public static int updateshenheRecord(yuyuehuiyi obj)
    {
        string strSQL = "update shenqinghuiyi set shenhe='" + "1" + "' where huiyiname='" + obj.huiyiname + "'";
        int    iRet   = DBOper.execNonQueryBySQLText(strSQL);

        return(iRet);
    }
Exemple #4
0
    public static int insertRecord(UpImage obj)
    {
        string strSQL = "insert into upImage(imageId,savepath,magazineId,sequence,imageDesc,uptime,articleId) values('" + obj.imageId + "','" + obj.savepath + "','" + obj.magazineId + "'," + obj.sequence + ",'" + obj.imageDesc + "','" + obj.uptime + "','" + obj.articleId + "')";
        int    iRet   = DBOper.execNonQueryBySQLText(strSQL);

        return(iRet);
    }
Exemple #5
0
    public static int updateRecord(UpImage obj)
    {
        string strSQL = "update upImage set savepath='" + obj.savepath + "',magazineId='" + obj.magazineId + "',sequence=" + obj.sequence + ",imageDesc='" + obj.imageDesc + "',uptime='" + obj.uptime + "',articleId='" + obj.articleId + "' where imageId='" + obj.imageId + "'";
        int    iRet   = DBOper.execNonQueryBySQLText(strSQL);

        return(iRet);
    }
Exemple #6
0
    public static int deleteRecord(Users obj)
    {
        string strSQL = "delete from tbusers where userid='" + obj.userid + "'";
        int    iRet   = DBOper.execNonQueryBySQLText(strSQL);

        return(iRet);
    }
    public string SaveDBWithTime(string time)
    {
        LoadJson load      = new LoadJson(this._name, time, "");
        string   sRes      = load.LoadJsonLikeTime();
        string   DBcommand = "";

        if (sRes != "[]")          //已经有数据
        {
            DBcommand =
                @"UPDATE [EPInfoSystem].[dbo].[JsonTable]
					SET 
						
						[内容] ='"                         + _jsonobj + @"'
					where [时间]= '"                     + time + "' AND [表名]='" + this._name + "'";
        }
        else
        {
            DBcommand =
                @"INSERT INTO [EPInfoSystem].[dbo].[JsonTable]
            ([表名]
            ,[时间]
           ,[类型]
           ,[内容])
     VALUES
           ('" + _name + "','" + time + "','json','" + _jsonobj + "')";
        }
        string ret;
        string source = ConfigurationManager.ConnectionStrings["EPInfoSys"].ConnectionString.ToString();

        DBOper DBop = new DBOper(source, DBcommand);

        ret = DBop.ReturnRows();
        return("数据库录入成功,影响" + ret);
    }
Exemple #8
0
    public static int deleteRecord(roomBook obj)
    {
        string strSQL = "delete from roombook where username='******' and roomid = '" + obj.roomid + "' and begintime = '" + obj.begintime + "' and endtime = '" + obj.endtime + "'";
        int    iRet   = DBOper.execNonQueryBySQLText(strSQL);

        return(iRet);
    }
Exemple #9
0
    public static int insertRecord(Admin obj)
    {
        string strSQL = "insert into admin(adminname,adminkey,adminphone) values('" + obj.adminname + "','" + obj.adminkey + "','" + obj.adminphone + "'";
        int    iRet   = DBOper.execNonQueryBySQLText(strSQL);

        return(iRet);
    }
Exemple #10
0
    public static DataTable selectDataTable1()
    {
        string    strSQL = "select* from roombook";
        DataTable dt     = DBOper.execQueryBySQLText(strSQL);

        return(dt);
    }
Exemple #11
0
    public static int insertRecord(roomBook obj)
    {
        string strSQL = "insert into roombook(username,roomid,begintime,endtime) values('" + obj.username + "','" + obj.roomid + "','" + obj.begintime + "','" + obj.endtime + "')";
        int    iRet   = DBOper.execNonQueryBySQLText(strSQL);

        return(iRet);
    }
 protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
 {
     if (DropDownList1.SelectedValue != "" && DropDownList2.SelectedValue == "")
     {
         DropDownList2.Items.Clear();
         ListItem item = new ListItem();
         item.Text  = "请选择";
         item.Value = "";
         DropDownList2.Items.Insert(0, item);
         DataTable dt = DBOper.execQueryBySQLText("Select * from article where magazineId='" + DropDownList1.SelectedValue + "'");
         DropDownList2.DataSource     = dt;
         DropDownList2.DataTextField  = "articleName";
         DropDownList2.DataValueField = "articleName";
         DropDownList2.DataBind();
     }
     //else
     //{
     //    DropDownList2.Items.Clear();
     //    ListItem item = new ListItem();
     //    item.Text = "请选择";
     //    item.Value = "";
     //    DropDownList2.Items.Insert(0, item);
     //    DataTable dt = DBOper.execQueryBySQLText("Select * from article");
     //    DropDownList2.DataSource = dt;
     //    DropDownList2.DataTextField = "articleName";
     //    DropDownList2.DataValueField = "articleName";
     //    DropDownList2.DataBind();
     //}
 }
Exemple #13
0
        public static bool InsertCheckOutRoomInfo(CheckOutRoom checkOutRoomInfo)
        {
            string sqlInsert         = @"insert into checkout values(@outId,@inId,@outTime,@roomId,@clientName,@inTime,@price,@foregift,@account,@note,@oper)";
            string sqlUpdateRegister = "update checkin set delMark=1 where inId=@inId";
            string sqlUpdateRoom     = "update room set inPerson=0 where roomId=@roomId";

            MySqlParameter p1  = new MySqlParameter("@outId", checkOutRoomInfo.OutId);
            MySqlParameter p2  = new MySqlParameter("@inId", checkOutRoomInfo.InId);
            MySqlParameter p3  = new MySqlParameter("@roomId", checkOutRoomInfo.RoomId);
            MySqlParameter p4  = new MySqlParameter("@price", checkOutRoomInfo.Price.ToString());
            MySqlParameter p5  = new MySqlParameter("@foregift", checkOutRoomInfo.Foregift.ToString());
            MySqlParameter p6  = new MySqlParameter("@total", checkOutRoomInfo.Total.ToString());
            MySqlParameter p7  = new MySqlParameter("@account", checkOutRoomInfo.Account.ToString());
            MySqlParameter p8  = new MySqlParameter("@inTime", checkOutRoomInfo.InTime);
            MySqlParameter p9  = new MySqlParameter("@outTime", checkOutRoomInfo.OutTime);
            MySqlParameter p10 = new MySqlParameter("@clientName", checkOutRoomInfo.ClietnName);
            MySqlParameter p11 = new MySqlParameter("@oper", checkOutRoomInfo.Oper);
            MySqlParameter p12 = new MySqlParameter("@note", checkOutRoomInfo.Note);

            MySqlParameter[] paramArray = new MySqlParameter[] { p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12 };

            MySqlParameter n1 = new MySqlParameter("@inId", checkOutRoomInfo.InId);
            MySqlParameter n2 = new MySqlParameter("@roomId", checkOutRoomInfo.RoomId);

            if (DBOper.ExecuteCommand(sqlInsert, paramArray) == 1 &&
                DBOper.ExecuteCommand(sqlUpdateRegister, n1) == 1 &&
                DBOper.ExecuteCommand(sqlUpdateRoom, n2) == 1)
            {
                return(true);
            }
            else
            {
                return(false);
            }
        }
Exemple #14
0
        public static DataTable GetInID(string roomId)
        {
            string    sql = @"select inId from checkin where delMark=0 and roomId=" + roomId;
            DataTable dt  = DBOper.GetDataTable(sql);

            return(dt);
        }
Exemple #15
0
    public static int updateRecord(Magazine obj)
    {
        string strSQL = "update magazine set magazineName='" + obj.magazineName + "',mdescription='" + obj.mdescription + "',magazineKey='" + obj.magazineKey + "' where magazineId='" + obj.magazineId + "'";
        int    iRet   = DBOper.execNonQueryBySQLText(strSQL);

        return(iRet);
    }
Exemple #16
0
    public static int deleteRecord(Admin obj)
    {
        string strSQL = "delete from admin where adminid='" + obj.adminname + "'";
        int    iRet   = DBOper.execNonQueryBySQLText(strSQL);

        return(iRet);
    }
Exemple #17
0
    public static int insertRecord(Users obj)
    {
        string strSQL = "insert into tbusers(userid,username,userpwd) values('" + obj.userid + "','" + obj.username + "','" + obj.userpwd + "'";
        int    iRet   = DBOper.execNonQueryBySQLText(strSQL);

        return(iRet);
    }
        public static DataTable GetNullBedNum()
        {
            string    sql       = @"SELECT COUNT(*) FROM ROOM WHERE PATIENTNAME IS NULL";
            DataTable dataTable = DBOper.GetDataTable(sql);

            return(dataTable);
        }//得到空置的床位数量
Exemple #19
0
    public static int updateRecord(Users obj)
    {
        string strSQL = "update tbusers set username='******' where userid='" + obj.userid + "'";
        int    iRet   = DBOper.execNonQueryBySQLText(strSQL);

        return(iRet);
    }
        }//得到空置的床位数量

        public static DataTable GetAllBedNum()
        {
            string    sql       = @"SELECT COUNT(*) FROM ROOM";
            DataTable dataTable = DBOper.GetDataTable(sql);

            return(dataTable);
        }
Exemple #21
0
    public static string ReadAccess(string order, string classify)
    {
        string ret;
        string DBcommand;
        string source = ConfigurationManager.ConnectionStrings["EPInfoSys"].ConnectionString.ToString();

        if (order == "-1")
        {
            DBcommand = @"SELECT TOP 1
									 [时间]   
									,[标题]
									,[内容]
									,[Id]
								FROM [EPInfoSystem].[dbo].[NewInfo]
								WHERE [分类]='"                                 + classify + @"' 
								order by [时间] desc"                                ;
        }
        else
        {
            DBcommand = @"SELECT 
									 [时间]   
									,[标题]
									,[内容]
									,[Id]
								FROM [EPInfoSystem].[dbo].[NewInfo]
								WHERE [Id]="                                 + order;
        }

        ret = DBOper.ReturnJson(source, DBcommand);
        return(ret);
    }
        //insert
        public static bool InsertIntoPatient(Patient p)
        {
            string sqlInsert = @"insert into patient values(
                @patientName,@age,@sex,@idCard_Num,@in_HospitalNum,@major_Doctor,
                @main_Symptom,@phone_Num,@in_Date
            )";

            MySqlParameter[] paramArray = new MySqlParameter[] {
                new MySqlParameter("@patientname", p.PatientName),
                new MySqlParameter("@age", p.Age),
                new MySqlParameter("@sex", p.Sex),
                new MySqlParameter("@idCard_Num", p.IdCard_Num),
                new MySqlParameter("@in_HospitalNum", p.In_HospitalNum),
                new MySqlParameter("@major_Doctor", p.Major_Doctor),
                new MySqlParameter("@main_Symptom", p.Main_Symptom),
                new MySqlParameter("@phone_Num", p.Phone_Num),
                new MySqlParameter("@in_Date", p.In_Date)
            };
            if (DBOper.ExecuteCommand(sqlInsert, paramArray) == 1)
            {
                return(true);
            }
            else
            {
                return(false);
            }
        }
Exemple #23
0
    public static int deleteRecord(UpImage obj)
    {
        string strSQL = "delete from upImage where imageId='" + obj.imageId + "'";
        int    iRet   = DBOper.execNonQueryBySQLText(strSQL);

        return(iRet);
    }
        public static bool UpdatePatient(Patient p)
        {
            string sqlUpdate = @"Update Patient set age=@age, 
            sex=@sex,idcard_num=@idCard_Num,in_hospitalnum=@in_HospitalNum,
            major_doctor=@major_Doctor,
            main_symptom=@main_Symptom,
            phone_num=@phone_Num,
            in_date=@in_Date
            where PatientName=@patientName
            ";

            MySqlParameter[] param = new MySqlParameter[] {
                new MySqlParameter("@age", p.Age),
                new MySqlParameter("@sex", p.Sex),
                new MySqlParameter("@idCard_Num", p.IdCard_Num),
                new MySqlParameter("@in_HospitalNum", p.In_HospitalNum),
                new MySqlParameter("@major_Doctor", p.Major_Doctor),
                new MySqlParameter("@main_Symptom", p.Main_Symptom),
                new MySqlParameter("@phone_Num", p.Phone_Num),
                new MySqlParameter("@in_Date", p.In_Date)
            };
            MySqlParameter pIn = new MySqlParameter("@patientName", p.PatientName);

            if (DBOper.ExecuteCommand(sqlUpdate, pIn, param) == 1)
            {
                return(true);
            }
            else
            {
                return(false);
            }
        }
Exemple #25
0
        public static bool InsertRoomInfo(CheckInRoom registerRoomInfo)
        {
            string         sqlInsert = @"insert into checkin values(null,@roomId,@price,@foregift,@inTime,@outTime,@clientName,
                            @sex,@phone,@cerType,@certId,@address,@personNum,@Oper,@deMark)";
            MySqlParameter p1        = new MySqlParameter("@roomId", registerRoomInfo.RoomId);
            MySqlParameter p2        = new MySqlParameter("@price", registerRoomInfo.Price);
            MySqlParameter p3        = new MySqlParameter("@foregift", registerRoomInfo.Foregift);
            MySqlParameter p4        = new MySqlParameter("@inTime", registerRoomInfo.InTime);
            MySqlParameter p5        = new MySqlParameter("@outTime", registerRoomInfo.OutTime);
            MySqlParameter p6        = new MySqlParameter("@clientName", registerRoomInfo.ClientName);
            MySqlParameter p7        = new MySqlParameter("@sex", registerRoomInfo.Sex);
            MySqlParameter p8        = new MySqlParameter("@phone", registerRoomInfo.Phone);
            MySqlParameter p9        = new MySqlParameter("@certType", registerRoomInfo.CertType);
            MySqlParameter p10       = new MySqlParameter("@certId", registerRoomInfo.CertId);
            MySqlParameter p11       = new MySqlParameter("@address", registerRoomInfo.Address);
            MySqlParameter p12       = new MySqlParameter("@personNum", registerRoomInfo.PersonNum);
            MySqlParameter p13       = new MySqlParameter("@Oper", registerRoomInfo.Oper);
            MySqlParameter p14       = new MySqlParameter("@delMark", registerRoomInfo.DelMark);

            MySqlParameter[] paramArray = new MySqlParameter[] { p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14 };
            string           sqlUpdate  = "update room set inPerson=@inPerson where roomId=@roomId";
            MySqlParameter   n1         = new MySqlParameter("@roomId", registerRoomInfo.RoomId);
            MySqlParameter   n2         = new MySqlParameter("@inPerson", registerRoomInfo.PersonNum);

            if (DBOper.ExecuteCommand(sqlInsert, paramArray) == 1 && DBOper.ExecuteCommand(sqlInsert, n1, n2) == 1)
            {
                return(true);
            }
            else
            {
                return(false);
            }
        }
Exemple #26
0
    public static int insertRecord(Magazine obj)
    {
        string strSQL = "insert into magazine(magazineId,magazineName,mdescription,magazineKey) values('" + obj.magazineId + "','" + obj.magazineName + "','" + obj.mdescription + "','" + obj.magazineKey + "')";
        int    iRet   = DBOper.execNonQueryBySQLText(strSQL);

        return(iRet);
    }
Exemple #27
0
    public static int deleteshenqingRecord(yuyuehuiyi obj)
    {
        string strSQL = "delete from shenqinghuiyi where huiyiname='" + obj.huiyiname + "'";
        int    iRet   = DBOper.execNonQueryBySQLText(strSQL);

        return(iRet);
    }
Exemple #28
0
    public static DataTable huiyishenqingqb(yuyuehuiyi hy)
    {
        string    str = "select * from shenqinghuiyi where 1=1 ";
        DataTable dt  = DBOper.execQueryBySQLText(str);

        return(dt);
    }
Exemple #29
0
    /// <summary>
    /// 执行SQL文本,返回DataTable
    /// </summary>
    /// <param name="strSQLText">SQL文本</param>
    /// <returns>DataTable</returns>
    public static DataTable execQueryBySQLText(string strSQLText)
    {
        OleDbCommand comm = DBOper.CreateCommandDBCon();

        comm.CommandType = CommandType.Text;
        comm.CommandText = strSQLText;

        DataTable table = null;

        try
        {
            comm.Connection.Open();
            OleDbDataReader reader = comm.ExecuteReader();
            table = new DataTable();
            table.Load(reader);
            reader.Close();
        }
        catch (Exception ex)
        {
            throw ex;
        }

        finally
        {
            comm.Connection.Close();
        }
        return(table);
    }
Exemple #30
0
        public static DataTable GetRoomInfo()
        {
            string    sql = @"select roomId as 房间号,roomType as 房间类型,roomFloor as 层数,Price as 价格,personNum as 可入住人数,inPerson as 已入住人数,note as 备注 from room where inPerson=0";
            DataTable dt  = DBOper.GetDataTable(sql);

            return(dt);
        }
Exemple #31
0
        public string CreateLineListExternTable(string projectCode)
        {
            string result = SUCCESS;
            try
            {
                Logger.Info("Start CreateLineListExternTable");

                _DBOper = new DBOper(_DbConnStr);
                string sql = string.Format(@"CREATE TABLE PDB.TB_LL_EXTN_{0}
                                            (
                                              LINE_NO    VARCHAR2(40 BYTE),
                                              RVSN_NO    VARCHAR2(4 BYTE),
                                              VER_NO     VARCHAR2(4 BYTE),
                                              PJT_CD     VARCHAR2(20 BYTE),
                                              REG_DT     VARCHAR2(23 BYTE),
                                              REG_EMPNO  VARCHAR2(10 BYTE),
                                              UPD_DT     VARCHAR2(23 BYTE),
                                              UPD_EMPNO  VARCHAR2(10 BYTE)
                                            )", projectCode);
                _DBOper.SaveData(sql);

                sql = string.Format(@"CREATE UNIQUE INDEX PDB.TB_LL_EXTN_{0}_PK ON PDB.TB_LL_EXTN_{0}
                                    (LINE_NO, RVSN_NO, VER_NO, PJT_CD)
                                    LOGGING
                                    TABLESPACE PDB
                                    PCTFREE    10
                                    INITRANS   2
                                    MAXTRANS   255
                                    STORAGE    (
                                                INITIAL          64K
                                                MINEXTENTS       1
                                                MAXEXTENTS       UNLIMITED
                                                PCTINCREASE      0
                                                BUFFER_POOL      DEFAULT
                                                )
                                    NOPARALLEL", projectCode);
                _DBOper.SaveData(sql);

                sql = string.Format(@"ALTER TABLE PDB.TB_LL_EXTN_{0} ADD (
                                    CONSTRAINT TB_LL_EXTN_{0}_PK
                                    PRIMARY KEY
                                    (LINE_NO, RVSN_NO, VER_NO, PJT_CD)
                                    USING INDEX PDB.TB_LL_EXTN_{0}_PK
                                    ENABLE VALIDATE)", projectCode);
                _DBOper.SaveData(sql);

                return result;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
                result = string.Format("CreateLineListExternTable Error: {0}", ex.Message);
                return result;
            }
        }
Exemple #32
0
        public string AddLineListCustomProperty(string projectCode, string propertyName, string maxLength)
        {
            string result = SUCCESS;
            try
            {
                Logger.Info("Start AddLineListCustomProperty");

                _DBOper = new DBOper(_DbConnStr);
                string sql = string.Format(@"ALTER TABLE TB_LL_EXTN_{0}
                                            ADD ({1}  VARCHAR2({2}))", projectCode, propertyName, maxLength);
                _DBOper.SaveData(sql);

                return result;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
                result = string.Format("AddLineListCustomProperty Error: {0}", ex.Message);
                return result;
            }
        }
Exemple #33
0
        private int InsertIsoList(string dwgNo, string sht, string line_no, string project_code)
        {
            int result = 0;
            try
            {
                Logger.Info("Start InsertIsoList");
                _DBOper = new DBOper(_DbConnStr);
                string sql = string.Format(@"Insert into ISO_LIST(dwg_no, sht, status, line_no, reg_dt, reg_empno, upd_dt, upd_empno, project_code)
                                        values('{0}', '{1}', '', '{2}', SYSDATE, 'Jorge Yoo', SYSDATE, 'Jorge Yoo', '{3}')"
                                        , dwgNo, sht, line_no, project_code);
                Logger.Info(sql);
                result = _DBOper.SaveData(sql);
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("InsertIsoList Error : {0}", ex.Message));
            }

            return result;
        }
Exemple #34
0
        private ArrayList GetIsoRevisionIndex()
        {
            try
            {
                Logger.Info("Start GetIsoRevisionIndex");
                _DBOper = new DBOper(_DbConnStr);
                string sql = string.Format(@"select rev.revision from iso_revision rev
                                            group by rev.revision
                                            order by revision asc");

                DataTable dt = _DBOper.GetDataTable(sql);
                DataRow[] dRows = dt.Select();
                ArrayList isoRevision = new ArrayList();

                foreach (DataRow dRow in dRows)
                {
                    isoRevision.Add(dRow["revision"]);
                }

                return isoRevision;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
            return null;
        }
Exemple #35
0
        private int InsertIsoFiles(string dctmObjectId, string fileExt, string fileName, string dwgNo, string sht, string revision, string project_code)
        {
            int result = 0;
            try
            {
                Logger.Info("Start InsertIsoList");
                _DBOper = new DBOper(_DbConnStr);
                string sql = string.Format(@"Insert into ISO_FILES(file_no, file_dest, file_extension, file_name, dwg_no, sht, revision, reg_dt, reg_empno, upd_dt, upd_empno, project_code)
                                        values(auto_increment_by_1.NEXTVAL, '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', SYSDATE, 'Jorge Yoo', SYSDATE, 'Jorge Yoo', '{6}')"
                                        , dctmObjectId, fileExt, fileName, dwgNo, sht, revision, project_code);
                result = _DBOper.SaveData(sql);
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("InsertIsoFiles Error : {0}", ex.Message));
            }

            return result;
        }
Exemple #36
0
        public DataTable GetIsoRevision(string where = "")
        {
            try
            {
                Logger.Info("Start GetIsoRevision");
                _DBOper = new DBOper(_DbConnStr);
                string sql = where.Equals(string.Empty) ? string.Format(@"SELECT * FROM ISO_REVISION")
                                                        : string.Format(@"SELECT * FROM ISO_REVISION WHERE {0}", where);
                DataTable dt = _DBOper.GetDataTable(sql);

                return dt;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
            return null;
        }
Exemple #37
0
        public int SaveRevisionVersion(string projectCode,string revision, string version, string user, string description)
        {
            try
            {
                Logger.Info("Start SaveRevisionVersion");

                _DBOper = new DBOper(_DbConnStr);
                string sqlCheck = string.Empty;
                string sql = string.Empty;
                int result = 0;
                DataTable dt = new DataTable();
                //Revision Version Insert
                sqlCheck = string.Format(@"SELECT * FROM LINELIST_HISTORY WHERE PROJECT_CODE = '{0}' AND REVISION = '{1}'  AND VERSION = '{2}' ", projectCode, revision, version);
                dt = _DBOper.GetDataTable(sqlCheck);
                if (dt.Rows.Count == 0)
                {
                    sql = string.Format(@"INSERT INTO LINELIST_HISTORY( PROJECT_CODE, REVISION, VERSION, DESCRIPTION, CREATE_DATE,CREATE_USER,UPDATE_DATE,UPDATE_USER)
                                             VALUES ( '{0}','{1}','{2}','{3}',SYSDATE,'{4}',SYSDATE,'{4}')",  projectCode, revision, version, description, user);
                    result = _DBOper.SaveData(sql);
                }

                return result;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
                return 0;
            }
        }
Exemple #38
0
        public DataTable LoadVersionLineList(string projectCode, string revision)
        {
            try
            {
                Logger.Info("Start LoadVersionLineList");
                _DBOper = new DBOper(_DbConnStr);
                string sql = string.Format(@"
                                            SELECT VERSION
                                            FROM LINELIST_HISTORY
                                            WHERE PROJECT_CODE = '{0}' AND REVISION = '{1}' ORDER BY CREATE_DATE DESC
                                        ", projectCode, revision);
                DataTable dt = _DBOper.GetDataTable(sql);
                return dt;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }

            return null;
        }
Exemple #39
0
        public int SaveLineList(string revision, string version, string projectCode, string user, string description, string type, string arrstringValue)
        {
            try
            {
                Logger.Info("Start SaveLineList");

                _DBOper = new DBOper(_DbConnStr);
                string sqlCheck = string.Empty;
                string sql = string.Empty;
                int result = 0;
                string lineNo = string.Empty;
                DataTable dt = new DataTable();
                DataTable dtPrevious = new DataTable();
                string[] arrValue = arrstringValue.Split(new char[] { '+' });
                //Master Insert
                sqlCheck = string.Format(@"SELECT * FROM LINELIST_MASTER WHERE PROJECT_CODE = '{0}' AND LINE_NO = '{1}' ", projectCode, arrValue[3].ToString());
                dt = _DBOper.GetDataTable(sqlCheck);
                if (dt.Rows.Count == 0)
                {
                    sql = string.Format(@"INSERT INTO LINELIST_MASTER(LINE_NO, PROJECT_CODE,CREATE_DATE,CREATE_USER,UPDATE_DATE,UPDATE_USER)
                                             VALUES ( '{0}','{1}',SYSDATE,'{2}',SYSDATE,'{2}')", arrValue[3].ToString(), projectCode, user);
                    result = _DBOper.SaveData(sql);
                }
                sqlCheck = string.Format(@"SELECT LINE_SIZE,FLUID_CODE,SEQUENCE_NO,PIPING_SPEC,SCHEDULE,HYDRO_TEST_MEDIUM,
                                                HYDRO_TEST_PRESSURE,INSULATION_SPEC,INSULATION_THICK,SERVICE,FROM_,TO_,DESIGN_TEMPERATURE,
                                                DESIGN_PRESSURE,PHASE,SPECIFIC_GRAVITY,FLOW_RATE,MASS_FLOW,OPERATING_TEMPERATURE,OPERATING_PRESSURE,
                                                VISCOSITY,MASS_DENSITY,VELOCITY,DIFFERENTIAL_PRESSURE,TOTAL_LENGTH,PID_NO,LINE_TYPE,DELETED
                                                FROM LINELIST_BASIC WHERE PROJECT_CODE = '{0}' AND LINE_NO = '{1}' AND LATEST ='Y' ", projectCode, arrValue[3].ToString());
                dt = _DBOper.GetDataTable(sqlCheck);
                dtPrevious = dt;
                if (dt.Rows.Count > 0)
                {
                    sql = string.Format(@"UPDATE LINELIST_BASIC
                                        SET LATEST = 'N',
                                            UPDATE_DATE = SYSDATE,
                                            UPDATE_USER = '******'
                                        WHERE LINE_NO = '{0}' and project_code = '{1}'"
                        , arrValue[3].ToString(), projectCode, user);
                    result = _DBOper.SaveData(sql);
                }

                //Basic Insert
                sql = string.Format(@"INSERT INTO LINELIST_BASIC(LINE_NO, LINE_SIZE,FLUID_CODE,SEQUENCE_NO,PIPING_SPEC,SCHEDULE,HYDRO_TEST_MEDIUM,
                                                HYDRO_TEST_PRESSURE,INSULATION_SPEC,INSULATION_THICK,SERVICE,FROM_,TO_,DESIGN_TEMPERATURE,
                                                DESIGN_PRESSURE,PHASE,SPECIFIC_GRAVITY,FLOW_RATE,MASS_FLOW,OPERATING_TEMPERATURE,OPERATING_PRESSURE,
                                                VISCOSITY,MASS_DENSITY,VELOCITY,DIFFERENTIAL_PRESSURE,TOTAL_LENGTH,PID_NO,LINE_TYPE,
                                                REVISION,VERSION,DELETED,LATEST,PROJECT_CODE,CREATE_DATE,CREATE_USER,UPDATE_DATE,UPDATE_USER)
                                             VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}',
                                            '{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}',
                                            '{30}','{31}','{32}',SYSDATE,'{33}',SYSDATE,'{33}')
                                            ", arrValue[3].ToString(), arrValue[4].ToString(), arrValue[5].ToString(), arrValue[6].ToString(), arrValue[7].ToString(),
                                                       arrValue[8].ToString(), arrValue[9].ToString(), arrValue[10].ToString(), arrValue[11].ToString(), arrValue[12].ToString(),
                                                       arrValue[13].ToString(), arrValue[14].ToString(), arrValue[15].ToString(), arrValue[16].ToString(), arrValue[17].ToString(),
                                                       arrValue[18].ToString(), arrValue[19].ToString(), arrValue[20].ToString(), arrValue[21].ToString(), arrValue[22].ToString(),
                                                       arrValue[23].ToString(), arrValue[24].ToString(), arrValue[25].ToString(), arrValue[26].ToString(), arrValue[27].ToString(),
                                                       arrValue[28].ToString(), arrValue[29].ToString(), arrValue[30].ToString(), revision, version, type, 'Y', projectCode, user);
                result = _DBOper.SaveData(sql);

                //History Detail 쿼리. 지금은 속성 아이디 하드코딩
                if (dtPrevious.Rows.Count > 0)
                {
                    for (int i = 0; i < dtPrevious.Columns.Count - 1; i++) //마지막은 deleted 컬럼이기때문에 insert 안한다
                    {
                        string currentValue;
                        if (type == "Y") //Delete되는 값은 히스토리에 NULL로 저장
                        {
                            currentValue = null;
                        }
                        else
                        {
                            currentValue = arrValue[i + 4]; //새로생기거나 바뀐 속성은 속성값으로 insert
                        }
                        if (dtPrevious.Rows[0][i].ToString() != currentValue || dtPrevious.Rows[0]["DELETED"].ToString() =="Y")//이전버전에 Linelist가 Delete라면 이전버전과 지금버전의 속성이 같아도 insert
                        {
                            sql = string.Format(@"INSERT INTO LINELIST_HISTORY_DETAIL(LINE_NO,REVISION,VERSION,ATTRIBUTE_ID,
                                                PREVIOUS_VALUE,CURRENT_VALUE,PROJECT_CODE,CREATE_DATE,CREATE_USER,UPDATE_DATE,UPDATE_USER)
                                             VALUES ( '{0}','{1}','{2}','{3}','{4}','{5}','{6}',SYSDATE,'{7}',SYSDATE,'{7}')",
                                                 arrValue[3].ToString(), revision, version, i + 1, dtPrevious.Rows[0][i].ToString(), currentValue, projectCode, user);
                            result = _DBOper.SaveData(sql);
                        }
                    }
                }
                else
                {
                    for (int i = 0; i < dtPrevious.Columns.Count; i++)
                    {
                        string currentValue;
                        if (type == "Y") //Delete되는 값
                        {
                            currentValue = null;
                        }
                        else
                        {
                            currentValue = arrValue[i + 4];
                        }
                        sql = string.Format(@"INSERT INTO LINELIST_HISTORY_DETAIL(LINE_NO,REVISION,VERSION,ATTRIBUTE_ID,
                                                PREVIOUS_VALUE,CURRENT_VALUE,PROJECT_CODE,CREATE_DATE,CREATE_USER,UPDATE_DATE,UPDATE_USER)
                                             VALUES ( '{0}','{1}','{2}','{3}','{4}','{5}','{6}',SYSDATE,'{7}',SYSDATE,'{7}')",
                                             arrValue[3].ToString(), revision, version, i + 1, null, currentValue, projectCode, user);
                        result = _DBOper.SaveData(sql);
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
                return 0;
            }
        }
Exemple #40
0
        public DataTable GetIsoBatchDate()
        {
            try
            {
                Logger.Info("Start GetIsoBatchDate");
                _DBOper = new DBOper(_DbConnStr);
                string sql = string.Format(@"select DISTINCT(REVISION_DATE) from iso_revision order by REVISION_DATE DESC");
                DataTable dt = _DBOper.GetDataTable(sql);

                return dt;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
            return null;
        }
Exemple #41
0
        public DataTable LoadRevisionLineList(string projectCode)
        {
            try
            {
                Logger.Info("Start LoadRevisionLineList");
                _DBOper = new DBOper(_DbConnStr);
                string sql = string.Format(@"
                                        SELECT REVISION
                                        FROM (
                                            SELECT
                                                REVISION,
                                                CREATE_DATE,
                                                ROW_NUMBER() OVER(
                                                                PARTITION BY REVISION ORDER BY CREATE_DATE) CNT
                                            FROM LINELIST_HISTORY
                                            WHERE PROJECT_CODE = '{0}'
                                            )WHERE CNT = 1
                                            ORDER BY create_date desc", projectCode);
                DataTable dt = _DBOper.GetDataTable(sql);
                return dt;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }

            return null;
        }
Exemple #42
0
        public DataTable LoadLineHistory(string projectCode, string revision, string version)
        {
            try
            {
                Logger.Info("Start LoadLineHistory");
                _DBOper = new DBOper(_DbConnStr);
                string sql = string.Format(@"
                                            SELECT a.LINE_NO, b.ATTRIBUTE_DISPLAY_NAME,a.PREVIOUS_VALUE,a.CURRENT_VALUE
                                            FROM LINELIST_HISTORY_DETAIL a,LINELIST_ATTRIBUTE b
                                            WHERE  a.attribute_id = b.attribute_id AND a.PROJECT_CODE = '{0}' AND REVISION = '{1}' AND VERSION = '{2}'
                                        ", projectCode, revision, version);
                DataTable dt = _DBOper.GetDataTable(sql);
                return dt;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }

            return null;
        }
Exemple #43
0
        private int InsertIsoRevision(string sht, string revision, string dwgNo, string project_code)
        {
            int result = 0;
            try
            {
                Logger.Info("Start InsertIsoList");
                _DBOper = new DBOper(_DbConnStr);

                // Update Previous Revision's Current_Revision Y=>N
                DataTable dtIsoRevision = GetIsoRevision();
                DataRow[] dIsoRows = dtIsoRevision.Select(string.Format(@"{0} = '{1}' AND {2} = '{3}' AND {4} = '{5}' AND {6} = '{7}'"
                                                            , "SHT", sht, "DWG_NO", dwgNo, "Project_Code", project_code, "CURRENT_REVISION", "Y"));
                if (dIsoRows.Count() > 0)
                {
                    foreach (DataRow dRow in dIsoRows)
                    {
                        string updateSql = string.Format(@"Update ISO_REVISION SET {0} = '{1}' WHERE {2} = '{3}' AND {4} = '{5}'"
                                            , "CURRENT_REVISION", "N", "SHT", dRow["SHT"], "DWG_NO", dRow["DWG_NO"]);
                        result = _DBOper.SaveData(updateSql);

                    }
                }

                string sql = string.Format(@"Insert into ISO_REVISION(sht, revision, revision_date, current_revision, dwg_no, reg_dt, reg_empno, upd_dt, upd_empno, project_code)
                                        values('{0}', '{1}', SYSDATE, 'Y', '{2}', SYSDATE, 'Jorge Yoo', SYSDATE, 'Jorge Yoo', '{3}')"
                                        , sht, revision, dwgNo, project_code);
                result = _DBOper.SaveData(sql);
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("InsertIsoRevision Error : {0}", ex.Message));
            }

            return result;
        }
Exemple #44
0
        public DataTable LoadLatestRevisionLineList(string projectCode)
        {
            try
            {
                Logger.Info("Start LoadLatestVersionLineList");
                _DBOper = new DBOper(_DbConnStr);
                string sql = string.Format(@"
                                            SELECT a.REVISION,a.VERSION
                                            FROM
                                            (SELECT *
                                            FROM LINELIST_HISTORY
                                            WHERE PROJECT_CODE = '{0}' ORDER BY CREATE_DATE DESC) a
                                            where rownum='1'
                                            ", projectCode);
                DataTable dt = _DBOper.GetDataTable(sql);
                return dt;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }

            return null;
        }
Exemple #45
0
 public DataTable LoadCompareDistinctLineList(string projectCode, string revision, string version)
 {
     try
     {
         Logger.Info("Start LoadLineList");
         _DBOper = new DBOper(_DbConnStr);
         string sql = string.Format(@"   SELECT  LINE_NO
                                             FROM linelist_basic
                                             WHERE
                                                   REVISION = '{1}' AND
                                                   VERSION <= '{2}'  AND
                                                   PROJECT_CODE = '{0}'
                                                   GROUP BY LINE_NO
                                         UNION
                                         SELECT  LINE_NO
                                             FROM linelist_basic
                                             WHERE
                                                   REVISION < '{1}' AND
                                                   PROJECT_CODE = '{0}'
                                                   GROUP BY LINE_NO
                                     ", projectCode, revision, version);
         DataTable dt = _DBOper.GetDataTable(sql);
         return dt;
     }
     catch (Exception ex)
     {
         Logger.Error(ex);
     }
     return null;
 }
Exemple #46
0
        public DataTable LoadBatchIsoList(string batchDate)
        {
            try
            {
                Logger.Info("Start LoadBatchIsoList");
                _DBOper = new DBOper(_DbConnStr);
                string sql = string.Format("select '' as Status, rev.DWG_NO \"ISO No\", rev.SHT \"SH'T\", rev.REVISION , rev.REVISION_DATE \"Rev Date\", lst.line_no \"Line No\", REV.REVISION_REMARK \"Remark\" from iso_revision rev, iso_list lst where revision_date = '{0}' and rev.dwg_no = lst.dwg_no", batchDate);
                DataTable dt = _DBOper.GetDataTable(sql);

                return dt;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
            return null;
        }
Exemple #47
0
        public DataTable GetMasterIsoList()
        {
            try
            {
                ArrayList arIsoRevision = GetIsoRevisionIndex();
                string revisionColumn = string.Empty;
                foreach (string revision in arIsoRevision)
                {
                    revisionColumn += string.Format("MAX(DECODE(rev.revision, '{0}', rev.revision_date, '')) REV_{0}, ", revision);
                }

                Logger.Info("Start GetMasterIsoList");
                _DBOper = new DBOper(_DbConnStr);
                string sql = string.Format(@"select '' as Unit, '' as LineNo, rev.DWG_NO as DrawingNumber, rev.SHT, '' as STATUS
                                            , '' as DCNStatus, '' as DCNNO, '' as CRNNO
                                            , {0} MAX(rev.revision) as Current_Revision, '' as Remark
                                            from ISO_LIST list, ISO_REVISION rev
                                            WHERE list.dwg_no = rev.dwg_no
                                            group by rev.DWG_NO, rev.SHT", revisionColumn);
                DataTable dt = _DBOper.GetDataTable(sql);

                return dt;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
            return null;
        }
Exemple #48
0
        public DataTable GetLineListMaster()
        {
            try
            {
                Logger.Info("Start GetLineListMaster");
                _DBOper = new DBOper(_DbConnStr);
                string sql = string.Format(@"SELECT * FROM LINELIST_MASTER");
                DataTable dt = _DBOper.GetDataTable(sql);

                return dt;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
            return null;
        }
Exemple #49
0
        public DataTable LoadLineListHistory(string projectCode,string revision, string version)
        {
            try
            {
                Logger.Info("Start LoadLineListHistory");
                _DBOper = new DBOper(_DbConnStr);
                string sql = string.Format(@" SELECT '' as COLUMNDCNSTATUS,
                                                    '' as COLUMNDCNNO,
                                                    '' as COLUMNREMARK,
                                                    LINE_NO,LINE_SIZE,FLUID_CODE,SEQUENCE_NO,PIPING_SPEC,SCHEDULE,HYDRO_TEST_MEDIUM,
                                                    HYDRO_TEST_PRESSURE,INSULATION_SPEC,INSULATION_THICK,SERVICE,FROM_,TO_,DESIGN_TEMPERATURE,
                                                    DESIGN_PRESSURE,PHASE,SPECIFIC_GRAVITY,FLOW_RATE,MASS_FLOW,OPERATING_TEMPERATURE,
                                                    OPERATING_PRESSURE,VISCOSITY,MASS_DENSITY,VELOCITY,DIFFERENTIAL_PRESSURE,TOTAL_LENGTH,PID_NO,
                                                    LINE_TYPE,
                                                    '' as COLUMNSTATUS,
                                                    '' as COLUMNNUMBER,
                                                    '' as COLUMNAFTERSTATUS
                                                FROM linelist_basic
                                                WHERE DELETED = 'N'   AND
                                                      revision = '{1}' AND
                                                      version <= '{2}' AND
                                                      LATEST = 'Y'  AND
                                                      PROJECT_CODE = '{0}'
                                                UNION
                                                SELECT '' as COLUMNDCNSTATUS,
                                                    '' as COLUMNDCNNO,
                                                    '' as COLUMNREMARK,
                                                    LINE_NO,LINE_SIZE,FLUID_CODE,SEQUENCE_NO,PIPING_SPEC,SCHEDULE,HYDRO_TEST_MEDIUM,
                                                    HYDRO_TEST_PRESSURE,INSULATION_SPEC,INSULATION_THICK,SERVICE,FROM_,TO_,DESIGN_TEMPERATURE,
                                                    DESIGN_PRESSURE,PHASE,SPECIFIC_GRAVITY,FLOW_RATE,MASS_FLOW,OPERATING_TEMPERATURE,
                                                    OPERATING_PRESSURE,VISCOSITY,MASS_DENSITY,VELOCITY,DIFFERENTIAL_PRESSURE,TOTAL_LENGTH,PID_NO,
                                                    LINE_TYPE,
                                                    '' as COLUMNSTATUS,
                                                    '' as COLUMNNUMBER,
                                                    '' as COLUMNAFTERSTATUS
                                                FROM linelist_basic
                                                WHERE DELETED = 'N'   AND
                                                      revision < '{1}' AND
                                                      LATEST = 'Y'  AND
                                                      PROJECT_CODE = '{0}'
                                            ", projectCode,revision,version);
                DataTable dt = _DBOper.GetDataTable(sql);

                return dt;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
            return null;
        }
Exemple #50
0
        public DataTable GetChangedLineList(string revision, string version)
        {
            const string CONST_STATE_COLUMN = "STATE";

            _DBOper = new DBOper(_DbConnStr);
            string curSql = string.Format("");
            string prevSql = string.Format("");

            DataTable dtChangedLineList = new DataTable();

            //DataTable dtCurLineList = _DBOper.GetDataTable(curSql);
            //DataTable dtPrevLineList = _DBOper.GetDataTable(prevSql);

            //DataColumn state_dc1 = new DataColumn(CONST_STATE_COLUMN, typeof(String));
            //insertDT.Columns.Add(state_dc1);
            //DataColumn state_dc2 = new DataColumn(CONST_STATE_COLUMN, typeof(String));
            //deleteDT.Columns.Add(state_dc2);

            //dt = insertDT.Clone();
            //updateDT = deleteDT.Clone();

            //DataRow[] dInsertRows = insertDT.Select();
            //foreach (DataRow item in dInsertRows)
            //{
            //    int count = deleteDT.Select(string.Format("SP_ID = '{0}'", item["SP_ID"])).Count();
            //    if (count == 0)
            //    {
            //        item[CONST_STATE_COLUMN] = "Add";
            //        dt.ImportRow(item);
            //    }
            //    else
            //    {
            //        item[CONST_STATE_COLUMN] = "Current";
            //        updateDT.ImportRow(item);
            //    }
            //}

            //DataRow[] dDeleteRows = deleteDT.Select();
            //foreach (DataRow item in dDeleteRows)
            //{
            //    int count = insertDT.Select(string.Format("SP_ID = '{0}'", item["SP_ID"])).Count();
            //    if (count == 0)
            //    {
            //        item[CONST_STATE_COLUMN] = "Delete";
            //        dt.ImportRow(item);
            //    }
            //    else
            //    {
            //        item[CONST_STATE_COLUMN] = "Previous";
            //        updateDT.ImportRow(item);
            //    }
            //}

            return dtChangedLineList;
        }