コード例 #1
0
ファイル: CNGGAS.ashx.cs プロジェクト: jxsgood/WEBCNG
        /// <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);
        }
コード例 #2
0
ファイル: CNGGAS.ashx.cs プロジェクト: jxsgood/WEBCNG
        /// <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;
        }