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