/// <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
}
/// <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;
}
/// <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;
}
}
}
}
