/// <summary>
/// 区间统计 修正值合计数据
/// </summary>
/// <param name="Gstart">结束</param>
/// <param name="Gend">起始</param>
/// <returns>修正值模型</returns>
private xzz 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();
xzz xz = new xzz();
xz.init();
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>
/// 返回 电类数据json 每班数据用
/// </summary>
private string jsonDianLei(CW_每班统计 Gstart, CW_每班统计 Gend, xzz xz)
{
string rowtext = "{\"total\":100,\"rows\":[行数据对象],\"footer\":脚对象}";
string row = " {{ \"电类\": \"{0}\",\"起数\":{1:f},\"止数\":{2:f},\"消耗量\":{3:f},\"修正\":{4:f} }}";
string temp = "";
temp += string.Format(row, "总电量(峰)", Gstart.SX_电量总峰, Gend.SX_电量总峰, Gend.SX_电量总峰 - Gstart.SX_电量总峰 + xz.zdf, xz.zdf) + ",";
temp += string.Format(row, "总电量(平)", Gstart.SX_电量总平, Gend.SX_电量总平, Gend.SX_电量总平 - Gstart.SX_电量总平 + xz.zdp, xz.zdp) + ",";
temp += string.Format(row, "总电量(谷)", Gstart.SX_电量总谷, Gend.SX_电量总谷, Gend.SX_电量总谷 - Gstart.SX_电量总谷 + xz.zdg, xz.zdg) + ",";
temp += string.Format(row, "商电量(峰)", Gstart.SX_电量商峰, Gend.SX_电量商峰, Gend.SX_电量商峰 - Gstart.SX_电量商峰 + xz.sdf, xz.sdf) + ",";
temp += string.Format(row, "商电量(平)", Gstart.SX_电量商平, Gend.SX_电量商平, Gend.SX_电量商平 - Gstart.SX_电量商平 + xz.sdp, xz.sdp) + ",";
temp += string.Format(row, "商电量(谷)", Gstart.SX_电量商谷, Gend.SX_电量商谷, Gend.SX_电量商谷 - Gstart.SX_电量商谷 + xz.sdg, xz.sdg) + ",";
temp += string.Format(row, "正无功", Gstart.SX_电量正无功, Gend.SX_电量正无功, Gend.SX_电量正无功 - Gstart.SX_电量正无功 + xz.zwg, xz.zwg) + ",";
temp += string.Format(row, "反无功", Gstart.SX_电量反无功, Gend.SX_电量反无功, Gend.SX_电量反无功 - Gstart.SX_电量反无功 + xz.fwg, xz.fwg);
rowtext = rowtext.Replace("行数据对象", temp);
double zdl = (Gend.SX_电量总峰 - Gstart.SX_电量总峰 + xz.zdf) + (Gend.SX_电量总平 - Gstart.SX_电量总平 + xz.zdp) + (Gend.SX_电量总谷 - Gstart.SX_电量总谷 + xz.zdg);
double zdlglys = zdl;
double wgglys = (Gend.SX_电量正无功 - Gstart.SX_电量正无功 + xz.zwg) + (Gend.SX_电量反无功 - Gstart.SX_电量反无功 + xz.fwg);
if (Gstart.SX_站点 == 4)
{//麻柳沱 系数为400
zdl = zdl * 400;
}
else
{//西路 南高 系数为800
zdl = zdl * 800;
}
double glys = 0;//功率因素
glys = zdlglys / (Math.Sqrt(zdlglys * zdlglys + wgglys * wgglys));
temp = " [{{ \"电类\": \"总电量：\",\"起数\":{0:f},\"止数\":\"单位电耗:\",\"消耗量\":{1:f4},\"修正\":\"功率因数：{2:f3}\" }}]";
temp = string.Format(temp, zdl, zdl / Gend.SX_生产气量, glys);
rowtext = rowtext.Replace("脚对象", temp);
return rowtext;
}
/// <summary>
/// 取得当班对象
/// </summary>
/// <param name="dt">日期</param>
/// <param name="zd">站点</param>
/// <param name="bz">班组</param>
/// <param name="yb">夜班</param>
/// <param name="scql">生产气量</param>
/// <returns></returns>
private string getGasSc(DateTime dt, int zd, int bz, bool yb, double scql)
{
DAL dal = new DAL();
CW_每班统计 dqBz = new CW_每班统计();//当班数据
CW_每班统计 syBz = new CW_每班统计();//上一班数据
DateTime sydt; //上一班日期
string retext = "{\"sc\":输差对象,\"s\":水对象,\"y\":油对象,\"d\":电对象,\"ysj\":压缩机对象}";
int sybz;//上一班 班组
gassc sc = new gassc();
string SQL = "SELECT ID FROM [CNGQT].[dbo].[CW_每班统计] where [站点]=" + zd.ToString() + " and [日期]='" + dt.ToString() + "' and [班组]='" + bz.ToString() + "'";
string id = dal.RunSqlGetID(SQL);
if (id == null) { return "[]"; }
dqBz = ZDGAS.GetModel_CW_每班统计(int.Parse(id));
if (zd == 4)
{
sydt = dt.AddDays(-1);
sybz = bz - 1;
if (sybz == 0) { sybz = 4; }
}
else
{
if (yb)//当前班是否是夜班
{
sydt = dt;
}
else
{
sydt = dt.AddDays(-1);
}
sybz = bz - 1;
if (sybz == 0) { sybz = 3; }
}
SQL = "SELECT ID FROM [CNGQT].[dbo].[CW_每班统计] where [站点]=" + zd.ToString() + " and [日期]='" + sydt.ToString() + "' and [班组]='" + sybz.ToString() + "'";
id = dal.RunSqlGetID(SQL);
syBz = ZDGAS.GetModel_CW_每班统计(int.Parse(id));//上一班数据
//取当班 流量计修正值
SQL = "SELECT [总电峰],[总电平],[总电谷],[商电峰],[商电平],[商电谷],[正无功],[反无功],[普通水],[消防水],[流量计] FROM [CNGQT].[dbo].[CW_每班修正] where 日期='" + dt.ToString() + "' AND 站点=" + zd.ToString() + " AND 班组=" + bz.ToString();
xzz dbxzz = new xzz();
dbxzz.init();
string[] val = dal.RunSqlStrings(SQL);
if (val != null)
{
dbxzz.zdf = double.Parse(val[0]);
dbxzz.zdp = double.Parse(val[1]);
dbxzz.zdg = double.Parse(val[2]);
dbxzz.sdf = double.Parse(val[3]);
dbxzz.sdp = double.Parse(val[4]);
dbxzz.sdg = double.Parse(val[5]);
dbxzz.zwg = double.Parse(val[6]);
dbxzz.fwg = double.Parse(val[7]);
dbxzz.pts = double.Parse(val[8]);
dbxzz.xfs = double.Parse(val[9]);
dbxzz.llj = double.Parse(val[10]);
}
sc.进站起 = dqBz.SX_进站起数;
sc.进站止 = dqBz.SX_进站止数;
sc.进站气量 = sc.进站止 - sc.进站起;
sc.再生起 = dqBz.SX_再生起数;
sc.再生止 = dqBz.SX_再生止数;
sc.再生气量 = sc.再生止 - sc.再生起;
sc.生产气量 = scql;
sc.购进气量 = sc.进站气量 - sc.再生气量 + dbxzz.llj;
sc.罐储气 = dqBz.SX_罐储气;
sc.罐差 = dqBz.SX_罐储气 - syBz.SX_罐储气;
sc.罐储气起 = syBz.SX_罐储气;
sc.罐储气止 = dqBz.SX_罐储气;
sc.输差 = sc.购进气量 - sc.生产气量 - sc.罐差;
sc.修正值 = dbxzz.llj;
if (sc.生产气量 == 0 || sc.购进气量 == 0)
{
sc.输差率 = 0;
}
else
{
sc.输差率 = sc.输差 / sc.购进气量;
}
string temp = "{{ \"total\": 1, \"rows\": [{{ \"进站起\": {0:f},\"进站止\":{1:f},\"再生起\":{2:f},\"再生止\":{3:f},\"购进气量\":{4:f},\"生产气量\":{5:f},\"罐储气\":{6:f},\"罐差\":{7:f},\"输差\":{8:f},\"输差率\":{9:f},\"修正\":{10:f} }}]}}";
temp = string.Format(temp, sc.进站起, sc.进站止, sc.再生起, sc.再生止, sc.购进气量, sc.生产气量, sc.罐储气, sc.罐差, sc.输差, sc.输差率 * 100, sc.修正值);
retext = retext.Replace("输差对象", temp);
temp = jsonYouLei(syBz, dqBz);
retext = retext.Replace("油对象", temp);
temp = jsonDianLei(syBz, dqBz, dbxzz);
retext = retext.Replace("电对象", temp);
temp = jsonShuiLei(syBz, dqBz, dbxzz);
retext = retext.Replace("水对象", temp);
temp = jsonYaShoJi(syBz, dqBz);
retext = retext.Replace("压缩机对象", temp);
Sys.DebugMes(temp);
return retext;
}
/// <summary>
/// 统计区间 合计数据 模型
/// </summary>
/// <param name="Gstart">起始</param>
/// <param name="Gend">结束</param>
/// <returns>模型</returns>
private CW_每班统计 getZDregsum(CW_每班统计 Gstart, CW_每班统计 Gend)
{
string SQL = @"SELECT sum([生产气量])as 生产气量
,sum([现金气量])as 现金气量
,sum([预购刷卡气量])as 预购刷卡气量
,sum([记帐刷卡气量])as 记帐刷卡气量
,sum([手工记账气量])as 手工记账气量
,sum([报损气量])as 报损气量
,sum([误差])as 误差
,sum([压缩机油新增])as 压缩机油新增
,sum([机械油新增])as 机械油新增
,sum([煤油新增])as 煤油新增
,sum([特殊用油新增])as 特殊用油新增
,sum([一号压缩机开])as 一号压缩机开
,sum([一号压缩机关])as 一号压缩机关
,sum([一号压缩机累计])as 一号压缩机累计
,sum([二号压缩机开])as 二号压缩机开
,sum([二号压缩机关])as 二号压缩机关
,sum([二号压缩机累计])as 二号压缩机累计
,sum([三号压缩机开])as 三号压缩机开
,sum([三号压缩机关])as 三号压缩机关
,sum([三号压缩机累计])as 三号压缩机累计
,sum([四号压缩机开])as 四号压缩机开
,sum([四号压缩机关])as 四号压缩机关
,sum([四号压缩机累计])as 四号压缩机累计
FROM [CNGQT].[dbo].[CW_每班统计]
where 站点=@站点 AND 日期 BETWEEN @起始时间 and @结束时间";
DAL dal = new DAL();
CW_每班统计 ret = new CW_每班统计();
SqlParameter par1 = new SqlParameter("@站点", Gstart.SX_站点);
SqlParameter par2 = new SqlParameter("@起始时间", Gstart.SX_日期.AddDays(1));
SqlParameter par3 = new SqlParameter("@结束时间", Gend.SX_日期);
DataTable dt = dal.RunSqlDataTable(SQL, new SqlParameter[] { par1, par2, par3 });
if (dt.Rows.Count > 0)
{
ret.SX_生产气量 = double.Parse(dt.Rows[0]["生产气量"].ToString());
ret.SX_现金气量 = double.Parse(dt.Rows[0]["现金气量"].ToString());
ret.SX_预购刷卡气量 = double.Parse(dt.Rows[0]["预购刷卡气量"].ToString());
ret.SX_记帐刷卡气量 = double.Parse(dt.Rows[0]["记帐刷卡气量"].ToString());
ret.SX_手工记账气量 = double.Parse(dt.Rows[0]["手工记账气量"].ToString());
ret.SX_报损气量 = double.Parse(dt.Rows[0]["报损气量"].ToString());
ret.SX_误差 = double.Parse(dt.Rows[0]["误差"].ToString());
ret.SX_压缩机油新增 = double.Parse(dt.Rows[0]["压缩机油新增"].ToString());
ret.SX_机械油新增 = double.Parse(dt.Rows[0]["机械油新增"].ToString());
ret.SX_煤油新增 = double.Parse(dt.Rows[0]["煤油新增"].ToString());
ret.SX_特殊用油新增 = double.Parse(dt.Rows[0]["特殊用油新增"].ToString());
ret.SX_一号压缩机开 = double.Parse(dt.Rows[0]["一号压缩机开"].ToString());
ret.SX_一号压缩机关 = double.Parse(dt.Rows[0]["一号压缩机关"].ToString());
ret.SX_一号压缩机累计 = double.Parse(dt.Rows[0]["一号压缩机累计"].ToString());
ret.SX_二号压缩机开 = double.Parse(dt.Rows[0]["二号压缩机开"].ToString());
ret.SX_二号压缩机关 = double.Parse(dt.Rows[0]["二号压缩机关"].ToString());
ret.SX_二号压缩机累计 = double.Parse(dt.Rows[0]["二号压缩机累计"].ToString());
ret.SX_三号压缩机开 = double.Parse(dt.Rows[0]["三号压缩机开"].ToString());
ret.SX_三号压缩机关 = double.Parse(dt.Rows[0]["三号压缩机关"].ToString());
ret.SX_三号压缩机累计 = double.Parse(dt.Rows[0]["三号压缩机累计"].ToString());
ret.SX_四号压缩机开 = double.Parse(dt.Rows[0]["四号压缩机开"].ToString());
ret.SX_四号压缩机关 = double.Parse(dt.Rows[0]["四号压缩机关"].ToString());
ret.SX_四号压缩机累计 = double.Parse(dt.Rows[0]["四号压缩机累计"].ToString());
}
return ret;
}
/// <summary>
/// 返回 压缩机数据json 用于每班统计
/// </summary>
private string jsonYaShoJi(CW_每班统计 Gstart, CW_每班统计 Gend)
{
string rowtext = "{\"total\":100,\"rows\":[行数据对象],\"footer\":脚对象}";
string row = " {{ \"编号\": \"{0}\",\"开机次数\":{1:f},\"关机次数\":{2:f},\"运行时间\":{3:f} }}";
string temp = "";
temp += string.Format(row, "1#", Gend.SX_一号压缩机开, Gend.SX_一号压缩机关, Gend.SX_一号压缩机累计) + ",";
temp += string.Format(row, "2#", Gend.SX_二号压缩机开, Gend.SX_二号压缩机关, Gend.SX_二号压缩机累计) + ",";
temp += string.Format(row, "3#", Gend.SX_三号压缩机开, Gend.SX_三号压缩机关, Gend.SX_三号压缩机累计) + ",";
temp += string.Format(row, "4#", Gend.SX_四号压缩机开, Gend.SX_四号压缩机关, Gend.SX_四号压缩机累计);
rowtext = rowtext.Replace("行数据对象", temp);
double GouJin = (Gend.SX_进站止数 - Gstart.SX_进站止数) - (Gend.SX_再生止数 - Gstart.SX_再生止数);//购进气量
double kai, guan, leiji, pailiang;//开机累计,关机累计,运行累计,排量
kai = Gend.SX_一号压缩机开 + Gend.SX_二号压缩机开 + Gend.SX_三号压缩机开 + Gend.SX_四号压缩机开;
guan = Gend.SX_一号压缩机关 + Gend.SX_二号压缩机关 + Gend.SX_三号压缩机关 + Gend.SX_四号压缩机关;
leiji = Gend.SX_一号压缩机累计 + Gend.SX_二号压缩机累计 + Gend.SX_三号压缩机累计 + Gend.SX_四号压缩机累计;
pailiang = GouJin / leiji;
temp = " [{{ \"编号\": \"合计:\",\"开机次数\":{0:f},\"关机次数\":{1:f},\"运行时间\":{2:f} }},{{\"编号\": \"单机排量1:\",\"开机次数\":{3:f},\"关机次数\":\"单机排量2:\",\"运行时间\":{4:f} }}]";
temp = string.Format(temp, kai, guan, leiji, Gend.SX_生产气量 / leiji, pailiang);
rowtext = rowtext.Replace("脚对象", temp);
return rowtext;
}
/// <summary>
/// 返回 油类数据json 用于当班
/// </summary>
private string jsonYouLei(CW_每班统计 Gstart, CW_每班统计 Gend)
{
string rowtext = "{\"total\":100,\"rows\":[行数据对象]}";
string row = " {{ \"油类\": \"{0}\",\"起数\":{1:f},\"止数\":{2:f},\"累计新增\":{3:f},\"消耗量\":{4:f} }}";
string temp = "";
temp += string.Format(row, "压缩机油", Gstart.SX_压缩机油, Gend.SX_压缩机油, Gend.SX_压缩机油新增, Gstart.SX_压缩机油 - (Gend.SX_压缩机油 - Gend.SX_压缩机油新增)) + ",";
temp += string.Format(row, "机械油", Gstart.SX_机械油, Gend.SX_机械油, Gend.SX_机械油新增, Gstart.SX_机械油 - (Gend.SX_机械油 - Gend.SX_机械油新增)) + ",";
temp += string.Format(row, "煤油", Gstart.SX_煤油, Gend.SX_煤油, Gend.SX_煤油新增, Gstart.SX_煤油 - (Gend.SX_煤油 - Gend.SX_煤油新增)) + ",";
temp += string.Format(row, "特殊用油", Gstart.SX_特殊用油, Gend.SX_特殊用油, Gend.SX_特殊用油新增, Gstart.SX_特殊用油 - (Gend.SX_特殊用油 - Gend.SX_特殊用油新增));
rowtext = rowtext.Replace("行数据对象", temp);
return rowtext;
}
/// <summary>
/// 返回 水类数据json 每班数据用
/// </summary>
private string jsonShuiLei(CW_每班统计 Gstart, CW_每班统计 Gend, xzz xz)
{
string rowtext = "{\"total\":100,\"rows\":[行数据对象],\"footer\":脚对象}";
string row = " {{ \"水类\": \"{0}\",\"起数\":{1:f},\"止数\":{2:f},\"消耗量\":{3:f},\"修正\":{4:f} }}";
string temp = "";
temp += string.Format(row, "普通水", Gstart.SX_普通水, Gend.SX_普通水, Gend.SX_普通水 - Gstart.SX_普通水 + xz.pts, xz.pts) + ",";
temp += string.Format(row, "消防水", Gstart.SX_消防水, Gend.SX_消防水, Gend.SX_消防水 - Gstart.SX_消防水 + xz.xfs, xz.xfs);
rowtext = rowtext.Replace("行数据对象", temp);
temp = " [{{ \"水类\": \"普通水： 每m³气耗水量\",\"起数\":{0:f4} }}]";
temp = string.Format(temp, (Gend.SX_普通水 - Gstart.SX_普通水 + xz.pts) / Gend.SX_生产气量);
rowtext = rowtext.Replace("脚对象", temp);
return rowtext;
}
/// <summary>
/// 返回 停业数据
/// </summary>
private string jsonTingYe(CW_每班统计 Gstart, CW_每班统计 Gend)
{
// string SQL = @"SELECT convert ( varchar(10), [日期],120) as 日期
// ,[起始时间]
// ,[结束时间]
// ,[累计小时]
// ,[分类]
// ,[停业原因]
// FROM [CNGQT].[dbo].[SC_停业时间]
// where 站点=@站点 AND 日期 BETWEEN @起始时间 AND @结束时间 order by 日期";
DAL dal = new DAL();
SqlParameter par1 = new SqlParameter("@站点", Gstart.SX_站点);
SqlParameter par2 = new SqlParameter("@起始时间", Gstart.SX_日期.AddDays(1));
SqlParameter par3 = new SqlParameter("@结束时间", Gend.SX_日期);
//DataTable dt = dal.RunSqlDataTable(SQL, new SqlParameter[] { par1, par2, par3 });
double Gsum = 0;
string retrow = "{\"a\":@停业@,\"b\":@分类@}";
//string rowtext = "{\"total\":" + dt.Rows.Count.ToString() + ",\"rows\":行数据对象,\"footer\":脚对象}";
//if (dt.Rows.Count > 0)
//{
// foreach (DataRow i in dt.Rows)
// {
// Gsum += double.Parse(i["累计小时"].ToString());
// }
// rowtext = rowtext.Replace("行数据对象", Json.TableToJson(BLL.tools.GetPagedTable(dt, 1, 20)));
//}
//else
//{
// rowtext = rowtext.Replace("行数据对象", "[]");
//}
//string temp = " [{{ \"结束时间\": \"累计:\",\"累计小时\":{0:f} }}]";
//temp = string.Format(temp, Gsum);
//rowtext = rowtext.Replace("脚对象", temp);
retrow = retrow.Replace("@停业@", "[]");
string rowtext = "{\"total\":100,\"rows\":行数据对象,\"footer\":脚对象}";
string SQL = @"SELECT [分类]
,sum([累计小时]) as 累计小时
FROM [CNGQT].[dbo].[SC_停业时间]
where 站点=@站点 AND 日期 BETWEEN @起始时间 AND @结束时间 GROUP BY 分类 ORDER BY 分类";
DataTable dt = dal.RunSqlDataTable(SQL, new SqlParameter[] { par1, par2, par3 });
if (dt.Rows.Count > 0)
{
Gsum = 0;
foreach (DataRow i in dt.Rows)
{
Gsum += double.Parse(i["累计小时"].ToString());
}
rowtext = rowtext.Replace("行数据对象", Json.TableToJson(dt));
}
else
{
rowtext = rowtext.Replace("行数据对象", "[]");
}
string temp = " [{{ \"分类\": \"累计:\",\"累计小时\":{0:f} }}]";
temp = string.Format(temp, Gsum);
rowtext = rowtext.Replace("脚对象", temp);
retrow = retrow.Replace("@分类@", rowtext);
return retrow;
}
/// <summary>
/// 返回流量计数据 json对象
/// </summary>
private string jsonLiuLianJi(CW_每班统计 Gstart, CW_每班统计 Gend, CW_每班统计 Gsum, xzz xz)
{
string rowtext = "{\"total\":100,\"rows\":行数据对象}";
string temp = " [{{ \"进站起\": {0:f},\"进站止\":{1:f},\"再生起\":{2:f},\"再生止\":{3:f},\"罐储气起\":{4:f},\"罐储气止\":{5:f},\"购进气量\":{6:f},\"生产气量\":{7:f},\"输差\":{8:f},\"输差率\":{9:f},\"修正值\":{10:f} }}]";
double GouJin = (Gend.SX_进站止数 - Gstart.SX_进站止数) - (Gend.SX_再生止数 - Gstart.SX_再生止数) + xz.llj;//购进气量
double ShuCha = GouJin - Gsum.SX_生产气量 - (Gend.SX_罐储气 - Gstart.SX_罐储气);
temp = string.Format(temp, Gstart.SX_进站止数, Gend.SX_进站止数, Gstart.SX_再生止数, Gend.SX_再生止数, Gstart.SX_罐储气, Gend.SX_罐储气, GouJin, Gsum.SX_生产气量, ShuCha, ShuCha / GouJin * 100, xz.llj);
rowtext = rowtext.Replace("行数据对象", temp);
return rowtext;
}
/// <summary>
/// 返回 记账单位json
/// </summary>
private string jsonJiZhang(CW_每班统计 Gstart, CW_每班统计 Gend)
{
string retext = "{\"tab\":对象1,\"val\":对象2,\"lab\":对象3,\"sum\":对象4}";
string SQL = @"SELECT [记账单位名称],sum([刷卡记账气量]) as 刷卡,sum([手工记账气量])as 手工,sum([刷卡记账气量]+[手工记账气量]) as 小计
FROM [CNGQT].[dbo].[SC_记账明细] where 站点=@站点 and 日期 BETWEEN @起始时间 and @结束时间
GROUP BY 记账单位名称 ORDER BY 小计 desc";
DAL dal = new DAL();
SqlParameter par1 = new SqlParameter("@站点", Gstart.SX_站点);
SqlParameter par2 = new SqlParameter("@起始时间", Gstart.SX_日期.AddDays(1));
SqlParameter par3 = new SqlParameter("@结束时间", Gend.SX_日期);
double[] Gsum = new double[3];
DataTable dt = dal.RunSqlDataTable(SQL, new SqlParameter[] { par1, par2, par3 });
string rowtext = "{\"total\":100,\"rows\":行数据对象,\"footer\":脚对象}";
string temp = " [{{ \"记账单位名称\": \"合计:\",\"刷卡\":{0:f},\"手工\":{1:f} ,\"小计\":{2:f} }}]";
string val = "", lab = "";
if (dt.Rows.Count > 0)
{
foreach (DataRow i in dt.Rows)
{
Gsum[0] += double.Parse(i["刷卡"].ToString());
Gsum[1] += double.Parse(i["手工"].ToString());
Gsum[2] += double.Parse(i["小计"].ToString());
lab += "\"" + i["记账单位名称"].ToString() + "\",";
val += double.Parse(i["小计"].ToString()) + ",";
}
lab = lab.Substring(0, lab.Length - 1);
val = val.Substring(0, val.Length - 1);
temp = string.Format(temp, Gsum[0], Gsum[1], Gsum[2]);
rowtext = rowtext.Replace("脚对象", temp);
rowtext = rowtext.Replace("行数据对象", Json.TableToJson(dt));
}
else
{
rowtext = rowtext.Replace("脚对象", "[]");
rowtext = rowtext.Replace("行数据对象", "[]");
}
retext = retext.Replace("对象1", rowtext);
lab = "[" + lab + "]";
val = "[" + val + "]";
retext = retext.Replace("对象2", val);
retext = retext.Replace("对象3", lab);
retext = retext.Replace("对象4", Gsum[2].ToString());
return retext;
}
/// <summary>
/// 返回 分类销售气量 json
/// </summary>
private string jsonGasFenLei(CW_每班统计 Gsum, int day)
{
string retext = "{\"tab\":对象1,\"val\":对象2,\"sum\":对象3}";
string rowtext = "{\"total\":100,\"rows\":行数据对象,\"footer\":脚对象}";
string temp = " [{{ \"销售分类\": \"现金气量\",\"销售气量\":{0:f} }},{{ \"销售分类\": \"预购气量\",\"销售气量\":{1:f} }},{{ \"销售分类\": \"刷卡记账气量\",\"销售气量\":{2:f} }},{{ \"销售分类\": \"手工记账气量\",\"销售气量\":{3:f} }},{{ \"销售分类\": \"报损气量\",\"销售气量\":{4:f} }}]";
double mysum = Gsum.SX_现金气量 + Gsum.SX_预购刷卡气量 + Gsum.SX_记帐刷卡气量 + Gsum.SX_手工记账气量 + Gsum.SX_报损气量;
temp = string.Format(temp, Gsum.SX_现金气量, Gsum.SX_预购刷卡气量, Gsum.SX_记帐刷卡气量, Gsum.SX_手工记账气量, Gsum.SX_报损气量);
rowtext = rowtext.Replace("行数据对象", temp);
temp = " [{{ \"销售分类\": \"平均每日：{0:f}\",\"销售气量\":\"累计：{1:f}\" }}]";
temp = string.Format(temp, mysum / day, mysum);
rowtext = rowtext.Replace("脚对象", temp);
retext = retext.Replace("对象1", rowtext);
temp = string.Format("[{0:f},{1:f},{2:f},{3:f},{4:f}]", Gsum.SX_现金气量, Gsum.SX_预购刷卡气量, Gsum.SX_记帐刷卡气量, Gsum.SX_手工记账气量, Gsum.SX_报损气量);
retext = retext.Replace("对象2", temp);
retext = retext.Replace("对象3", mysum.ToString());
return retext;
}
/// <summary>
/// 取得输差对象
/// </summary>
/// <param name="dt">日期</param>
/// <param name="zd">站点</param>
/// <param name="bz">班组</param>
/// <param name="yb">夜班</param>
/// <param name="scql">生产气量</param>
/// <returns></returns>
private string getGasSc(DateTime dt, int zd, int bz, bool yb, double scql)
{
DAL dal = new DAL();
CW_每班统计 dqBz = new CW_每班统计();//当班数据
CW_每班统计 syBz = new CW_每班统计();//上一班数据
DateTime sydt; //上一班日期
int sybz;//上一班 班组
gassc sc = new gassc();
string SQL = "SELECT ID FROM [CNGQT].[dbo].[CW_每班统计] where [站点]=" + zd.ToString() + " and [日期]='" + dt.ToString() + "' and [班组]='" + bz.ToString() + "'";
string id = dal.RunSqlGetID(SQL);
if (id == null) { return "[]"; }
dqBz = ZDGAS.GetModel_CW_每班统计(int.Parse(id));
if (zd == 4)
{
sydt = dt.AddDays(-1);
sybz = bz - 1;
if (sybz == 0) { sybz = 4; }
}
else
{
if (yb)//当前班是否是夜班
{
sydt = dt;
}
else
{
sydt = dt.AddDays(-1);
}
sybz = bz - 1;
if (sybz == 0) { sybz = 3; }
}
SQL = "SELECT ID FROM [CNGQT].[dbo].[CW_每班统计] where [站点]=" + zd.ToString() + " and [日期]='" + sydt.ToString() + "' and [班组]='" + sybz.ToString() + "'";
id = dal.RunSqlGetID(SQL);
syBz = ZDGAS.GetModel_CW_每班统计(int.Parse(id));
sc.进站起 = dqBz.SX_进站起数;
sc.进站止 = dqBz.SX_进站止数;
sc.进站气量 = sc.进站止 - sc.进站起;
sc.再生起 = dqBz.SX_再生起数;
sc.再生止 = dqBz.SX_再生止数;
sc.再生气量 = sc.再生止 - sc.再生起;
sc.生产气量 = scql;
sc.实际用气 = sc.进站气量 - sc.再生气量;
sc.罐储气 = dqBz.SX_罐储气;
sc.罐差 = dqBz.SX_罐储气 - syBz.SX_罐储气;
sc.输差 = sc.实际用气 - sc.生产气量 - sc.罐差;
if (sc.生产气量 == 0 || sc.实际用气 == 0)
{
sc.输差率 = 0;
}
else
{
sc.输差率 = sc.输差 / sc.实际用气;
}
string temp = "{{ \"total\": 1, \"rows\": [{{ \"进站起\": {0:f},\"进站止\":{1:f},\"再生起\":{2:f},\"再生止\":{3:f},\"进站气量\":{4:f},\"再生气量\":{5:f},\"实际用气\":{6:f},\"生产气量\":{7:f},\"罐储气\":{8:f},\"罐差\":{9:f},\"输差\":{10:f},\"输差率\":{11:f} }}]}}";
temp = string.Format(temp, sc.进站起, sc.进站止, sc.再生起, sc.再生止, sc.进站气量, sc.再生气量, sc.实际用气, sc.生产气量, sc.罐储气, sc.罐差, sc.输差, sc.输差率 * 100);
Sys.DebugMes(temp);
return temp;
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public static CW_每班统计 GetModel_CW_每班统计(int ID)
{
DAL dal = new DAL();
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID, 预购刷卡气量, 记帐刷卡气量, 手工记账气量, 报损气量, 进站起数, 进站止数, 再生起数, 再生止数, 高压, 中压, 站点, 低压, 罐储气, 误差, 电量总峰, 电量总谷, 电量总平, 电量正无功, 电量反无功, 电量商峰, 电量商谷, 日期, 电量商平, 普通水, 消防水, 压缩机油, 压缩机油新增, 机械油, 机械油新增, 煤油, 煤油新增, 特殊用油, 白班夜班, 特殊用油新增, 一号压缩机开, 一号压缩机关, 一号压缩机累计, 二号压缩机开, 二号压缩机关, 二号压缩机累计, 三号压缩机开, 三号压缩机关, 三号压缩机累计, 班组, 四号压缩机开, 四号压缩机关, 四号压缩机累计, 更新时间, 接班底数, 交班底数, 生产气量, 现金气量 ");
strSql.Append(" from [CNGQT].[dbo].[CW_每班统计]");
strSql.Append(" where ID=@ID");
SqlParameter[] parameters = {
new SqlParameter("@ID", SqlDbType.Int,4)
};
parameters[0].Value = ID;
CW_每班统计 model = new CW_每班统计();
DataSet ds = dal.RunSqlDataSet(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
{
model.SX_ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
}
if (ds.Tables[0].Rows[0]["预购刷卡气量"].ToString() != "")
{
model.SX_预购刷卡气量 = double.Parse(ds.Tables[0].Rows[0]["预购刷卡气量"].ToString());
}
if (ds.Tables[0].Rows[0]["记帐刷卡气量"].ToString() != "")
{
model.SX_记帐刷卡气量 = double.Parse(ds.Tables[0].Rows[0]["记帐刷卡气量"].ToString());
}
if (ds.Tables[0].Rows[0]["手工记账气量"].ToString() != "")
{
model.SX_手工记账气量 = double.Parse(ds.Tables[0].Rows[0]["手工记账气量"].ToString());
}
if (ds.Tables[0].Rows[0]["报损气量"].ToString() != "")
{
model.SX_报损气量 = double.Parse(ds.Tables[0].Rows[0]["报损气量"].ToString());
}
if (ds.Tables[0].Rows[0]["进站起数"].ToString() != "")
{
model.SX_进站起数 = double.Parse(ds.Tables[0].Rows[0]["进站起数"].ToString());
}
if (ds.Tables[0].Rows[0]["进站止数"].ToString() != "")
{
model.SX_进站止数 = double.Parse(ds.Tables[0].Rows[0]["进站止数"].ToString());
}
if (ds.Tables[0].Rows[0]["再生起数"].ToString() != "")
{
model.SX_再生起数 = double.Parse(ds.Tables[0].Rows[0]["再生起数"].ToString());
}
if (ds.Tables[0].Rows[0]["再生止数"].ToString() != "")
{
model.SX_再生止数 = double.Parse(ds.Tables[0].Rows[0]["再生止数"].ToString());
}
if (ds.Tables[0].Rows[0]["高压"].ToString() != "")
{
model.SX_高压 = double.Parse(ds.Tables[0].Rows[0]["高压"].ToString());
}
if (ds.Tables[0].Rows[0]["中压"].ToString() != "")
{
model.SX_中压 = double.Parse(ds.Tables[0].Rows[0]["中压"].ToString());
}
if (ds.Tables[0].Rows[0]["站点"].ToString() != "")
{
model.SX_站点 = int.Parse(ds.Tables[0].Rows[0]["站点"].ToString());
}
if (ds.Tables[0].Rows[0]["低压"].ToString() != "")
{
model.SX_低压 = double.Parse(ds.Tables[0].Rows[0]["低压"].ToString());
}
if (ds.Tables[0].Rows[0]["罐储气"].ToString() != "")
{
model.SX_罐储气 = double.Parse(ds.Tables[0].Rows[0]["罐储气"].ToString());
}
if (ds.Tables[0].Rows[0]["误差"].ToString() != "")
{
model.SX_误差 = double.Parse(ds.Tables[0].Rows[0]["误差"].ToString());
}
if (ds.Tables[0].Rows[0]["电量总峰"].ToString() != "")
{
model.SX_电量总峰 = double.Parse(ds.Tables[0].Rows[0]["电量总峰"].ToString());
}
if (ds.Tables[0].Rows[0]["电量总谷"].ToString() != "")
{
model.SX_电量总谷 = double.Parse(ds.Tables[0].Rows[0]["电量总谷"].ToString());
}
if (ds.Tables[0].Rows[0]["电量总平"].ToString() != "")
{
model.SX_电量总平 = double.Parse(ds.Tables[0].Rows[0]["电量总平"].ToString());
}
if (ds.Tables[0].Rows[0]["电量正无功"].ToString() != "")
{
model.SX_电量正无功 = double.Parse(ds.Tables[0].Rows[0]["电量正无功"].ToString());
}
if (ds.Tables[0].Rows[0]["电量反无功"].ToString() != "")
{
model.SX_电量反无功 = double.Parse(ds.Tables[0].Rows[0]["电量反无功"].ToString());
}
if (ds.Tables[0].Rows[0]["电量商峰"].ToString() != "")
{
model.SX_电量商峰 = double.Parse(ds.Tables[0].Rows[0]["电量商峰"].ToString());
}
if (ds.Tables[0].Rows[0]["电量商谷"].ToString() != "")
{
model.SX_电量商谷 = double.Parse(ds.Tables[0].Rows[0]["电量商谷"].ToString());
}
if (ds.Tables[0].Rows[0]["日期"].ToString() != "")
{
model.SX_日期 = DateTime.Parse(ds.Tables[0].Rows[0]["日期"].ToString());
}
if (ds.Tables[0].Rows[0]["电量商平"].ToString() != "")
{
model.SX_电量商平 = double.Parse(ds.Tables[0].Rows[0]["电量商平"].ToString());
}
if (ds.Tables[0].Rows[0]["普通水"].ToString() != "")
{
model.SX_普通水 = double.Parse(ds.Tables[0].Rows[0]["普通水"].ToString());
}
if (ds.Tables[0].Rows[0]["消防水"].ToString() != "")
{
model.SX_消防水 = double.Parse(ds.Tables[0].Rows[0]["消防水"].ToString());
}
if (ds.Tables[0].Rows[0]["压缩机油"].ToString() != "")
{
model.SX_压缩机油 = double.Parse(ds.Tables[0].Rows[0]["压缩机油"].ToString());
}
if (ds.Tables[0].Rows[0]["压缩机油新增"].ToString() != "")
{
model.SX_压缩机油新增 = double.Parse(ds.Tables[0].Rows[0]["压缩机油新增"].ToString());
}
if (ds.Tables[0].Rows[0]["机械油"].ToString() != "")
{
model.SX_机械油 = double.Parse(ds.Tables[0].Rows[0]["机械油"].ToString());
}
if (ds.Tables[0].Rows[0]["机械油新增"].ToString() != "")
{
model.SX_机械油新增 = double.Parse(ds.Tables[0].Rows[0]["机械油新增"].ToString());
}
if (ds.Tables[0].Rows[0]["煤油"].ToString() != "")
{
model.SX_煤油 = double.Parse(ds.Tables[0].Rows[0]["煤油"].ToString());
}
if (ds.Tables[0].Rows[0]["煤油新增"].ToString() != "")
{
model.SX_煤油新增 = double.Parse(ds.Tables[0].Rows[0]["煤油新增"].ToString());
}
if (ds.Tables[0].Rows[0]["特殊用油"].ToString() != "")
{
model.SX_特殊用油 = double.Parse(ds.Tables[0].Rows[0]["特殊用油"].ToString());
}
model.SX_白班夜班 = ds.Tables[0].Rows[0]["白班夜班"].ToString();
if (ds.Tables[0].Rows[0]["特殊用油新增"].ToString() != "")
{
model.SX_特殊用油新增 = double.Parse(ds.Tables[0].Rows[0]["特殊用油新增"].ToString());
}
if (ds.Tables[0].Rows[0]["一号压缩机开"].ToString() != "")
{
model.SX_一号压缩机开 = double.Parse(ds.Tables[0].Rows[0]["一号压缩机开"].ToString());
}
if (ds.Tables[0].Rows[0]["一号压缩机关"].ToString() != "")
{
model.SX_一号压缩机关 = double.Parse(ds.Tables[0].Rows[0]["一号压缩机关"].ToString());
}
if (ds.Tables[0].Rows[0]["一号压缩机累计"].ToString() != "")
{
model.SX_一号压缩机累计 = double.Parse(ds.Tables[0].Rows[0]["一号压缩机累计"].ToString());
}
if (ds.Tables[0].Rows[0]["二号压缩机开"].ToString() != "")
{
model.SX_二号压缩机开 = double.Parse(ds.Tables[0].Rows[0]["二号压缩机开"].ToString());
}
if (ds.Tables[0].Rows[0]["二号压缩机关"].ToString() != "")
{
model.SX_二号压缩机关 = double.Parse(ds.Tables[0].Rows[0]["二号压缩机关"].ToString());
}
if (ds.Tables[0].Rows[0]["二号压缩机累计"].ToString() != "")
{
model.SX_二号压缩机累计 = double.Parse(ds.Tables[0].Rows[0]["二号压缩机累计"].ToString());
}
if (ds.Tables[0].Rows[0]["三号压缩机开"].ToString() != "")
{
model.SX_三号压缩机开 = double.Parse(ds.Tables[0].Rows[0]["三号压缩机开"].ToString());
}
if (ds.Tables[0].Rows[0]["三号压缩机关"].ToString() != "")
{
model.SX_三号压缩机关 = double.Parse(ds.Tables[0].Rows[0]["三号压缩机关"].ToString());
}
if (ds.Tables[0].Rows[0]["三号压缩机累计"].ToString() != "")
{
model.SX_三号压缩机累计 = double.Parse(ds.Tables[0].Rows[0]["三号压缩机累计"].ToString());
}
if (ds.Tables[0].Rows[0]["班组"].ToString() != "")
{
model.SX_班组 = int.Parse(ds.Tables[0].Rows[0]["班组"].ToString());
}
if (ds.Tables[0].Rows[0]["四号压缩机开"].ToString() != "")
{
model.SX_四号压缩机开 = double.Parse(ds.Tables[0].Rows[0]["四号压缩机开"].ToString());
}
if (ds.Tables[0].Rows[0]["四号压缩机关"].ToString() != "")
{
model.SX_四号压缩机关 = double.Parse(ds.Tables[0].Rows[0]["四号压缩机关"].ToString());
}
if (ds.Tables[0].Rows[0]["四号压缩机累计"].ToString() != "")
{
model.SX_四号压缩机累计 = double.Parse(ds.Tables[0].Rows[0]["四号压缩机累计"].ToString());
}
if (ds.Tables[0].Rows[0]["更新时间"].ToString() != "")
{
model.SX_更新时间 = DateTime.Parse(ds.Tables[0].Rows[0]["更新时间"].ToString());
}
if (ds.Tables[0].Rows[0]["接班底数"].ToString() != "")
{
model.SX_接班底数 = double.Parse(ds.Tables[0].Rows[0]["接班底数"].ToString());
}
if (ds.Tables[0].Rows[0]["交班底数"].ToString() != "")
{
model.SX_交班底数 = double.Parse(ds.Tables[0].Rows[0]["交班底数"].ToString());
}
if (ds.Tables[0].Rows[0]["生产气量"].ToString() != "")
{
model.SX_生产气量 = double.Parse(ds.Tables[0].Rows[0]["生产气量"].ToString());
}
if (ds.Tables[0].Rows[0]["现金气量"].ToString() != "")
{
model.SX_现金气量 = double.Parse(ds.Tables[0].Rows[0]["现金气量"].ToString());
}
return model;
}
else
{
return null;
}
}
