Пример #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


        }
Пример #2
0
        /// <summary>
        /// 区间统计 修正值合计数据 
        /// </summary>
        /// <param name="Gstart">结束</param>
        /// <param name="Gend">起始</param>
        /// <returns>修正值模型</returns>
        private XzzMod getZDregXzz(CW_每班统计 Gstart, CW_每班统计 Gend)
        {
            string SQL = @"SELECT isnull(sum([总电峰]),0) as 总电峰
                                                ,isnull(sum([总电平]),0) as 总电平
                                                ,isnull(sum([总电谷]),0) as 总电谷
                                                ,isnull(sum([商电峰]),0) as 商电峰
                                                ,isnull(sum([商电平]),0) as 商电平
                                                ,isnull(sum([商电谷]),0) as 商电谷
                                                ,isnull(sum([正无功]),0) as 正无功
                                                ,isnull(sum([反无功]),0) as 反无功
                                                ,isnull(sum([普通水]),0) as 普通水
                                                ,isnull(sum([消防水]),0) as 消防水
                                                ,isnull(sum([流量计]),0) as 流量计
                                            FROM [CNGQT].[dbo].[CW_每班修正]
                                            where 日期 BETWEEN @起始日期 AND @结束日期 AND  站点=@站点 ";
            DAL dal = new DAL();
            XzzMod xz = new XzzMod();

            SqlParameter par1 = new SqlParameter("@站点", Gstart.SX_站点);
            SqlParameter par2 = new SqlParameter("@起始日期", Gstart.SX_日期.AddDays(1));
            SqlParameter par3 = new SqlParameter("@结束日期", Gend.SX_日期);
            string[] val = dal.RunSqlStrings(SQL, new SqlParameter[] { par1, par2, par3 });
            if (val != null)
            {
                xz.zdf = double.Parse(val[0]);
                xz.zdp = double.Parse(val[1]);
                xz.zdg = double.Parse(val[2]);
                xz.sdf = double.Parse(val[3]);
                xz.sdp = double.Parse(val[4]);
                xz.sdg = double.Parse(val[5]);
                xz.zwg = double.Parse(val[6]);
                xz.fwg = double.Parse(val[7]);
                xz.pts = double.Parse(val[8]);
                xz.xfs = double.Parse(val[9]);
                xz.llj = double.Parse(val[10]);
            }
            return xz;
        }
Пример #3
0
        /// <summary>
        ///分站点统计 每日 购进气量,电量,水量 
        /// </summary>
        private void CountDayGoujinDianSui()
        {
            foreach (ZdDaysGasMod my in MyDayGas)
            {
                int zd = my.Zd;
                int bz_str = 0, bz_end = 0;
                string ID_str = "0", ID_end = "0", SQL = "";
                DAL dal = new DAL();
                double ljxz = 0; //存放累计修正值

                foreach (CountZdGasMod day in my.DayGas)
                {
                    //第一步 取 区间 的班组号
                    if (zd == 4)
                    {
                        bz_str = ZDGAS.getBZno(day.日期.AddDays(-1));
                        bz_end = ZDGAS.getBZno(day.日期);
                    }
                    else
                    {
                        bz_str = ZDGAS.getBZno(day.日期.AddDays(-1), zd, true);
                        bz_end = ZDGAS.getBZno(day.日期, 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("@日期", day.日期.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("@日期", day.日期);
                    par3 = new SqlParameter("@班组", bz_end);
                    ID_end = dal.RunSqlGetID(SQL, new SqlParameter[] { par1, par2, par3 });

                    //第三步 取得实体对象 修正值对象
                    modstart = ZDGAS.GetModel_CW_每班统计(int.Parse(ID_str));
                    modend = ZDGAS.GetModel_CW_每班统计(int.Parse(ID_end));
                    zdxz = getZDregXzz(modstart, modend);
                    ljxz += zdxz.llj;
                    //第四步 取得所需数据
                    day.进站起 = modstart.SX_进站止数;
                    day.进站止 = modend.SX_进站止数;
                    day.再生起 = modstart.SX_再生止数;
                    day.再生止 = modend.SX_再生止数;
                    day.购进气量 = (modend.SX_进站止数 - modstart.SX_进站止数) - (modend.SX_再生止数 - modstart.SX_再生止数) + zdxz.llj;//购进气量
                    day.礶差 = modend.SX_罐储气 - modstart.SX_罐储气;
                    day.耗电量 = (modend.SX_电量总峰 - modstart.SX_电量总峰 + zdxz.zdf) + (modend.SX_电量总平 - modstart.SX_电量总平 + zdxz.zdp) + (modend.SX_电量总谷 - modstart.SX_电量总谷 + zdxz.zdg);
                    day.购进气量 = Math.Round(day.购进气量, 2);
                    day.礶差 = Math.Round(day.礶差, 2);
                    day.耗电量 = Math.Round(day.耗电量, 2);
                }

                foreach (ZdYueGasMod yue in MyYueGas)
                {
                    if (yue.Zd == my.Zd)
                    {
                        yue.YueGas.累计修正 = ljxz;
                    }
                }
            }
        }