Esempio n. 1
0
        public IList <makkebunDTO> PagedListDTO(int pageIndex, int pageSize, string sidx, string sord, out int totalRecords, makkebun oWhereClause)
        {
            string whereClause = "";
            string operators   = "";

            if (!object.Equals(null, oWhereClause))
            {
                if (oWhereClause.appinfo_id >= 0)
                {
                    operators   = whereClause.StartsWith("WHERE") ? " AND " : "WHERE";
                    whereClause = whereClause + operators + " a.appinfo_id=@appinfo_id ";
                }
            }

            IVariableSettingRepo VariableSettingRepo = new VariableSettingRepo();
            VariableSetting      VariableSetting     = VariableSettingRepo.GetBy("HiddenDataSync");

            if (VariableSetting != null && Convert.ToBoolean(VariableSetting.Value))
            {
                operators   = whereClause.StartsWith("WHERE") ? " AND " : "WHERE";
                whereClause = whereClause + operators + " c.syncdate is null ";
            }

            string qry    = string.Format(@"SELECT a.*, IFNULL(b.tarikh_lawat, 'BELUM LAWAT') tarikh_lawat, b.id semak_tapak_id FROM MAKKEBUN a LEFT JOIN SEMAK_TAPAK b
                                            ON a.id_makkebun = b.makkebun_id AND a.appinfo_id = b.appinfo_id join appinfo c on a.appinfo_id = c.id
                                            {0} ORDER BY {1} {2} LIMIT {3}, {4}", whereClause, sidx, sord, (pageIndex - 1) * pageSize, pageSize);
            string qryCtn = string.Format(@"SELECT COUNT(a.id_makkebun)  FROM MAKKEBUN a LEFT JOIN SEMAK_TAPAK b
                                            ON a.id_makkebun = b.makkebun_id AND a.appinfo_id = b.appinfo_id join appinfo c on a.appinfo_id = c.id {0}", whereClause);

            IList <makkebunDTO> lstEnt = sqliteCon.Query <makkebunDTO>(qry, oWhereClause).ToList();

            totalRecords = sqliteCon.Query <int>(qryCtn, oWhereClause).FirstOrDefault();
            return(lstEnt);
        }
Esempio n. 2
0
        private IDbConnection MySQLConnectin()
        {
            try
            {
                if (_mysqlCon == null)
                {
                    IVariableSettingRepo VariableSettingRepo = new VariableSettingRepo();
                    VariableSetting      VariableSetting     = VariableSettingRepo.GetBy("Status");
                    if (VariableSetting != null)
                    {
                        if (!string.IsNullOrEmpty(VariableSetting.Value) && VariableSetting.Value.ToUpper() == "PRODUCTION")
                        {
                            VariableSetting VariableSettingConStr = VariableSettingRepo.GetBy("MySQLConn");
                            _mysqlCon = new MySqlConnection(WFUtils.Decrypt(VariableSettingConStr.Value));
                        }
                        else
                        {
                            _mysqlCon = new MySqlConnection("Server=127.0.0.1;Database=tsspk1511;Uid=root;Pwd=;");
                        }
                    }
                    else
                    {
                        _mysqlCon = new MySqlConnection("Server=127.0.0.1;Database=tsspk1511;Uid=root;Pwd=;");
                    }
                }
                if (_mysqlCon.State != ConnectionState.Open)
                {
                    _mysqlCon.Open();
                }
            }
            catch (Exception)
            {
                _mysqlCon = null;
            }

            return(_mysqlCon);
        }
Esempio n. 3
0
        public IList <appinfoDTO> PagedListDTO(int page, int rows, string sidx, string sodx, out int rowCount, appinfo oWhereClause = null)
        {
            string whereClause = "";
            string operators   = "";

            if (!object.Equals(null, oWhereClause))
            {
                if (oWhereClause.id > 0)
                {
                    operators   = whereClause.StartsWith("WHERE") ? " AND " : "WHERE";
                    whereClause = whereClause + operators + " id=@id ";
                }
                if (!string.IsNullOrEmpty(oWhereClause.bangsa))
                {
                    operators   = whereClause.StartsWith("WHERE") ? " AND " : "WHERE";
                    whereClause = whereClause + operators + " bangsa = @bangsa ";
                }
                if (!string.IsNullOrEmpty(oWhereClause.negeri))
                {
                    operators   = whereClause.StartsWith("WHERE") ? " AND " : "WHERE";
                    whereClause = whereClause + operators + " negeri = @negeri ";
                }
                if (!string.IsNullOrEmpty(oWhereClause.daerah))
                {
                    operators   = whereClause.StartsWith("WHERE") ? " AND " : "WHERE";
                    whereClause = whereClause + operators + " daerah = @daerah ";
                }
                if (!string.IsNullOrEmpty(oWhereClause.dun))
                {
                    operators   = whereClause.StartsWith("WHERE") ? " AND " : "WHERE";
                    whereClause = whereClause + operators + " dun = @dun   ";
                }
                if (!string.IsNullOrEmpty(oWhereClause.refno_new))
                {
                    operators   = whereClause.StartsWith("WHERE") ? " AND " : "WHERE";
                    whereClause = whereClause + operators + " refno_new like '%'||@refno_new||'%'   ";
                }
                if (oWhereClause.parlimen > 0)
                {
                    operators   = whereClause.StartsWith("WHERE") ? " AND " : "WHERE";
                    whereClause = whereClause + operators + " parlimen =@parlimen ";
                }
                if (!string.IsNullOrEmpty(oWhereClause.icno))
                {
                    operators   = whereClause.StartsWith("WHERE") ? " AND " : "WHERE";
                    whereClause = whereClause + operators + " icno like '%'||@icno||'%'   ";
                }
                if (!string.IsNullOrEmpty(oWhereClause.nolesen))
                {
                    operators   = whereClause.StartsWith("WHERE") ? " AND " : "WHERE";
                    whereClause = whereClause + operators + " nolesen like '%'||@nolesen||'%'   ";
                }
                if (oWhereClause.appdate != null && !string.IsNullOrEmpty(oWhereClause.appdate.Trim()))
                {
                    operators   = whereClause.StartsWith("WHERE") ? " AND " : "WHERE";
                    whereClause = whereClause + operators + " appdate =@appdate   ";
                }
            }

            operators   = whereClause.StartsWith("WHERE") ? " AND " : "WHERE";
            whereClause = whereClause + operators + " type COLLATE NOCASE ='Negeri' ";

            IVariableSettingRepo VariableSettingRepo = new VariableSettingRepo();
            VariableSetting      VariableSetting     = VariableSettingRepo.GetBy("HiddenDataSync");

            if (VariableSetting != null && Convert.ToBoolean(VariableSetting.Value))
            {
                operators   = whereClause.StartsWith("WHERE") ? " AND " : "WHERE";
                whereClause = whereClause + operators + " syncdate is null ";
            }

            //string qry = string.Format(@"SELECT nama, icno, value negeri, nolesen, refno_new, appdate, created, createdby, id, newrefno, syncdate
            //                                -- , keputusan
            //                                FROM appinfo join variables
            //                                ON negeri = code
            //                                {0} ORDER BY {1} {2} LIMIT {3}, {4}", whereClause, sidx, sodx, (page - 1) * rows, rows);
            string qry    = string.Format(@"SELECT nama, icno, value negeri, nolesen, refno_new, appdate, created, createdby, id, newrefno, syncdate
                                            FROM appinfo join variables
                                            ON negeri = code
                                            {0} ORDER BY {1} {2} LIMIT {3}, {4}", whereClause, sidx, sodx, (page - 1) * rows, rows);
            string qryCtn = string.Format(@"SELECT COUNT(*) FROM appinfo join variables
                                            ON negeri = code {0}", whereClause);

            IList <appinfoDTO> lstEnt = sqliteCon.Query <appinfoDTO>(qry, oWhereClause).ToList();

            rowCount = sqliteCon.Query <int>(qryCtn, oWhereClause).FirstOrDefault();
            return(lstEnt);
        }