public IEnumerable <GroupTown> GetListTownInArea(GroupAreaModel condition)
{
// Declare database connection
StringBuilder sql = new StringBuilder();
// SQL発行
sql.Append(@" SELECT A. *, B.CITY_NAME, C.DISTRICT_NAME
FROM MstTown A
LEFT JOIN MstCity B
ON A.CITY_CD = B.CITY_CD
LEFT JOIN MstDistrict C
ON A.CITY_CD = C.CITY_CD AND A.DISTRICT_CD = C.DISTRICT_CD
WHERE A.DEL_FLG = @DEL_FLG ");
if (condition.FOR_USER == GroupForUser.Receive)
{
sql.Append("AND GROUP_CD_RECEIVE = @GROUP_CD ");
}
else
{
sql.Append("AND GROUP_CD_SENDER = @GROUP_CD ");
}
return(base.Query <GroupTown>(sql.ToString(),
new
{
DEL_FLG = DeleteFlag.NON_DELETE,
GROUP_CD = condition.GROUP_CD
}).ToList());
}
public long UpdateGroupArea(GroupAreaModel model)
{
long res = 0;
// Declare new DataAccess object
ManageDistrictDa dataAccess = new ManageDistrictDa();
using (var transaction = new TransactionScope())
{
MstGroupArea entity = new MstGroupArea();
entity.GROUP_CD = model.GROUP_CD;
entity.GROUP_NAME = model.GROUP_NAME;
entity.FOR_USER = model.FOR_USER;
entity.DEL_FLG = DeleteFlag.NON_DELETE;
entity.INS_DATE = Utility.GetCurrentDateTime();
entity.UPD_DATE = Utility.GetCurrentDateTime();
res = dataAccess.UpdateGroupArea(entity);
if (res <= 0)
{
transaction.Dispose();
}
transaction.Complete();
}
return(res);
}
public IEnumerable <MstGroupArea> SearchGroupAreatList(DataTableModel dt, GroupAreaModel model, out int total_row)
{
StringBuilder sql = new StringBuilder();
sql.Append(@"
SELECT A. *
FROM MstGroupArea A
WHERE
A.DEL_FLG = @DEL_FLG AND A.FOR_USER = @FOR_USER");
if (!string.IsNullOrEmpty(model.GROUP_NAME))
{
sql.Append(" AND (A.GROUP_NAME LIKE @GROUP_NAME)");
}
if (!string.IsNullOrEmpty(model.GROUP_CD_LIST))
{
sql.Append(" AND A.GROUP_CD IN ('" + model.GROUP_CD_LIST + "')");
}
else if (model.CITY_CD > 0 || model.DISTRICT_CD > 0 || model.TOWN_CD > 0)
{
sql.Append(" AND A.GROUP_CD IN ('')");
}
sql.Append(" ORDER BY A.GROUP_NAME ASC, A.UPD_DATE DESC ");
int lower = dt.iDisplayStart + 1;
int upper = dt.iDisplayStart + dt.iDisplayLength;
PagingHelper.SQLParts parts;
PagingHelper.SplitSQL(sql.ToString(), out parts);
string sqlpage = PagingHelper.BuildPageQuery(lower, dt.iDisplayLength, parts);
string sqlcount = parts.sqlCount;
var dataList = base.Query <MstGroupArea>(sqlpage,
new
{
DEL_FLG = model.DEL_FLG,
GROUP_NAME = '%' + model.GROUP_NAME + '%',
FOR_USER = model.FOR_USER,
pageindex = lower,
pagesize = upper
}).ToList();
total_row = base.Query <int>(sqlcount,
new
{
DEL_FLG = model.DEL_FLG,
GROUP_NAME = '%' + model.GROUP_NAME + '%',
FOR_USER = model.FOR_USER,
pageindex = lower,
pagesize = upper
}).FirstOrDefault();
return(dataList);
}
public IEnumerable <long> getListGroupForSearch(GroupAreaModel condition)
{
// Declare database connection
StringBuilder sql = new StringBuilder();
// SQL発行
if (condition.FOR_USER == GroupForUser.Receive)
{
sql.Append(@" SELECT DISTINCT GROUP_CD_RECEIVE AS GROUP_CD
FROM MstTown
WHERE GROUP_CD_RECEIVE > 0 AND [DEL_FLG] = @DEL_FLG ");
if (condition.CITY_CD > 0)
{
sql.Append("AND [CITY_CD] = @CITY_CD ");
}
if (condition.CITY_CD > 0 && condition.DISTRICT_CD > 0)
{
sql.Append("AND [DISTRICT_CD] = @DISTRICT_CD ");
}
if (condition.CITY_CD > 0 && condition.DISTRICT_CD > 0 && condition.TOWN_CD > 0)
{
sql.Append("AND [TOWN_CD] = @TOWN_CD ");
}
}
else
{
sql.Append(@" SELECT DISTINCT [GROUP_CD_SENDER] AS GROUP_CD
FROM MstTown
WHERE [GROUP_CD_SENDER] > 0 AND [DEL_FLG] = @DEL_FLG ");
if (condition.CITY_CD > 0)
{
sql.Append("AND [CITY_CD] = @CITY_CD ");
}
if (condition.CITY_CD > 0 && condition.DISTRICT_CD > 0)
{
sql.Append("AND [DISTRICT_CD] = @DISTRICT_CD ");
}
if (condition.CITY_CD > 0 && condition.DISTRICT_CD > 0 && condition.TOWN_CD > 0)
{
sql.Append("AND [TOWN_CD] = @TOWN_CD ");
}
}
return(base.Query <long>(sql.ToString(),
new
{
DEL_FLG = DeleteFlag.NON_DELETE,
CITY_CD = condition.CITY_CD,
DISTRICT_CD = condition.DISTRICT_CD,
TOWN_CD = condition.TOWN_CD
}).ToList());
}
public IEnumerable <MstGroupArea> SearchGroupAreatList(DataTableModel dt, GroupAreaModel model, out int total_row)
{
ManageDistrictDa dataAccess = new ManageDistrictDa();
if (model.CITY_CD != null && model.CITY_CD > 0)
{
var getListGroup = dataAccess.getListGroupForSearch(model).ToList();
if (getListGroup.Count > 0)
{
model.GROUP_CD_LIST = string.Join("','", getListGroup.ToArray());
}
}
IEnumerable <MstGroupArea> results = dataAccess.SearchGroupAreatList(dt, model, out total_row);
return(results);
}
public ActionResult GroupList()
{
CmnEntityModel currentUser = Session["CmnEntityModel"] as CmnEntityModel;
var authorityList = currentUser.USER_AUTHORITY;
if (currentUser == null || authorityList != 2)
{
return(RedirectToAction("Login", "UserAccount"));
}
GroupAreaModel model = new GroupAreaModel();
CommonService comService = new CommonService();
var tmpCondition = GetRestoreData() as GroupAreaModel;
if (tmpCondition != null)
{
model = tmpCondition;
}
model.CITY_LIST = comService.GetCityList().ToList().Select(
f => new SelectListItem
{
Value = f.CITY_CD.ToString(),
Text = f.CITY_NAME
}).ToList();
model.CITY_LIST.Insert(0, new SelectListItem {
Value = Constant.DEFAULT_VALUE, Text = ""
});
model.DISTRICT_LIST = comService.GetDistrictList().ToList().Select(
f => new SelectListItem
{
Value = f.CITY_CD.ToString() + "_" + f.DISTRICT_CD.ToString(),
Text = f.DISTRICT_NAME
}).ToList();
model.TOWN_LIST = comService.GetTownList().ToList().Select(
f => new SelectListItem
{
Value = f.CITY_CD.ToString() + "_" + f.DISTRICT_CD.ToString() + "_" + f.TOWN_CD.ToString(),
Text = f.TOWN_NAME
}).ToList();
ViewBag.GetGroupForUser = new SelectList(UtilityServices.UtilityServices.GetGroupForUser(), "Value", "Text");
return(View(model));
}
public ActionResult GroupList(DataTableModel dt, GroupAreaModel condition)
{
if (ModelState.IsValid)
{
using (ManageDistrictService service = new ManageDistrictService())
{
int total_row = 0;
//condition = Session["SearchGroupAreaList"] as GroupAreaModel ?? new GroupAreaModel();
var dataList = service.SearchGroupAreatList(dt, condition, out total_row);
int order = 1;
int totalRowCount = dataList.Count();
int lastItem = dt.iDisplayLength + dt.iDisplayStart;
var result = Json(
new
{
sEcho = dt.sEcho,
iTotalRecords = total_row,
iTotalDisplayRecords = total_row,
aaData = (from i in dataList
select new object[]
{
i.GROUP_CD,
order++,
i.GROUP_NAME != null ? HttpUtility.HtmlEncode(i.GROUP_NAME) : String.Empty,
i.FOR_USER_TEXT = GroupForUser.Items[i.FOR_USER].ToString(),
i.INS_DATE != null ? i.INS_DATE.Value.ToString("dd/MM/yyyy") : String.Empty,
i.UPD_DATE != null ? i.UPD_DATE.Value.ToString("dd/MM/yyyy") : String.Empty,
i.DEL_FLG,
i.FOR_USER
})
});
SaveRestoreData(condition);
result.MaxJsonLength = Int32.MaxValue;
return(result);
}
}
return(new EmptyResult());
}
public ActionResult EditGroup(GroupAreaModel model)
{
try
{
using (ManageDistrictService service = new ManageDistrictService())
{
if (ModelState.IsValid)
{
bool isNew = false;
bool success = true;
MstDistrict entity = new MstDistrict();
if (model.GROUP_CD == 0)
{
isNew = true;
var groupCd = service.InsertGroupArea(model);
if (groupCd > 0)
{
model.GROUP_CD = groupCd;
foreach (var town in model.LIST_TOWN)
{
town.GROUP_CD = groupCd;
var updateTown = service.UpdateTownInArea(town, model.FOR_USER);
success = updateTown > 0 ? true : false;
if (!success)
{
break;
}
}
}
else
{
success = false;
}
JsonResult result = Json(new { isNew = isNew, success = success }, JsonRequestBehavior.AllowGet);
return(result);
}
else
{
isNew = false;
var update = service.UpdateGroupArea(model);
if (update > 0)
{
foreach (var town in model.LIST_TOWN)
{
town.GROUP_CD = model.GROUP_CD;
var updateTown = service.UpdateTownInArea(town, model.FOR_USER);
success = updateTown > 0 ? true : false;
if (!success)
{
break;
}
}
}
else
{
success = false;
}
JsonResult result = Json(new { isNew = isNew, success = success }, JsonRequestBehavior.AllowGet);
return(result);
}
}
else
{
var ErrorMessages = ModelState.Where(x => x.Value.Errors.Count > 0).Select(x => new { x.Key, x.Value.Errors }).ToArray();
}
return(new EmptyResult());
}
}
catch (Exception ex)
{
Response.StatusCode = (int)System.Net.HttpStatusCode.BadRequest;
System.Web.HttpContext.Current.Session["ERROR"] = ex;
return(new EmptyResult());
}
}
public ActionResult GroupDistrictEdit(long GroupCd = 0)
{
CmnEntityModel currentUser = Session["CmnEntityModel"] as CmnEntityModel;
var authorityList = currentUser != null ? currentUser.USER_AUTHORITY : 0;
if (currentUser == null || authorityList != 2)
{
return(RedirectToAction("Login", "UserAccount"));
}
GroupAreaModel model = new GroupAreaModel();
CommonService comService = new CommonService();
ManageDistrictDa dataAccess = new ManageDistrictDa();
model.LIST_TOWN = new List <GroupTown>();
if (GroupCd > 0)
{
model = dataAccess.getInfoGroupArea(GroupCd);
var listTown = dataAccess.GetListTownInArea(model);
if (listTown.Count() > 0)
{
model.LIST_TOWN = listTown.ToList();
foreach (var Town in model.LIST_TOWN)
{
if (model.FOR_USER == GroupForUser.Receive)
{
Town.DSP_ORDER = Town.DSP_ORDER_RECEIVE;
Town.GROUP_CD = Town.GROUP_CD_RECEIVE;
}
else
{
Town.DSP_ORDER = Town.DSP_ORDER_SENDER;
Town.GROUP_CD = Town.GROUP_CD_SENDER;
}
}
model.LIST_TOWN = model.LIST_TOWN.OrderBy(i => i.DSP_ORDER).ToList();
}
model.FOR_USER_TEXT = GroupForUser.Items[model.FOR_USER].ToString();
}
model.CITY_LIST = comService.GetCityList().ToList().Select(
f => new SelectListItem
{
Value = f.CITY_CD.ToString(),
Text = f.CITY_NAME
}).ToList();
//model.CITY_LIST.Insert(0, new SelectListItem { Value = Constant.DEFAULT_VALUE, Text = "" });
model.DISTRICT_LIST = comService.GetDistrictList().ToList().Select(
f => new SelectListItem
{
Value = f.CITY_CD.ToString() + "_" + f.DISTRICT_CD.ToString(),
Text = f.DISTRICT_NAME
}).ToList();
model.TOWN_LIST = comService.GetTownList().ToList().Select(
f => new SelectListItem
{
Value = f.CITY_CD.ToString() + "_" + f.DISTRICT_CD.ToString() + "_" + f.TOWN_CD.ToString(),
Text = f.TOWN_NAME
}).ToList();
ViewBag.GetGroupForUser = new SelectList(UtilityServices.UtilityServices.GetGroupForUser(), "Value", "Text");
return(View(model));
}
