/// <summary>
/// 统计月度数据
/// </summary>
/// <param name="context"></param>
public void CountYue(HttpContext context)
{
string year = context.Request["year"];
string yue = context.Request["yue"];
string retext = "{{ \"ok\":{0},\"msg\":\"{1}\",\"d\":@替换对象@ }}";
bool ok = false;
string mesg = "";
DateTime str = new DateTime();//起始日期
DateTime end = new DateTime();//结束日期
DateTime day = DateTime.Parse(year + "-" + yue + "-1");
RetTjQuJian(day, ref str, ref end);
ok = prScDate(str, end, ref mesg);//检查站点数据是否上传
if (ok)
{
try
{
CountCWGas Cw = new CountCWGas(str, end);//创建财务报表统计
Cw.year = int.Parse(year);//设置统计月度
Cw.yue = int.Parse(yue);
Cw.getCWData();//生成财务数据
System.Threading.Thread.Sleep(1000);//延时1秒
CreateCWExcel(year, yue, context, Cw);//生成财务报表Excel pdf swf文档
CountYueGas TJ = new CountYueGas(str, end);//创建月度统计对象
TJ.year = int.Parse(year);//设置统计月度
TJ.yue = int.Parse(yue);
TJ.CountBzDay();//开始统计
CreateYueExcl(year, yue, context, TJ);//生成Excel 和 Html 的月报表
ok = true;
mesg = "数据已经重新生成完毕！";
}
catch (Exception e)
{
ok = false;
mesg = e.Message + "<br/><span style='font-size: 18; color: #FF0000'>请检查站点上报数据是否完整</span>";
}
}
retext = string.Format(retext, ok.ToString().ToLower(), mesg);
retext = retext.Replace("@替换对象@", "{}");
Sys.DebugMes(retext);
context.Response.Write(retext);
}
/// <summary>
/// 创建 财务附表表Excel
/// </summary>
private bool CreateCWExcel(string year, string yue, HttpContext context, CountCWGas Cw)
{
string template = HttpContext.Current.Server.MapPath("~/CNGGAS/BB/template/单位统计模板.xls");//模板文件
string newFile = HttpContext.Current.Server.MapPath("~/CNGGAS/BB/" + year + "/" + yue + "/财务附表.xls");//报表文件文件
string pdfFile = HttpContext.Current.Server.MapPath("~/CNGGAS/BB/" + year + "/" + yue + "/财务附表.pdf");//报表文件文件
string swfFile = HttpContext.Current.Server.MapPath("~/CNGGAS/BB/" + year + "/" + yue + "/财务附表.swf");//报表文件文件
string viewPaht = HttpContext.Current.Server.MapPath("~/CNGGAS/BB/" + year + "/" + yue);//报表文件文件目录
if (System.IO.Directory.Exists(viewPaht) == false)
{
FileManager.CreateFolder(viewPaht);
}
bool ok = FileManager.CopyFile(template, newFile);
if (ok == false)
{
return false;
}
MyExcel ME = new MyExcel();
ME.Open(newFile);
#region 第一步 IC卡充值,消费 写入
ME.ActivateSheet("IC卡消费、充值统计");
string txt = string.Format("系统统计时间：{0} 当班(白班)接班 至 {1} 当班(夜班)交班", Cw.Str.ToShortDateString(),
Cw.End.ToShortDateString());
ME.WriteStr(txt, 3, 1);//时间范围
txt = string.Format("系统统计时间：{0} 至 {1} ", Cw.Str.ToShortDateString(),
Cw.End.ToShortDateString());
ME.WriteStr(txt, 33, 1);//时间范围
foreach (CW_zdGunYuGouMod my in Cw.myGunYuGou)
{
string sheetName = "";
int row = 0;//储存底数报表对应行号
ME.ActivateSheet("IC卡消费、充值统计");
if (my.Zd == 2)
{
sheetName = "月度汇总(南高)";
row = 10;
ME.WriteStr(my.充值金额.ToString(), 36, 5);//充值金额
ME.WriteStr(Cw.Str.ToShortDateString() + " 0:00:00", 36, 3);//起始时间
ME.WriteStr(Cw.End.ToShortDateString() + " 23:59:59", 36, 4);//结束时间
}
if (my.Zd == 3)
{
row = 17;
sheetName = "月度汇总(西路)";
ME.WriteStr(my.充值金额.ToString(), 37, 5);//充值金额
ME.WriteStr(Cw.Str.ToShortDateString() + " 0:00:00", 37, 3);//起始时间
ME.WriteStr(Cw.End.ToShortDateString() + " 23:59:59", 37, 4);//结束时间
}
if (my.Zd == 4)
{
row = 5;
sheetName = "月度汇总(麻柳沱)";
ME.WriteStr(my.充值金额.ToString(), 35, 5);//充值金额
ME.WriteStr(Cw.Str.ToShortDateString() + " 0:00:00", 35, 3);//起始时间
ME.WriteStr(Cw.End.ToShortDateString() + " 23:59:59", 35, 4);//结束时间
}
//每枪 预购金额
for (int i = 0; i < my.Guns; i++)
{
ME.WriteStr(my.GunYuGou[i].GunStr, row + i, 3);//起始时间
ME.WriteStr(my.GunYuGou[i].GunEnd, row + i, 4);//结束时间
ME.WriteStr(my.GunYuGou[i].预购金额.ToString(), row + i, 5);//预购金额
}
}
#endregion
#region 第二部 公交车数据写入
foreach (CW_zdGunYuGouMod my in Cw.myGunYuGou)
{
ME.ActivateSheet("公交月度");
if (my.Zd == 2)
{
continue;
}
if (my.Zd == 3 || my.Zd == 4)
{
for (int i = 0; i < my.GongJiao.Length; i++)
{
int col = 0;
if (my.Zd == 3)
{
ME.WriteStr(my.GongJiao[i].日期, 5 + i, 1);//写入日期
ME.WriteStr(my.GongJiao[i].手工记账.ToString(), 5 + i, 2);//写入手工记账
ME.WriteStr(my.GongJiao[i].刷卡记账.ToString(), 5 + i, 3);//写入手工记账
ME.WriteStr(my.GongJiao[i].停业时间.ToString(), 5 + i, 5);//写入手工记账
}
if (my.Zd == 4)
{
ME.WriteStr(my.GongJiao[i].手工记账.ToString(), 5 + i, 6);//写入手工记账
ME.WriteStr(my.GongJiao[i].刷卡记账.ToString(), 5 + i, 7);//写入手工记账
ME.WriteStr(my.GongJiao[i].停业时间.ToString(), 5 + i, 9);//写入手工记账
}
}
}
}
ME.WriteStr(Cw.Days.ToString(), 43, 2);//写入天数
#endregion
#region 第三步 记账单位合计数写入 , 记账单位 分站合计数
string sheet = "";
CW_月度统计Mod d = null;
CW_JiZhangDanWen[] jz = null;
for (int i = 0; i < 4; i++)
{
switch (i)
{
case 0: { sheet = "月度汇总"; d = Cw.月度汇总; jz = Cw.记账单位合计; } break;//总表合计
case 1: { sheet = "月度汇总(南高)"; d = Cw.myData[0]; jz = Cw.myGunYuGou[0].JiZhang; } break;//南高
case 2: { sheet = "月度汇总(西路)"; d = Cw.myData[1]; jz = Cw.myGunYuGou[1].JiZhang; } break;//西路
case 3: { sheet = "月度汇总(麻柳沱)"; d = Cw.myData[2]; jz = Cw.myGunYuGou[2].JiZhang; } break;//麻柳沱
}
ME.ActivateSheet(sheet);
ME.WriteStr(d.预购刷卡气量.ToString(), 4, 4);
ME.WriteStr(d.刷卡金额.ToString(), 4, 5);
ME.WriteStr(d.交款气量.ToString(), 4, 6);
ME.WriteStr(d.交款金额.ToString(), 4, 7);
for (int j = 0; j < jz.Length; j++)
{
ME.WriteStr(jz[j].手工.ToString(), 4 + (j + 1) * 2, 3);
ME.WriteStr(jz[j].刷卡.ToString(), 5 + (j + 1) * 2, 3);
if (jz[j].单位名称 == "公交公司")
{
continue;
}
ME.WriteStr(jz[j].价格.ToString(), 4 + (j + 1) * 2, 5);
}
}
#endregion
#region 第四步 记账单位表统计
string SQL = @"SELECT a.[日期]
,a.[记账单位名称]
,sum(a.[刷卡记账气量]) as 刷卡
,sum(a.[手工记账气量]) as 手工
,b.编号
FROM [CNGQT].[dbo].[SC_记账明细] a LEFT JOIN cngqt.dbo.SC_记账单位名称 b
on b.记账单位名称=a.[记账单位名称]
where 日期 between '@起始日期@' and '@结束日期@'
group by a.日期,a.记账单位名称,b.编号 order by 日期";
SQL = SQL.Replace("@起始日期@", Cw.Str.ToShortDateString());
SQL = SQL.Replace("@结束日期@", Cw.End.ToShortDateString());
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL);
ME.ActivateSheet("记账单位统计表");
// ME.WriteStr(Cw.Days.ToString(), 33, 5); //插入统计天数
for (int i = 0; i < Cw.Days; i++)//写入日期标题)
{
string dtext = Cw.Str.AddDays(i).ToString("MM月dd日");
ME.WriteStr(dtext, 1, 3 + i);
}
foreach (DataRow row in dt.Rows)
{
DateTime rq = DateTime.Parse(row["日期"].ToString());
int col = (rq - Cw.Str).Days;//取日期间隔 确定列数
int r = int.Parse(row["编号"].ToString());
double sk = double.Parse(row["刷卡"].ToString());
double sg = double.Parse(row["手工"].ToString());
ME.WriteStr(sk.ToString(), r * 2 + 1, 3 + col);
ME.WriteStr(sg.ToString(), r * 2, 3 + col);
}
ME.HideSheet("记账单位统计表");
#endregion
#region 最后一步:生成 excel,pdf,Swf 文档
ME.Save();
ME.SavePdf(pdfFile);//保存为pdf文档
Sys.Pdf2Swf(pdfFile, swfFile);//生成 swf文件
//转换为图片
//myConvert.ConvertPDF2Image(viewPaht + "\\" + "me.pdf", viewPaht + "\\", "me", 1, gg,
// ImageFormat.Jpeg, myConvert.pdfType.Two);
ME.Save();
ME.Close();
#endregion
return true;
}