public int Update(DistrictModels item)
{
int rs = 0;
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("UPDATE ");
sqlBuilder.Append("gov_district ");
sqlBuilder.Append("SET ");
sqlBuilder.Append("name = @1, ");
sqlBuilder.Append("level = @2, ");
sqlBuilder.Append("decription = @3, ");
sqlBuilder.Append("link = @4, ");
sqlBuilder.Append("update_user_id = @5, ");
sqlBuilder.Append("update_datetime = @6");
sqlBuilder.Append("order_number = @7");
sqlBuilder.Append("WHERE ");
sqlBuilder.Append("id = @8");
this.Sql = sqlBuilder.ToString();
MySqlCommand objCmd = new MySqlCommand(Sql, getConnection());
objCmd.Parameters.AddWithValue("@8", item.Id);
objCmd.Parameters.AddWithValue("@1", item.Name);
objCmd.Parameters.AddWithValue("@2", item.Level);
objCmd.Parameters.AddWithValue("@3", item.Decription);
objCmd.Parameters.AddWithValue("@4", item.Link);
objCmd.Parameters.AddWithValue("@5", item.UpdateUserId);
objCmd.Parameters.AddWithValue("@6", item.UpdateDatetime);
objCmd.Parameters.AddWithValue("@7", item.OrderNumber);
rs = objCmd.ExecuteNonQuery();
return rs;
}
public int insert(DistrictModels item)
{
int rs = 0;
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("INSERT ");
sqlBuilder.Append("INTO ");
sqlBuilder.Append("gov_district ");
sqlBuilder.Append("( ");
sqlBuilder.Append("name, ");
sqlBuilder.Append("level, ");
sqlBuilder.Append("decription, ");
sqlBuilder.Append("link, ");
sqlBuilder.Append("update_user_id, ");
sqlBuilder.Append("update_datetime,");
sqlBuilder.Append("order_number");
sqlBuilder.Append(") ");
sqlBuilder.Append("values(");
sqlBuilder.Append("@2, @3, @4, @5, @6, @7, @8");
sqlBuilder.Append(")");
this.Sql = sqlBuilder.ToString();
MySqlCommand objCmd = new MySqlCommand(Sql, getConnection());
objCmd.Parameters.AddWithValue("@2", item.Name);
objCmd.Parameters.AddWithValue("@3", item.Level);
objCmd.Parameters.AddWithValue("@4", item.Decription);
objCmd.Parameters.AddWithValue("@5", item.Link);
objCmd.Parameters.AddWithValue("@6", item.UpdateUserId);
objCmd.Parameters.AddWithValue("@7", item.UpdateDatetime);
objCmd.Parameters.AddWithValue("@8", item.OrderNumber);
rs = objCmd.ExecuteNonQuery();
return rs;
}
public String GetJsonMap(int id)
{
String rs = "{\"type\": \"FeatureCollection\",";
rs += "\"features\": [";
DistrictServices districtServices = new DistrictServices();
districtServices.ShowMap = Boolean.TrueString;
districtServices.Id = id.ToString();
List<DistrictModels> lstDistrict = districtServices.select(-1, -1);
DistrictModels districtInfo = new DistrictModels();
if(lstDistrict != null && lstDistrict.Count > 0){
districtInfo = lstDistrict.First();
}
String coordinates = districtInfo.Coordinates;
String[] lstCoordinates = coordinates.Split(',');
for (int i = 0; i < lstCoordinates.Length; i++ )
{
String[] coordinates1 = lstCoordinates[i].Trim().Split(' ');
rs += "{ \"type\": \"Feature\", \"id\": \"" + (i + 1).ToString() +"\", \"geometry\": { \"type\": \"Point\", \"coordinates\": [" + coordinates1[0] + "," + coordinates1[1] + "] } }";
if(i != (lstCoordinates.Length - 1))
rs += ",";
}
rs += "]}";
return rs;
}
public List<DistrictModels> select(int page, int limit)
{
int offset = (page - 1) * limit;
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("SELECT ");
sqlBuilder.Append("* ");
sqlBuilder.Append("FROM ");
sqlBuilder.Append("gov_district gdt ");
sqlBuilder.Append(getBaseSQL());
if (page > 0 && limit > 0)
{
sqlBuilder.Append(" LIMIT @limit ");
sqlBuilder.Append(" OFFSET @offset ");
}
this.Sql = sqlBuilder.ToString();
MySqlCommand objCmd = new MySqlCommand(Sql, getConnection());
if (page > 0 && limit > 0)
{
objCmd.Parameters.AddWithValue("@limit", limit);
objCmd.Parameters.AddWithValue("@offset", offset);
}
MySqlDataReader dataReader = objCmd.ExecuteReader();
List<DistrictModels> lstMenu = new List<DistrictModels>();
DistrictModels item;
while (dataReader.Read())
{
item = new DistrictModels();
item.Id = Convert.ToInt32(dataReader["id"]);
item.Name = dataReader["name"].ToString();
item.Level = Convert.ToInt32(dataReader["level"]);
item.Decription = dataReader["decription"].ToString();
item.Link = dataReader["link"].ToString();
item.ShowMap = Convert.ToBoolean(dataReader["show_map"]);
item.Coordinates = dataReader["coordinates"].ToString();
if (dataReader["order_number"] != null)
{
item.OrderNumber = Convert.ToInt32(dataReader["order_number"]);
}
//item.ActiveFlg = Convert.ToBoolean(dataReader["active_flg"]);
item.UpdateDatetime = Convert.ToDateTime(dataReader["update_datetime"]);
lstMenu.Add(item);
}
getConnection().Close();
return lstMenu;
}
