public string GetChargeReport(GetChargeReportReq req)
{
switch (req.type)
{
case "year":
req.date = new DateTime(Convert.ToInt32(req.iYear), 1, 1, 0, 0, 0).ToString("yyyy-MM-dd HH:mm:ss");
break;
case "month":
var arr = req.iMonth.Split('-');
req.date = new DateTime(Convert.ToInt32(arr[0]), Convert.ToInt32(arr[1]), 1, 0, 0, 0).ToString("yyyy-MM-dd HH:mm:ss");
break;
}
req.date = req.date ?? DateTime.Now.ToString("yyyy-MM-dd");
var user = JsonConvert.DeserializeObject <AllInOne_UserInfo>(Session["user"].ToString());
req.lastName = req.lastName ?? user.name;
//return null;
var r = AllFunc.Instance.GetChargeLine(req);
if (r.HttpCode == 200)
{
Session["gcrReq"] = JsonConvert.SerializeObject(req);
}
return(JsonConvert.SerializeObject(r));
}
public string GetChargePieByType(GetChargeReportReq req)
{
//req.date = "2016-08-01";
//req.customerNo = "0000000681";
switch (req.type)
{
case "year":
req.date = new DateTime(Convert.ToInt32(req.iYear), 1, 1, 0, 0, 0).ToString("yyyy-MM-dd HH:mm:ss");
break;
case "month":
var arr = req.iMonth.Split('-');
req.date = new DateTime(Convert.ToInt32(arr[0]), Convert.ToInt32(arr[1]), 1, 0, 0, 0).ToString("yyyy-MM-dd HH:mm:ss");
break;
}
var user = JsonConvert.DeserializeObject <AllInOne_UserInfo>(Session["user"].ToString());
req.lastName = req.lastName ?? user.name;
var r = AllFunc.Instance.GetChargePieByType(req);
return(JsonConvert.SerializeObject(r));
}
/// <summary>
/// 圆饼 用户类型
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public List <PieRes> GetPieListByCustType(GetChargeReportReq req)
{
var r = new List <PieRes>();//民用，公建，工业
var date = Convert.ToDateTime(req.date);
var customerNo = req.customerNo;
var year = date.Year;
var month = date.Month;
var day = date.Day;
var type = req.type;
string str = "";
switch (type)
{
case "year":
var newDate = new DateTime(year, 1, 1);
str = $@"select r.CustTypeName as name,sum(r.chargeVolume)as value from
(select * from ICChargeRecordCustTypeView where chargeTime>'{newDate}' and chargeTime<'{newDate.AddYears(1)}'and customerNo='{customerNo}')r
GROUP BY r.CustTypeName";
break;
case "month":
var newDate2 = new DateTime(year, month, 1);
str = $@"select r.CustTypeName as name,sum(r.chargeVolume)as value from
(select * from ICChargeRecordCustTypeView where chargeTime>'{newDate2}' and chargeTime<'{newDate2.AddMonths(1)}'and customerNo='{customerNo}')r
GROUP BY r.CustTypeName";
break;
case "day":
str = $@"select r.CustTypeName as name,sum(r.chargeVolume)as value from
(select * from ICChargeRecordCustTypeView where chargeTime>'{date}' and chargeTime<'{date.AddDays(1)}'and customerNo='{customerNo}')r
GROUP BY r.CustTypeName";
break;
}
r = SqlHelper.Instance.ExecuteGetDt <PieRes>(str, new Dictionary <string, string>());
r = CompleteListForType(r);
return(r);
}
/// <summary>
/// 大范围统计，进来lastName就一定有了，不判断了
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public List <chargeReport> GetLineListForCompany(GetChargeReportReq req)
{
var cid = AllInOne_UserInfoOper.Instance.GetLastCIdByName(req.lastName);
var date = Convert.ToDateTime(req.date);
var customerNo = req.customerNo;
var year = date.Year;
var month = date.Month;
var day = date.Day;
var type = req.type;
var str = "";
if (type == "year")
{
for (int i = 1; i < 13; i++)
{
var newDate = new DateTime(year, i, 1);
if (i == 1)
{
str = $@"SELECT
'{newDate.ToString("yyyy-MM-dd HH:mm:ss")}' AS dt,
isnull(SUM(chargeVolume), 0) AS sumVolume
,isnull(sum(chargeMoney),0)as sumMoney
FROM
ICChargeRecordCustTypeView
WHERE
chargeTime < '{newDate.AddMonths(1)}'
AND chargeTime >= '{newDate}'
AND (cId1='{cid}' or cId2='{cid}' or cId3='{cid}' or cId4='{cid}' )";
if (customerNo != "0")
{
str += $" and customerNo='{customerNo}' ";
}
}
else
{
str += $@" union SELECT
'{newDate.ToString("yyyy-MM-dd HH:mm:ss")}' AS dt,
isnull(SUM(chargeVolume), 0) AS sumVolume
,isnull(sum(chargeMoney),0)as sumMoney
FROM
ICChargeRecordCustTypeView
WHERE
chargeTime < '{newDate.AddMonths(1)}'
AND chargeTime >= '{newDate}'
AND (cId1='{cid}' or cId2='{cid}' or cId3='{cid}' or cId4='{cid}' )";
if (customerNo != "0")
{
str += $" and customerNo='{customerNo}' ";
}
}
}
}
else if (type == "month")
{
var days = DateTime.DaysInMonth(year, month);
for (int i = 1; i < days + 1; i++)
{
var newDate = new DateTime(year, month, i);
if (i == 1)
{
str = $@"SELECT
'{newDate.ToString("yyyy-MM-dd HH:mm:ss")}' AS dt,
isnull(SUM(chargeVolume), 0) AS sumVolume
,isnull(sum(chargeMoney),0)as sumMoney
FROM
ICChargeRecordCustTypeView
WHERE
chargeTime < '{newDate.AddDays(1)}'
AND chargeTime >= '{newDate}'
AND (cId1='{cid}' or cId2='{cid}' or cId3='{cid}' or cId4='{cid}' )";
if (customerNo != "0")
{
str += $" and customerNo='{customerNo}' ";
}
}
else
{
str += $@" union SELECT
'{newDate.ToString("yyyy-MM-dd HH:mm:ss")}' AS dt,
isnull(SUM(chargeVolume), 0) AS sumVolume
,isnull(sum(chargeMoney),0)as sumMoney
FROM
ICChargeRecordCustTypeView
WHERE
chargeTime < '{newDate.AddDays(1)}'
AND chargeTime >= '{newDate}'
AND (cId1='{cid}' or cId2='{cid}' or cId3='{cid}' or cId4='{cid}' )";
if (customerNo != "0")
{
str += $" and customerNo='{customerNo}' ";
}
}
}
}
else if (type == "day")
{
var hour = Convert.ToInt32(req.startTime);
for (int i = hour; i < hour + 24; i++)
{
var newDate = new DateTime();
if (i > 23)
{
newDate = new DateTime(year, month, day, 0, 0, 0).AddDays(1).AddHours(i - 24);
//newDate = new DateTime(year, month, day, i, 0, 0);
}
else
{
newDate = new DateTime(year, month, day, i, 0, 0);
}
if (i == hour)
{
str = $@"SELECT
'{newDate.ToString("yyyy-MM-dd HH:mm:ss")}' AS dt,
isnull(SUM(chargeVolume), 0) AS sumVolume
,isnull(sum(chargeMoney),0)as sumMoney
FROM
ICChargeRecordCustTypeView
WHERE
chargeTime < '{newDate.AddHours(1)}'
AND chargeTime >= '{newDate}'
AND (cId1='{cid}' or cId2='{cid}' or cId3='{cid}' or cId4='{cid}' )";
if (customerNo != "0")
{
str += $" and customerNo='{customerNo}' ";
}
}
else
{
str += $@" union SELECT
'{newDate.ToString("yyyy-MM-dd HH:mm:ss")}' AS dt,
isnull(SUM(chargeVolume), 0) AS sumVolume
,isnull(sum(chargeMoney),0)as sumMoney
FROM
ICChargeRecordCustTypeView
WHERE
chargeTime < '{newDate.AddHours(1)}'
AND chargeTime >= '{newDate}'
AND (cId1='{cid}' or cId2='{cid}' or cId3='{cid}' or cId4='{cid}' )";
if (customerNo != "0")
{
str += $" and customerNo='{customerNo}' ";
}
}
}
}
var list = SqlHelper.Instance.ExecuteGetDt <chargeReport>(str, new Dictionary <string, string>());
return(list);
}
public List <PieRes> GetPieListByTimeForCompany(GetChargeReportReq req)
{
var cid = AllInOne_UserInfoOper.Instance.GetLastCIdByName(req.lastName);
var date = Convert.ToDateTime(req.date);
var customerNo = req.customerNo;
var year = date.Year;
var month = date.Month;
var day = date.Day;
var type = req.type;
var str = "";
if (type == "year")
{
for (int i = 1; i < 13; i++)
{
var newDate = new DateTime(year, i, 1);
if (i == 1)
{
str = $"select '{newDate.ToString("yyyy-MM")}' as name,isnull(sum(chargeVolume),0)as value from ICChargeRecordCustTypeView where chargeTime<'{newDate.AddMonths(1)}' and chargeTime>='{newDate}' and (cId1='{cid}' or cId2='{cid}' or cId3='{cid}' or cId4='{cid}' )";
if (customerNo != "0")
{
str += $" and customerNo='{customerNo}' ";
}
}
else
{
str += $" union select '{newDate.ToString("yyyy-MM")}' as name,isnull(sum(chargeVolume),0)as value from ICChargeRecordCustTypeView where chargeTime<'{newDate.AddMonths(1)}' and chargeTime>='{newDate}' and (cId1='{cid}' or cId2='{cid}' or cId3='{cid}' or cId4='{cid}' )";
if (customerNo != "0")
{
str += $" and customerNo='{customerNo}' ";
}
}
}
}
else if (type == "month")
{
var days = DateTime.DaysInMonth(year, month);
for (int i = 1; i < days + 1; i++)
{
var newDate = new DateTime(year, month, i);
if (i == 1)
{
str = $"select '{newDate.ToString("dd号")}' as name,isnull(sum(chargeVolume),0)as value from ICChargeRecordCustTypeView where chargeTime<'{newDate.AddDays(1)}' and chargeTime>='{newDate}' and (cId1='{cid}' or cId2='{cid}' or cId3='{cid}' or cId4='{cid}' )";
if (customerNo != "0")
{
str += $" and customerNo='{customerNo}' ";
}
}
else
{
str += $" union select '{newDate.ToString("dd号")}' as name,isnull(sum(chargeVolume),0)as value from ICChargeRecordCustTypeView where chargeTime<'{newDate.AddDays(1)}' and chargeTime>='{newDate}' and (cId1='{cid}' or cId2='{cid}' or cId3='{cid}' or cId4='{cid}' )";
if (customerNo != "0")
{
str += $" and customerNo='{customerNo}' ";
}
}
}
}
else if (type == "day")
{
var hour = Convert.ToInt32(req.startTime);
for (int i = hour; i < hour + 24; i = i + 2)
{
var newDate = new DateTime();
if (i > 23)
{
newDate = new DateTime(year, month, day, 0, 0, 0).AddDays(1).AddHours(i - 24);
//newDate = new DateTime(year, month, day, i, 0, 0);
}
else
{
newDate = new DateTime(year, month, day, i, 0, 0);
}
var lastHour = i + 2;
if (lastHour > 24)
{
lastHour -= 24;
}
if (i == hour)
{
str = $"select '{i}时-{lastHour}时' as name,isnull(sum(chargeVolume),0)as value from ICChargeRecordCustTypeView where chargeTime<'{newDate.AddHours(1)}' and chargeTime>='{newDate}' and (cId1='{cid}' or cId2='{cid}' or cId3='{cid}' or cId4='{cid}' )";
if (customerNo != "0")
{
str += $" and customerNo='{customerNo}' ";
}
}
else
{
str += $" union select '{i}时-{lastHour}时' as name,isnull(sum(chargeVolume),0)as value from ICChargeRecordCustTypeView where chargeTime<'{newDate.AddHours(1)}' and chargeTime>='{newDate}' and (cId1='{cid}' or cId2='{cid}' or cId3='{cid}' or cId4='{cid}' )";
if (customerNo != "0")
{
str += $" and customerNo='{customerNo}' ";
}
}
}
}
var list = SqlHelper.Instance.ExecuteGetDt <PieRes>(str, new Dictionary <string, string>());
return(list);
}
public List <PieRes> GetPieListByCustTypeForCompany(GetChargeReportReq req)
{
var cid = AllInOne_UserInfoOper.Instance.GetLastCIdByName(req.lastName);
var r = new List <PieRes>();//民用，公建，工业
var date = Convert.ToDateTime(req.date);
var customerNo = req.customerNo;
var year = date.Year;
var month = date.Month;
var day = date.Day;
var type = req.type;
string str = "";
switch (type)
{
case "year":
var newDate = new DateTime(year, 1, 1);
// str = $@"select r.CustTypeName as name,sum(r.chargeVolume)as value from
//(select * from ICChargeRecordCustTypeView where chargeTime>'{newDate}' and chargeTime<'{newDate.AddYears(1)}'and customerNo='{customerNo}')r
//GROUP BY r.CustTypeName";
str = $@"select r.CustTypeName as name,sum(r.chargeVolume)as value from
(select * from ICChargeRecordCustTypeView where chargeTime>'{newDate}' and chargeTime<'{newDate.AddYears(1)}'and (cId1='{cid}' or cId2='{cid}' or cId3='{cid}' or cId4='{cid}') ";
if (customerNo != "0")
{
str += $" and customerNo='{customerNo}' ";
}
str += @")r
GROUP BY r.CustTypeName";
break;
case "month":
var newDate2 = new DateTime(year, month, 1);
// str = $@"select r.CustTypeName as name,sum(r.chargeVolume)as value from
//(select * from ICChargeRecordCustTypeView where chargeTime>'{newDate2}' and chargeTime<'{newDate2.AddMonths(1)}'and customerNo='{customerNo}')r
//GROUP BY r.CustTypeName";
str = $@"select r.CustTypeName as name,sum(r.chargeVolume)as value from
(select * from ICChargeRecordCustTypeView where chargeTime>'{newDate2}' and chargeTime<'{newDate2.AddMonths(1)}'and (cId1='{cid}' or cId2='{cid}' or cId3='{cid}' or cId4='{cid}')";
if (customerNo != "0")
{
str += $" and customerNo='{customerNo}' ";
}
str += @" )r
GROUP BY r.CustTypeName";
break;
case "day":
// str = $@"select r.CustTypeName as name,sum(r.chargeVolume)as value from
//(select * from ICChargeRecordCustTypeView where chargeTime>'{date}' and chargeTime<'{date.AddDays(1)}'and customerNo='{customerNo}')r
//GROUP BY r.CustTypeName";
str = $@"select r.CustTypeName as name,sum(r.chargeVolume)as value from
(select * from ICChargeRecordCustTypeView where chargeTime>'{date}' and chargeTime<'{date.AddDays(1)}'and (cId1='{cid}' or cId2='{cid}' or cId3='{cid}' or cId4='{cid}')";
if (customerNo != "0")
{
str += $" and customerNo='{customerNo}' ";
}
str += @" )r
GROUP BY r.CustTypeName";
break;
}
r = SqlHelper.Instance.ExecuteGetDt <PieRes>(str, new Dictionary <string, string>());
r = CompleteListForType(r);
return(r);
}
/// <summary>
/// 曲线图，柱形图。表格
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public List <chargeReport> GetLineList(GetChargeReportReq req)
{
var date = Convert.ToDateTime(req.date);
var customerNo = req.customerNo;
var year = date.Year;
var month = date.Month;
var day = date.Day;
var type = req.type;
var str = "";
if (type == "year")
{
for (int i = 1; i < 13; i++)
{
var newDate = new DateTime(year, i, 1);
if (i == 1)
{
str = $"select '{newDate.ToString("yyyy-MM-dd HH:mm:ss")}' as dt,isnull(sum(chargeVolume),0)as sumVolume,isnull(sum(chargeMoney),0)as sumMoney from ICChargeRecord where chargeTime<'{newDate.AddMonths(1)}' and chargeTime>='{newDate}' and customerNo='{customerNo}'";
}
else
{
str += $" union select '{newDate.ToString("yyyy-MM-dd HH:mm:ss")}' as dt,isnull(sum(chargeVolume),0)as sumVolume,isnull(sum(chargeMoney),0)as sumMoney from ICChargeRecord where chargeTime<'{newDate.AddMonths(1)}' and chargeTime>='{newDate}' and customerNo='{customerNo}'";
}
}
}
else if (type == "month")
{
var days = DateTime.DaysInMonth(year, month);
for (int i = 1; i < days + 1; i++)
{
var newDate = new DateTime(year, month, i);
if (i == 1)
{
str = $"select '{newDate.ToString("yyyy-MM-dd HH:mm:ss")}' as dt,isnull(sum(chargeVolume),0)as sumVolume,isnull(sum(chargeMoney),0)as sumMoney from ICChargeRecord where chargeTime<'{newDate.AddDays(1)}' and chargeTime>='{newDate}' and customerNo='{customerNo}'";
}
else
{
str += $" union select '{newDate.ToString("yyyy-MM-dd HH:mm:ss")}' as dt,isnull(sum(chargeVolume),0)as sumVolume,isnull(sum(chargeMoney),0)as sumMoney from ICChargeRecord where chargeTime<'{newDate.AddDays(1)}' and chargeTime>='{newDate}' and customerNo='{customerNo}'";
}
}
}
else if (type == "day")
{
var hour = Convert.ToInt32(req.startTime);
for (int i = hour; i < hour + 24; i++)
{
var newDate = new DateTime();
if (i > 23)
{
newDate = new DateTime(year, month, day, 0, 0, 0).AddDays(1).AddHours(i - 24);
//newDate = new DateTime(year, month, day, i, 0, 0);
}
else
{
newDate = new DateTime(year, month, day, i, 0, 0);
}
if (i == hour)
{
str = $"select '{newDate.ToString("yyyy-MM-dd HH:mm:ss")}' as dt,isnull(sum(chargeVolume),0)as sumVolume,isnull(sum(chargeMoney),0)as sumMoney from ICChargeRecord where chargeTime<'{newDate.AddHours(1)}' and chargeTime>='{newDate}' and customerNo='{customerNo}'";
}
else
{
str += $" union select '{newDate.ToString("yyyy-MM-dd HH:mm:ss")}' as dt,isnull(sum(chargeVolume),0)as sumVolume,isnull(sum(chargeMoney),0)as sumMoney from ICChargeRecord where chargeTime<'{newDate.AddHours(1)}' and chargeTime>='{newDate}' and customerNo='{customerNo}'";
}
}
}
var list = SqlHelper.Instance.ExecuteGetDt <chargeReport>(str, new Dictionary <string, string>());
return(list);
}
