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); }
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); }
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); }