Exemplo n.º 1
0
        /// <summary>
        /// 获取财务数据
        /// </summary>
        public void getCWData()
        {
            DAL dal = new DAL();

            #region 第一步 分站 统计 各项气量和金额  统计 月度汇总气量
            //分站统计
            string SQL = @"SELECT [站点]
                                                  ,sum([生产气量]) as 生产合计
                                                  ,sum([预购刷卡气量]) as 刷卡气量合计
                                                  ,sum([记账刷卡气量]) as 刷卡记账合计
                                                  ,sum([手工记账气量]) as 手工记账合计
                                                  ,sum([现金消费气量]) as 现金气量合计
                                                  ,sum([现金消费金额]) as 现金合计
                                                  ,sum([报损气量]) as 报损合计
                                              FROM [CNGQT].[dbo].[SC_班销售统计]
                                            where 日期 between '@起始日期@' and '@结束日期@' group by 站点 order by 站点";
            SQL = SQL.Replace("@起始日期@", Str.ToString("yyyy-MM-dd"));
            SQL = SQL.Replace("@结束日期@", End.ToString("yyyy-MM-dd"));

            DataTable dt = dal.RunSqlDataTable(SQL);

            foreach (DataRow row in dt.Rows)
            {
                int n = (int)row["站点"];

                for (int i = 0; i < zds; i++)
                {
                    if (n == myData[i].站点)
                    {
                        myData[i].生产气量 = double.Parse(row["生产合计"].ToString());
                        myData[i].预购刷卡气量 = double.Parse(row["刷卡气量合计"].ToString());
                        myData[i].刷卡记账气量 = double.Parse(row["刷卡记账合计"].ToString());
                        myData[i].手工记账气量 = double.Parse(row["手工记账合计"].ToString());
                        myData[i].交款气量 = double.Parse(row["现金气量合计"].ToString());
                        myData[i].交款金额 = double.Parse(row["现金合计"].ToString());
                        myData[i].报损气量 = double.Parse(row["报损合计"].ToString());

                    }
                }

            }

            //汇总统计
            SQL = @"SELECT sum ([预购刷卡气量]) as 预购刷卡气量, sum ([预购刷卡金额]) as 预购刷卡金额, sum ([现金消费气量]) as 现金消费气量, sum ([现金消费金额]) as 现金消费金额 FROM [CNGQT].[dbo].[SC_班销售统计] where  日期 BETWEEN '@起始时间@' AND '@结束时间@' ";
            SQL = SQL.Replace("@起始时间@", Str.ToString("yyyy-MM-dd"));
            SQL = SQL.Replace("@结束时间@", End.ToString("yyyy-MM-dd"));
            dt = dal.RunSqlDataTable(SQL);
            月度汇总.预购刷卡气量 = double.Parse(dt.Rows[0]["预购刷卡气量"].ToString());
            月度汇总.刷卡金额 = double.Parse(dt.Rows[0]["预购刷卡金额"].ToString());
            月度汇总.交款气量 = double.Parse(dt.Rows[0]["现金消费气量"].ToString());
            月度汇总.交款金额 = double.Parse(dt.Rows[0]["现金消费金额"].ToString());
        
            #endregion

            #region 第二部 区间统计 修正值,库存,普通水,电量,购进(无修正)

            foreach (CW_月度统计Mod my in myData)
            {
                int zd = my.站点;
                int bz_str = 0, bz_end = 0;
                string ID_str = "0", ID_end = "0";

                double ljxz = 0; //存放累计修正值


                //第一步 取 区间 的班组号
                if (zd == 4)
                {
                    bz_str = ZDGAS.getBZno(Str.AddDays(-1));
                    bz_end = ZDGAS.getBZno(End);
                }
                else
                {
                    bz_str = ZDGAS.getBZno(Str.AddDays(-1), zd, true);
                    bz_end = ZDGAS.getBZno(End, zd, true);
                }
                CW_每班统计 modstart; //起始数据 起始班 的上一个夜班
                CW_每班统计 modend; //结束数据
                CW_每班统计 modsum; //累计数据
                XzzMod zdxz = new XzzMod(); //区间修正值累计

                //第二步 取得 班组数据所在数据库的ID
                SQL = "SELECT ID  FROM [CNGQT].[dbo].[CW_每班统计]  where 站点=@站点 AND 日期=@日期 AND 班组=@班组";
                SqlParameter par1 = new SqlParameter("@站点", zd);
                SqlParameter par2 = new SqlParameter("@日期", Str.AddDays(-1));
                SqlParameter par3 = new SqlParameter("@班组", bz_str);
                ID_str = dal.RunSqlGetID(SQL, new SqlParameter[] { par1, par2, par3 });

                par1 = new SqlParameter("@站点", zd);
                par2 = new SqlParameter("@日期", End);
                par3 = new SqlParameter("@班组", bz_end);
                ID_end = dal.RunSqlGetID(SQL, new SqlParameter[] { par1, par2, par3 });
                if (ID_end==null || ID_str==null)
                {
                    Exception e = new Exception("交接班数据不完整,请检查!");
                    throw e;
                }
                
                //第三步 取得实体对象 修正值对象
                modstart = ZDGAS.GetModel_CW_每班统计(int.Parse(ID_str));
                modend = ZDGAS.GetModel_CW_每班统计(int.Parse(ID_end));
                zdxz = getZDregXzz(modstart, modend);
                ljxz += zdxz.llj;
                //第四步 取得所需数据
                my.购进数 = Math.Round((modend.SX_进站止数 - modstart.SX_进站止数) - (modend.SX_再生止数 - modstart.SX_再生止数), 2);
                //无修正值购进数
                my.电量总峰 = Math.Round(modend.SX_电量总峰 - modstart.SX_电量总峰 + zdxz.zdf, 2);
                my.电量总平 = Math.Round(modend.SX_电量总平 - modstart.SX_电量总平 + zdxz.zdp, 2);
                my.电量总谷 = Math.Round(modend.SX_电量总谷 - modstart.SX_电量总谷 + zdxz.zdg, 2);
                my.修正值 = zdxz.llj;
                my.普通水 = Math.Round(modend.SX_普通水 - modstart.SX_普通水 + zdxz.pts, 2);
                my.库存数 = Math.Round(modend.SX_罐储气 - modstart.SX_罐储气, 2);


            }


            #endregion

            #region 第三步  将月度数据写入数据库

            foreach (CW_月度统计Mod my in myData)
            {
                SQL = @"SELECT ID,锁定 FROM [CNGQT].[dbo].[CW_月度统计] where 年=@年@ and 月=@月@ and 站点=@站点@";
                SQL = SQL.Replace("@年@", year.ToString());
                SQL = SQL.Replace("@月@", yue.ToString());
                SQL = SQL.Replace("@站点@", my.站点.ToString());

                string[] data = dal.RunSqlStrings(SQL);
                if (data[1] == "True")
                {//数据如果锁定,就不更新数据
                    return;
                }

                SQL = @"UPDATE [CNGQT].[dbo].[CW_月度统计]
                                           SET [购进数] = @购进数@
                                              ,[生产气量] = @生产气量@
                                              ,[刷卡金额] = @刷卡金额@
                                              ,[充值金额] = @充值金额@
                                              ,[交款金额] = @交款金额@
                                              ,[手工记账气量] = @手工记账气量@
                                              ,[刷卡记账气量] = @刷卡记账气量@
                                              ,[预购刷卡气量] = @预购刷卡气量@
                                              ,[交款气量] = @交款气量@
                                              ,[报损气量] = @报损气量@
                                              ,[库存数] = @库存数@
                                              ,[电量总峰] = @电量总峰@
                                              ,[电量总平] = @电量总平@
                                              ,[电量总谷] = @电量总谷@
                                              ,[普通水] = @普通水@
                                              ,[停业时间] = @停业时间@
                                              ,[更新时间] = getdate()
                                         WHERE ID=@ID@";
                SQL = SQL.Replace("@购进数@", my.购进数.ToString());
                SQL = SQL.Replace("@生产气量@", my.生产气量.ToString());
                SQL = SQL.Replace("@刷卡金额@", my.刷卡金额.ToString());
                SQL = SQL.Replace("@充值金额@", my.充值金额.ToString());
                SQL = SQL.Replace("@交款金额@", my.交款金额.ToString());
                SQL = SQL.Replace("@手工记账气量@", my.手工记账气量.ToString());
                SQL = SQL.Replace("@刷卡记账气量@", my.刷卡记账气量.ToString());
                SQL = SQL.Replace("@预购刷卡气量@", my.预购刷卡气量.ToString());
                SQL = SQL.Replace("@交款气量@", my.交款气量.ToString());
                SQL = SQL.Replace("@报损气量@", my.报损气量.ToString());
                SQL = SQL.Replace("@库存数@", my.库存数.ToString());
                SQL = SQL.Replace("@电量总峰@", my.电量总峰.ToString());
                SQL = SQL.Replace("@电量总平@", my.电量总平.ToString());
                SQL = SQL.Replace("@电量总谷@", my.电量总谷.ToString());
                SQL = SQL.Replace("@普通水@", my.普通水.ToString());
                SQL = SQL.Replace("@停业时间@", my.停业时间.ToString());
                SQL = SQL.Replace("@ID@", data[0]);
                dal.RunSql(SQL);
            }

            #endregion


            #region 第四步  统计单位记账数据 合计数据

            SQL = @"SELECT  a.[记账单位名称] 
                                          ,isnull ( b.刷卡气量,0) as 刷卡
                                          ,isnull ( b.手工气量,0) as 手工
                                          ,isnull ( c.普通价格,0) as 价格
                                      FROM [CNGQT].[dbo].[SC_记账单位名称] a LEFT JOIN 
                                      (
                                      SELECT [记账单位名称]
                                          ,sum([刷卡记账气量]) as 刷卡气量
                                          ,sum([手工记账气量]) as 手工气量
                                      FROM [CNGQT].[dbo].[SC_记账明细]
                                      where  日期 BETWEEN '@起始时间@' and '@结束时间@' GROUP BY 记账单位名称
                                      ) b ON a.记账单位名称=b.记账单位名称 LEFT JOIN
                                      (
                                      select aa.单位名称,aa.普通价格 from  [CNGQT].[dbo].SC_记账单位执行价格 aa 
                                    where 起始时间 = (
                                    select top 1 起始时间 from [CNGQT].[dbo].SC_记账单位执行价格 
                                    where 单位名称 = aa.单位名称 and 起始时间<='@起始时间@' ORDER BY aa.起始时间 DESC )
                                      ) c ON c.单位名称=a.记账单位名称
                                     ORDER BY a.编号";

            SQL = SQL.Replace("@起始时间@", Str.ToShortDateString());
            SQL = SQL.Replace("@结束时间@", End.ToShortDateString());
            DataTable dt1 = dal.RunSqlDataTable(SQL);
            for (int i = 0; i < dt1.Rows.Count; i++)
            {
                记账单位合计[i] = new CW_JiZhangDanWen();
                记账单位合计[i].单位名称 = dt1.Rows[i]["记账单位名称"].ToString();
                记账单位合计[i].手工 = double.Parse(dt1.Rows[i]["手工"].ToString());
                记账单位合计[i].刷卡 = double.Parse(dt1.Rows[i]["刷卡"].ToString());
                记账单位合计[i].价格 = double.Parse(dt1.Rows[i]["价格"].ToString());
            }
            #endregion


        }
Exemplo n.º 2
0
        /// <summary>
        /// 初始化数据
        /// </summary>
        public void init(DateTime daystr, DateTime dayend, int days)
        {
            DAL dal = new DAL();

            switch (Zd)//初始化 枪数
            {
                case 2: Guns = (int)jiCuShuJu.ZdGuns.南高; break;
                case 3: Guns = (int)jiCuShuJu.ZdGuns.西路; break;
                case 4: Guns = (int)jiCuShuJu.ZdGuns.麻柳沱; break;
            }
            GunYuGou = new CW_YuGou_GunMod[Guns];
            GongJiao = new CW_GongJiaoMod[days];
            JiZhang = new CW_JiZhangDanWen[jiCuShuJu.记账单位数];

            #region 第一步 统计站点 每枪的预购数据
            
            //统计出 每枪的 预购数据
            string SQL = @"select sum([NowGas]) as 合计气量,sum([NowMoney]) as 合计金额 from(
                                        SELECT *
                                          FROM [CSERVER].[dbo].[GAS]
                                        where [StationNO]=@站点@ and [GunNO]=@枪号@ and Payway='预购金额' and [TradeDate] between '@起始时间@' and '@结束时间@'
                                        union
                                        SELECT *
                                          FROM [CSERVER].[dbo].[TEMPGAS]
                                        where [StationNO]=@站点@ and [GunNO]=@枪号@ and Payway='预购金额' and [TradeDate] between '@起始时间@' and '@结束时间@'
                                        ) test";

            double h1 = 0;//用于计算合计金额
            double h2 = 0;//用于计算合计气量
            for (int i = 0; i < Guns; i++)
            {
                //根据枪号、站点 取交接班时间
                string str = "";
                string end = "";
                GunYuGou[i] = new CW_YuGou_GunMod();
                if (Zd == 4)
                {
                    str = ZDGAS.getBZstarttime(daystr, i + 1);
                    end = ZDGAS.getBZendtime(dayend, i + 1);
                }
                if (Zd == 2 || Zd == 3)
                {
                    str = ZDGAS.getBZstarttime(daystr, i + 1, Zd, false);
                    end = ZDGAS.getBZendtime(dayend, i + 1, Zd, true);
                }
                if (str=="" || end=="")
                {
                    Exception e = new Exception("交接班时间无法获取!");
                    throw e;
                }


                string sq = SQL.Replace("@站点@", Zd.ToString());
                sq = sq.Replace("@枪号@", (i + 1).ToString());
                sq = sq.Replace("@起始时间@", str);
                sq = sq.Replace("@结束时间@", end);
                string[] ss = dal.RunSqlStrings(sq);
                GunYuGou[i].GunStr = str;
                GunYuGou[i].GunEnd = end;
                GunYuGou[i].预购金额 = double.Parse(ss[1]);
                GunYuGou[i].预购气量 = double.Parse(ss[0]);
                h1 += GunYuGou[i].预购金额;
                h2 += GunYuGou[i].预购气量;
            }
            合计预购金额 = Math.Round(h1, 2);
            合计预购气量 = Math.Round(h2, 2);
            #endregion

            #region 第二部 统计站点 充值金额
            SQL = @"SELECT sum(money) as 合计金额
                              FROM [CSERVER].[dbo].[Addmoney]
                            where [StationNO]=@站点@ and [PurchaseDate] between '@起始时间@' and '@结束时间@'";
            string s2 = SQL.Replace("@站点@", Zd.ToString());
            s2 = s2.Replace("@起始时间@", daystr.ToShortDateString() + " 0:00:00");
            s2 = s2.Replace("@结束时间@", dayend.ToShortDateString() + " 23:59:59");

            充值金额 = double.Parse(dal.RunSqlGetID(s2));
            #endregion

            #region 第三步  统计出 公交公司每日明细
            SQL = @"SELECT a.[日期],ISNULL(b.刷卡,0) as 刷卡,ISNULL(b.手工,0) as 手工,ISNULL(c.停业时间,0) as 停业 FROM [CNGQT].[dbo].[Temp_日期] a LEFT JOIN (
                            SELECT 日期,sum([刷卡记账气量]) as 刷卡,sum([手工记账气量]) as 手工 FROM [CNGQT].[dbo].[SC_记账明细]  
                            where 站点=@站点@ and 日期 between '@起始时间@' and '@结束时间@' AND 记账单位名称='公交公司' group BY 日期 
                            ) b ON a.日期=b.日期 LEFT JOIN(
                            SELECT [日期],SUM([累计小时]) as 停业时间 FROM [CNGQT].[dbo].[SC_停业时间]
                            where 站点=@站点@ and 日期 between '@起始时间@' and '@结束时间@' group by 日期
                            ) c ON a.日期=c.日期";
            string s3 = SQL.Replace("@站点@", Zd.ToString());
            s3 = s3.Replace("@起始时间@", daystr.ToShortDateString());
            s3 = s3.Replace("@结束时间@", dayend.ToShortDateString());
            DataTable dt1 = dal.RunSqlDataTable(s3);
            for (int i = 0; i < dt1.Rows.Count; i++)
            {
                GongJiao[i] = new CW_GongJiaoMod();
                GongJiao[i].日期 = DateTime.Parse(dt1.Rows[i]["日期"].ToString()).ToString("MM月dd日");
                GongJiao[i].手工记账 = double.Parse(dt1.Rows[i]["手工"].ToString());
                GongJiao[i].刷卡记账 = double.Parse(dt1.Rows[i]["刷卡"].ToString());
                GongJiao[i].停业时间 = double.Parse(dt1.Rows[i]["停业"].ToString());
            }
            #endregion

            #region 第四步  统计单位记账数据

            SQL = @"SELECT  a.[记账单位名称] 
                                          ,isnull ( b.刷卡气量,0) as 刷卡
                                          ,isnull ( b.手工气量,0) as 手工
                                          ,isnull ( c.普通价格,0) as 价格
                                      FROM [CNGQT].[dbo].[SC_记账单位名称] a LEFT JOIN 
                                      (
                                      SELECT [记账单位名称]
                                          ,sum([刷卡记账气量]) as 刷卡气量
                                          ,sum([手工记账气量]) as 手工气量
                                      FROM [CNGQT].[dbo].[SC_记账明细]
                                      where 站点=@站点@ AND 日期 BETWEEN '@起始时间@' and '@结束时间@' GROUP BY 记账单位名称
                                      ) b ON a.记账单位名称=b.记账单位名称 LEFT JOIN
                                      (
                                      select aa.单位名称,aa.普通价格 from  [CNGQT].[dbo].SC_记账单位执行价格 aa 
                                    where 起始时间 = (
                                    select top 1 起始时间 from [CNGQT].[dbo].SC_记账单位执行价格 
                                    where 单位名称 = aa.单位名称 and 起始时间<='@起始时间@' ORDER BY aa.起始时间 DESC )
                                      ) c ON c.单位名称=a.记账单位名称
                                     ORDER BY a.编号";
            string s4 = SQL.Replace("@站点@", Zd.ToString());
            s4 = s4.Replace("@起始时间@", daystr.ToShortDateString());
            s4 = s4.Replace("@结束时间@", dayend.ToShortDateString());
            dt1 = dal.RunSqlDataTable(s4);
            for (int i = 0; i < dt1.Rows.Count; i++)
            {
                JiZhang[i] = new CW_JiZhangDanWen();
                JiZhang[i].单位名称 = dt1.Rows[i]["记账单位名称"].ToString();
                JiZhang[i].手工 = double.Parse(dt1.Rows[i]["手工"].ToString());
                JiZhang[i].刷卡 = double.Parse(dt1.Rows[i]["刷卡"].ToString());
                JiZhang[i].价格 = double.Parse(dt1.Rows[i]["价格"].ToString());
            }
            #endregion

            #region 取 停业时间

            SQL = @"SELECT ISNULL(sum([累计小时]),0) as 停业时间 FROM [CNGQT].[dbo].[SC_停业时间] where 站点=@站点@ and 日期 between '@起始时间@' and '@结束时间@'";
            string s5 = SQL.Replace("@站点@", Zd.ToString());
            s5 = s5.Replace("@起始时间@", daystr.ToShortDateString());
            s5 = s5.Replace("@结束时间@", dayend.ToShortDateString());
            string tysj = dal.RunSqlGetID(s5);

            停业时间 = double.Parse(tysj);

            #endregion
        }
Exemplo n.º 3
0
        /// <summary>
        /// 构造函数 
        /// </summary>
        /// <param name="str">起始时间</param>
        /// <param name="end">结束时间</param>
        public CountCWGas(DateTime str, DateTime end)
        {
            Str = str;
            End = end;
            Days = (end - str).Days + 1; //统计区间天数
            in_data(str, Days);//插入日期临时表
            zds = jiCuShuJu.ZDS;//默认 3个站点

            myData = new CW_月度统计Mod[zds];
            myGunYuGou = new CW_zdGunYuGouMod[zds];
            记账单位合计 = new CW_JiZhangDanWen[jiCuShuJu.记账单位数];
            月度汇总 = new CW_月度统计Mod();
            for (int i = 0; i < zds; i++)
            {
                int zd = 0;
                switch (i)
                {
                    case 0: zd = (int)jiCuShuJu.CngZd.南高; break;
                    case 1: zd = (int)jiCuShuJu.CngZd.西路; break;
                    case 2: zd = (int)jiCuShuJu.CngZd.麻柳沱; break;
                }
                myData[i] = new CW_月度统计Mod();
                myGunYuGou[i] = new CW_zdGunYuGouMod();
                myData[i].站点 = zd;
                myGunYuGou[i].Zd = zd;
                myGunYuGou[i].init(str, end, Days);//初始化 就完成 每枪的 预购数据统计
                myData[i].充值金额 = myGunYuGou[i].充值金额;
                myData[i].刷卡金额 = myGunYuGou[i].合计预购金额;
                myData[i].停业时间 = myGunYuGou[i].停业时间;
            }
        }