/// <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>
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
}
/// <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].停业时间;
}
}