public ActionResult GetPaymentDetailMoe(string model)
{
try
{
var filters = JsonConvert.DeserializeObject <List <FilterModel> >(model);
List <SupplierPaymentRequestDetail> lstPayment = new List <SupplierPaymentRequestDetail>();
var zonedataDic = new Dictionary <string, SupplierDetailZoneLevel>();
var year = filters.Select(x => x.Year).FirstOrDefault();
var month = filters.Select(x => x.Month).FirstOrDefault();
using (var conn = new SqlConnection(_strConnection))
using (var cmd = conn.CreateCommand())
{
conn.Open();
foreach (var filter in filters)
{
cmd.CommandText =
$"EXEC GET_SupplerPaymentDetails {filter.Year},'{filter.Month}',{filter.ProvinceId},{false}";
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
SupplierPaymentRequestDetail row = new SupplierPaymentRequestDetail();
row.CensorsID = Convert.ToString(dr["CensusID"]);
row.SupplierId = Convert.ToInt32(dr["SupplierId"]);
row.SupplierName = Convert.ToString(dr["SupplierName"]);
row.Amount = Convert.ToDecimal(dr["Amount"]);
row.SupplierPaymentReqHeaderId = Convert.ToDecimal(dr["supplierPaymentReq_HeaderId"]);
row.BankAccountNo = Convert.ToString(dr["BankAccountNo"]);
row.BankName = Convert.ToString(dr["BankName"]);
row.BranchName = Convert.ToString(dr["BranchName"]);
row.BankCode = Convert.ToString(dr["BankCode"]);
row.BranchCode = Convert.ToString(dr["BranchCode"]);
row.ZoneName = Convert.ToString(dr["ZoneName"]);
row.ProvinceName = Convert.ToString(dr["ProvinceName"]);
row.ProvinceId = Convert.ToInt32(dr["ProvinceId"]);
row.SupplierPaymentReqDetailId = Convert.ToInt32(dr["SupplierPaymentReq_DetailId"]);
lstPayment.Add(row);
}
}
var provinceGroup = lstPayment.GroupBy(x => x.ProvinceName);
var returnModel = new SupplierDetailPayModel();
returnModel.FullTotal = lstPayment.Sum(x => x.Amount);
foreach (var prov in provinceGroup)
{
var provinceLevel = new SupplierDetailProvinceLevel();
provinceLevel.ProvincialTotal = prov.Sum(x => x.Amount);
provinceLevel.ProvinceName = prov.Key;
var zoneGroup = prov.GroupBy(x => x.ZoneName);
foreach (var zone in zoneGroup)
{
var zoneLevel = new SupplierDetailZoneLevel();
zoneLevel.ZoneTotal = zone.Sum(x => x.Amount);
zoneLevel.ZoneName = zone.Key;
var details = zone.ToList().Select(c => new SupplierPaymentRequestDetailMoe()
{
Amount = c.Amount,
BankName = c.BankName,
BankCode = c.BankCode,
BranchCode = c.BranchCode,
BranchName = c.BranchName,
SupplierName = c.SupplierName,
BankAccountNo = c.BankAccountNo,
CensorsId = c.CensorsID,
Year = year,
Month = month,
ProvinceId = c.ProvinceId,
SupplierId = c.SupplierId,
SupplierPaymentReqHeaderId = c.SupplierPaymentReqHeaderId,
SupplierPaymentReqDetailId = c.SupplierPaymentReqDetailId
}).ToList();
zoneLevel.SupplierPaymentRequestDetails = details;
provinceLevel.SupplierDetailZoneLevel.Add(zoneLevel);
zonedataDic.Add(zone.Key, zoneLevel);
}
returnModel.Detail.Add(provinceLevel);
}
ViewBag.TableData = zonedataDic;
return(PartialView("_SupplierPaymentMOEGroupLevel", returnModel));
}
}
catch (Exception e)
{
return(Json(new { status = false }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult SupplierPaymentMoePdfGenerate(string model)
{
var filters = JsonConvert.DeserializeObject <List <FilterModel> >(model);
List <SupplierPaymentRequestDetail> lstPayment = new List <SupplierPaymentRequestDetail>();
var year = filters.Select(x => x.Year).FirstOrDefault();
var month = filters.Select(x => x.Month).FirstOrDefault();
using (var conn = new SqlConnection(_strConnection))
using (var cmd = conn.CreateCommand())
{
conn.Open();
foreach (var filter in filters)
{
cmd.CommandText = $"EXEC GET_SupplerPaymentDetails {filter.Year},'{filter.Month}',{filter.ProvinceId},{true}";
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
SupplierPaymentRequestDetail row = new SupplierPaymentRequestDetail();
row.CensorsID = Convert.ToString(dr["CensusID"]);
row.SupplierId = Convert.ToInt32(dr["SupplierId"]);
row.SupplierName = Convert.ToString(dr["SupplierName"]);
row.Amount = Convert.ToDecimal(dr["Amount"]);
row.SupplierPaymentReqHeaderId = Convert.ToDecimal(dr["supplierPaymentReq_HeaderId"]);
row.BankAccountNo = Convert.ToString(dr["BankAccountNo"]);
row.BankName = Convert.ToString(dr["BankName"]);
row.BranchName = Convert.ToString(dr["BranchName"]);
row.BankCode = Convert.ToString(dr["BankCode"]);
row.ZoneName = Convert.ToString(dr["ZoneName"]);
row.ProvinceName = Convert.ToString(dr["ProvinceName"]);
lstPayment.Add(row);
}
}
var provinceGroup = lstPayment.GroupBy(x => x.ProvinceName);
var returnModel = new SupplierDetailPayModel();
returnModel.FullTotal = lstPayment.Sum(x => x.Amount);
foreach (var prov in provinceGroup)
{
var provinceLevel = new SupplierDetailProvinceLevel();
provinceLevel.ProvincialTotal = prov.Sum(x => x.Amount);
provinceLevel.ProvinceName = prov.Key;
var zoneGroup = prov.GroupBy(x => x.ZoneName);
foreach (var zone in zoneGroup)
{
var zoneLevel = new SupplierDetailZoneLevel();
zoneLevel.ZoneTotal = zone.Sum(x => x.Amount);
zoneLevel.ZoneName = zone.Key;
var details = zone.ToList().Select(c => new SupplierPaymentRequestDetailMoe()
{
Amount = c.Amount,
BankName = c.BankName,
BankCode = c.BankCode,
BranchName = c.BranchName,
SupplierName = c.SupplierName,
BankAccountNo = c.BankAccountNo,
CensorsId = c.CensorsID,
Year = year,
Month = month,
Paid = "Paid"
}).ToList();
zoneLevel.SupplierPaymentRequestDetails = details;
provinceLevel.SupplierDetailZoneLevel.Add(zoneLevel);
}
returnModel.Detail.Add(provinceLevel);
}
return(new Rotativa.PartialViewAsPdf("_SupplierPaymentMOEPdfView", returnModel)
{
FileName = $"SupplierPayment_DetailsMOE_{year}_{month}.pdf"
});
}
}