/// <summary>
/// 获取最近一小时站点信息
/// </summary>
/// <returns></returns>
public static DataTable getOnetimegas()
{
DAL dal = new DAL();
string gas = "";
DataTable dt = new DataTable();
dt.Columns.Add("站点");
dt.Columns.Add("销售气量");
dt.Columns.Add("当班班组");
int n = 0;
for (int i = 0; i < 2; i++)//循环3次,统计3个站点数据
{
DataRow row = dt.NewRow();
n = i + 2;
if (n == 2) { row["站点"] = "南高"; }
if (n == 3) { row["站点"] = "西路"; }
if (n == 4) { row["站点"] = "麻柳沱"; }
gas = dal.RunSqlGetID("SELECT sum([NowGas]) as 气量 FROM [CSERVER].[dbo].[tempGAS] where StationNO =" + n.ToString() + " and TradeDate > dateadd(hh,-1,getdate()) ");
if (gas == null) { gas = "0"; }
row["销售气量"] = gas;
row["当班班组"] = dal.RunSqlGetID("SELECT top 1 [BZNO] FROM [CSERVER].[dbo].[DateSum] where StationNO=" + n.ToString() + " ORDER BY SxBTime DESC");
dt.Rows.Add(row);
}
return dt;
}
/// <summary>
/// 上一笔定购单
/// </summary>
/// <param name="context"></param>
public void CG_last(HttpContext context)
{
string retext = "";
string code = context.Request["code"];
string SQL = "SELECT top 1 [单号] FROM [CNGCL].[dbo].[CL_采购订单] order by 单号";
DAL dal = new DAL();
string min = dal.RunSqlGetID(SQL);
if (code == min)
{
retext = "{\"err\":{\"ISok\":false,\"msg\":\"已经是第一笔订单!\"},\"data\":[],\"minxi\":{\"total\":0,\"rows\":[] }}";
context.Response.Write(retext);
return;
}
SQL = "SELECT top 1 [单号] FROM [CNGCL].[dbo].[CL_采购订单] where 单号<'" + code + "' order by 单号 desc";
code = dal.RunSqlGetID(SQL);
retext = CG_json(code);
context.Response.Write(retext);
}
protected void Page_Load(object sender, EventArgs e)
{
DAL dal = new DAL();
string SQL = "SELECT TOP 1 convert(varchar(20),[时间],120) as 时间 FROM [CNGTZ].[dbo].[TZ_人员_考勤处理时间] ORDER BY 时间 DESC";
string val = dal.RunSqlGetID(SQL);
if (val == null)
{
val = "";
}
cxdate = val;
}
//通过 日期 找订单数据
public void CG_date(HttpContext context)
{
string retext = "{\"err\":{\"ISok\":true,\"msg\":\"未找到订单数据!\"},\"data\":订单数据对象,\"minxi\":明细对象}";
string date = context.Request["date"];
string SQL = "SELECT TOP 1 [单号] FROM [CNGCL].[dbo].[CL_采购订单] where 制单日期>='" + date + "' ORDER BY 单号";
DAL dal = new DAL();
string code = dal.RunSqlGetID(SQL);
if (code == null)
{
retext = "{\"err\":{\"ISok\":false,\"msg\":\"未找到订单数据!\"},\"data\":[],\"minxi\":{\"total\":0,\"rows\":[] }}";
context.Response.Write(retext);
return;
}
retext = CG_json(code);
context.Response.Write(retext);
}
/// <summary>
/// 验证用户签名
/// </summary>
/// <param name="user">用户名</param>
/// <param name="pw">签名密码</param>
/// <returns></returns>
public static bool UserQianMing(string user, string pw)
{
//用参数传入方式,可以防SQL注入
string sql = "SELECT [权限] FROM [WebApp].[dbo].[DL_用户名] where 用户名=@参数一 and 签名密码=@参数二";
SqlParameter par1 = new SqlParameter("@参数一", user.Trim());
SqlParameter par2 = new SqlParameter("@参数二", Sys.getMd5(pw.Trim()));
SqlParameter[] par = { par1, par2 };
DAL dal = new DAL();
string qx = dal.RunSqlGetID(sql, par);
if (qx == null)
{
qx = "";
return (false);
}
else
{
return (true);
}
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
context.Response.Charset = "utf-8";
var file = context.Request.Files["Filedata"];//文件 对象
string userID = context.Request["userID"];//上传用户
string user = context.Request["user"];//上传用户
string imgname = context.Request["imgname"];//图片名
string imgID = context.Request["imgID"];//相片ID 如果为0 表示 没有上传记录
string savePaht = context.Request.PhysicalApplicationPath + "CNGTZ\\userimg\\" + imgname + ".jpg"; //略缩图储存路径
string sourcePaht = context.Request.PhysicalApplicationPath + "CNGTZ\\userimg\\source\\" + imgname + ".jpg"; //原图储存路径
//string xnpath =context.Request.Path;
DAL dal = new DAL();
if (file != null)
{
file.SaveAs(sourcePaht);
ImageClass.MakeThumbnail(sourcePaht, savePaht, 100, 130, "HW");//生成略缩图
FileItem fi = FileManager.GetItemInfo(savePaht);
if (fi.Name.Length > 0)//判断略缩图是否创建完成
{
string SQL1 = "", SQL2 = "", SQL3 = "";
if (imgID != "0")
{
SQL1 = "UPDATE [CNGTZ].[dbo].[TZ_图片]SET [类型] =@类型,[图片] =@图片,[备注] =@备注,[上传用户] =@上传用户,[所属用户] =@所属用户,[更新时间] = getdate() WHERE ID=" + imgID;
}
else
{
SQL1 = @"INSERT INTO [CNGTZ].[dbo].[TZ_图片]([类型],[图片],[备注],[上传用户],[所属用户],[更新时间])VALUES(@类型,@图片,@备注,@上传用户,@所属用户,getdate())";
SQL2 = @"SELECT [ID] FROM [CNGTZ].[dbo].[TZ_图片]where 类型='人员' AND [所属用户]='" + imgname + "'";//查询是否已经上传过相片
SQL3 = @"UPDATE [CNGTZ].[dbo].[TZ_人员_基本信息] SET [相片ID] ={0}, 更新时间 = getdate() WHERE ID=" + userID;
}
try
{
dal.TranBegin();//开始事务
SqlParameter par1 = new SqlParameter("@类型", "人员");
SqlParameter par2 = new SqlParameter("@图片", savePaht);
SqlParameter par3 = new SqlParameter("@备注", "");
SqlParameter par4 = new SqlParameter("@上传用户", user);
SqlParameter par5 = new SqlParameter("@所属用户", imgname);
dal.ExeSql(SQL1, new SqlParameter[] { par1, par2, par3, par4, par5 });//执行语句
if (imgID == "0")
{
string ID = dal.RunSqlGetID(SQL2);
SQL3 = string.Format(SQL3, ID);
dal.ExeSql(SQL3);
}
dal.TranCommit();//提交事务
Sys.addSteps(user, "上传员工相片:[" + fi.Name + "]", "人员台账");
//下面这句代码缺少的话，上传成功后上传队列的显示不会自动消失
context.Response.Write("1");
}
catch (Exception)
{
dal.TranRollBack();//回滚事务
context.Response.Write("0");//数据库操作失败
}
}
else
{
context.Response.Write("0"); //如果创建文件失败
}
}
else
{
context.Response.Write("0");//上传文件失败
}
}
/// <summary>
/// 源图片获取
/// </summary>
/// <param name="context"></param>
public void Img_getsource(HttpContext context)
{
string ID = context.Request["ID"];//图片ID
DAL dal = new DAL();
string fiPaht = dal.RunSqlGetID("SELECT [图片] FROM [CNGTZ].[dbo].[TZ_图片] where ID=" + ID);
if (fiPaht == null)
{
context.Response.Write("对不起,服务器,没有找到此文件!");
return;
}
fiPaht = fiPaht.Replace("userimg", "userimg\\source");
if (System.IO.File.Exists(fiPaht) == false)
{
context.Response.Write("对不起,服务器,没有找到此文件!");
return;
}
//string filename = "XX.jpg";
Byte[] imgbyte = System.IO.File.ReadAllBytes(fiPaht);//这里只要 得到图片的字节流就可以，如：FileStream f=new FileStream(path);这方法也可以实现
context.Response.ContentType = "image/jpeg";
context.Response.BinaryWrite(imgbyte);
}
/// <summary>
/// 新增员工
/// </summary>
/// <param name="context"></param>
public void user_new(HttpContext context)
{
string name = context.Request["name"];
name = name.Trim();
string py = PY.GetTop(name);
string SQL = "INSERT INTO [CNGTZ].[dbo].[TZ_人员]([姓名],[拼音],[是否在职],[所属部门],[更新时间]) VALUES(@name,@拼音,1,'03',getdate())";
DAL dal = new DAL();
SqlParameter par1 = new SqlParameter("@name", name);
SqlParameter par2 = new SqlParameter("@拼音", py);
dal.RunSql(SQL, new SqlParameter[] { par1, par2 });
if (dal.ExecuteRowCount == 1)
{
SQL = "SELECT [ID] FROM [CNGTZ].[dbo].[TZ_人员] where 姓名='" + name + "'";
string ID = dal.RunSqlGetID(SQL);
SQL = "INSERT INTO [CNGTZ].[dbo].[TZ_人员_基本信息] ([ID],[更新时间])VALUES(" + ID + ",getdate())";
dal.RunSql(SQL);
SQL = "INSERT INTO [CNGTZ].[dbo].[TZ_人员_档案1] ([ID],[更新时间])VALUES(" + ID + ",getdate())";
dal.RunSql(SQL);
SQL = "INSERT INTO [CNGTZ].[dbo].[TZ_人员_档案2] ([ID],[更新时间])VALUES(" + ID + ",getdate())";
dal.RunSql(SQL);
context.Response.Write("true");
}
else
{
context.Response.Write("false");
}
}
private string Chart_站点年度生产(string year ,string zd)
{
string retext = "{ \"ok\":true,\"msg\":\"\",\"sc1\":@替换对象1@,\"sc2\":@替换对象2@,\"sum\":@替换对象3@ }";
string SQL = @"SELECT sum(生产气量) as 生产气量,
'输差率'=
CASE sum([购进数]+[修正值]) when 0 then 0
else round((sum([购进数])+sum([修正值])-sum([生产气量])-sum([库存数]))/sum([购进数]+[修正值])*100,2) end FROM [CNGQT].[dbo].[CW_月度统计] where 年=@年度@ and 站点=@站点@ GROUP BY 月 order by 月";
SQL = SQL.Replace("@年度@", year);
SQL = SQL.Replace("@站点@", zd);
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL);
if (dt == null)
{
retext = retext.Replace(":true", ":false");
return retext;
}
double[] sc1 = new double[12];
double[] sc2 = new double[12];
for (int i = 0; i < dt.Rows.Count; i++)
{
sc1[i] = double.Parse(dt.Rows[i]["生产气量"].ToString());
sc2[i] = double.Parse(dt.Rows[i]["输差率"].ToString());
}
SQL = "SELECT sum(生产气量) as 生产气量 FROM [CNGQT].[dbo].[CW_月度统计] where 年=@年度@ and 站点=@站点@";
SQL = SQL.Replace("@年度@", year);
SQL = SQL.Replace("@站点@", zd);
string yearSum = dal.RunSqlGetID(SQL);
string sc1Json = Json.Json_转换到json<double[]>(sc1);
string sc2Json = Json.Json_转换到json<double[]>(sc2);
retext = retext.Replace("@替换对象1@", sc1Json);
retext = retext.Replace("@替换对象2@", sc2Json);
retext = retext.Replace("@替换对象3@", yearSum);
return retext;
}
/// <summary>
/// 根据 单号 查找出库数据 返回json对象
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
private string CK_json(string code)
{
string retext = "{\"err\":{\"ISok\":true,\"msg\":\"未找到单据数据!\"},\"data\":订单数据对象,\"minxi\":明细对象}";
string SQL = "SELECT [ID],[单号],[仓库],[来往单位],[制单日期],[经手人],[总金额],[备注],[领料人],[审核人]FROM [CNGCL].[dbo].[CL_仓库单据] where 进出='出库' and 单据类型 ='物资出库' and 单号='" + code.Trim().ToUpper() + "'";
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL);
string temp = "";
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
string weixiu = dr["来往单位"].ToString();
string data = "{{\"单号\":\"{0}\",\"仓库\":\"{1}\",\"出库分类\":\"{2}\",\"制单日期\":\"{3}\",\"经手人\":\"{4}\",\"总金额\":\"{5}\",\"备注\":\"{6}\",\"领料人\":\"{7}\",\"审核人\":\"{8}\",\"维修编号\":\"@维修编号@\" }}";
temp = string.Format(data, dr["单号"].ToString(), dr["仓库"].ToString(), dr["来往单位"].ToString(), DateTime.Parse(dr["制单日期"].ToString()).ToString("yyyy-MM-dd"), dr["经手人"].ToString(), dr["总金额"].ToString(), dr["备注"].ToString().Replace("\r\n", "\\r\\n"), dr["领料人"].ToString(), dr["审核人"].ToString());
retext = retext.Replace("订单数据对象", temp);
if (weixiu == "维修维护")
{
SQL = "SELECT [维修编号] FROM [CNGCL].[dbo].[CL_维修出库] where 出库单号='" + code.Trim().ToUpper() + "'";
weixiu = dal.RunSqlGetID(SQL);
retext = retext.Replace("@维修编号@", weixiu);
}
else
{
retext = retext.Replace("@维修编号@", "");
}
SQL = "SELECT [ID],[物品名称],[物品编号],[分类名称],[规格] ,[单位],[数量],[单价],[小计],[批号],[备注] FROM [CNGCL].[dbo].[CL_仓库明细] where 单号='" + code.Trim().ToUpper() + "'";
dt = dal.RunSqlDataTable(SQL);
temp = "{\"total\":100,\"rows\":[] }";
if (dt.Rows.Count > 0)
{
temp = temp.Replace("[]", Json.TableToJson(dt));
}
retext = retext.Replace("明细对象", temp);
return retext;
}
else
{
retext = "{\"err\":{\"ISok\":false,\"msg\":\"未找到单据数据!\"},\"data\":[],\"minxi\":{\"total\":0,\"rows\":[] }}";
return retext;
}
}
/// <summary>
/// 检查数据是否上传
/// </summary>
/// <param name="context"></param>
public void scDate(HttpContext context)
{
string t1 = context.Request["time"];
string zd = context.Request["zd"];
DAL dal = new DAL();
string SQL = "SELECT top 1 convert(varchar(10), [日期], 120) as 日期 FROM [CNGQT].[dbo].[SC_班销售统计]where 站点=" + zd + " order by 日期 desc";
string t2 = dal.RunSqlGetID(SQL);
if (DateTime.Parse(t1) > DateTime.Parse(t2))
{
SQL = "最后上报数据日期为：【" + t2 + "】，统计数据可能不完整！<br>请确认是否要继续查询？";
}
else
{
SQL = "yes";
}
context.Response.Write(SQL);
}
/// <summary>
/// 取站点区间数据
/// </summary>
/// <param name="context"></param>
public void getZDreg(HttpContext context)
{
string temp = "";
string text = "{\"err\":{\"ok\":false,\"mes\":\"\"}}";
string retext = "{\"err\":{\"ok\":true,\"mes\":\"\"},\"CanShu\":返回参数对象,\"title\":\"头对象\",\"top1\":流量计对象,\"TingYe\":停业对象,\"gasFL\":销售分类对象,\"YaShoJi\":压缩机对象,\"YouLei\":油类对象,\"ShuiLei\":水类对象,\"DianLei\":电类对象,\"jizhang\":记账单位对象}";
DAL dal = new DAL();
DateTime dtstart = DateTime.Parse(context.Request["datestart"]); //起始日期
DateTime dtend = DateTime.Parse(context.Request["dateend"]); //结束日期
int day = (dtend - dtstart).Days + 1;
int zd = Convert.ToInt32(context.Request["zd"].ToString());//站点
string title = dtstart.ToString("yyyy年MM月dd日") + " 至 " + dtend.ToString("yyyy年MM月dd日") + " 共:" + day.ToString() + "天";
if (dtstart > dtend)
{
text = "{\"err\":{\"ok\":false,\"mes\":\"起始日期在结束日期之后!\"}}";
context.Response.Write(text);
return;
}
temp = "{\"zd\":" + zd.ToString() + ",\"time1\":\"" + dtstart.ToShortDateString() + "\",\"time2\":\"" + dtend.ToShortDateString() + "\"}";
retext = retext.Replace("返回参数对象", temp);
dtstart = dtstart.AddDays(-1);//起始班 的上一天
int bz_str = 0, bz_end = 0;
string ID_str = "0", ID_end = "0", SQL = "";
temp = "";
CW_每班统计 modstart; //起始数据 起始班 的上一个夜班
CW_每班统计 modend; //结束数据
CW_每班统计 modsum; //累计数据
xzz zdxz;//区间修正值累计
if (zd == 4)
{
bz_str = ZDGAS.getBZno(dtstart);
bz_end = ZDGAS.getBZno(dtend);
title = "区间统计：麻柳沱CNG站 " + title;
}
else
{
bz_str = ZDGAS.getBZno(dtstart, zd, true);
bz_end = ZDGAS.getBZno(dtend, zd, true);
if (zd == 2) { title = "区间统计：南高CNG站 " + title; }
if (zd == 3) { title = "区间统计：江阳西路CNG站 " + title; }
}
retext = retext.Replace("头对象", title);
SQL = "SELECT ID FROM [CNGQT].[dbo].[CW_每班统计] where 站点=@站点 AND 日期=@日期 AND 班组=@班组";
SqlParameter par1 = new SqlParameter("@站点", zd);
SqlParameter par2 = new SqlParameter("@日期", dtstart);
SqlParameter par3 = new SqlParameter("@班组", bz_str);
ID_str = dal.RunSqlGetID(SQL, new SqlParameter[] { par1, par2, par3 });
par1 = new SqlParameter("@站点", zd);
par2 = new SqlParameter("@日期", dtend);
par3 = new SqlParameter("@班组", bz_end);
ID_end = dal.RunSqlGetID(SQL, new SqlParameter[] { par1, par2, par3 });
if (ID_str == null)
{
text = "{\"err\":{\"ok\":false,\"mes\":\"没有找到起始日期数据!\"}}";
context.Response.Write(text);
return;
}
if (ID_end == null)
{
text = "{\"err\":{\"ok\":false,\"mes\":\"没有找到结束日期数据!\"}}";
context.Response.Write(text);
return;
}
modstart = ZDGAS.GetModel_CW_每班统计(int.Parse(ID_str));
modend = ZDGAS.GetModel_CW_每班统计(int.Parse(ID_end));
modsum = getZDregsum(modstart, modend);
zdxz = getZDregXzz(modstart, modend);
temp = jsonLiuLianJi(modstart, modend, modsum, zdxz);
retext = retext.Replace("流量计对象", temp);
temp = jsonGasFenLei(modsum, day);
retext = retext.Replace("销售分类对象", temp);
temp = jsonYouLei(modstart, modend, modsum);
retext = retext.Replace("油类对象", temp);
temp = jsonShuiLei(modstart, modend, modsum, day, zdxz);
retext = retext.Replace("水类对象", temp);
temp = jsonDianLei(modstart, modend, modsum, zdxz);
retext = retext.Replace("电类对象", temp);
temp = jsonYaShoJi(modstart, modend, modsum);
retext = retext.Replace("压缩机对象", temp);
temp = jsonJiZhang(modstart, modend);
retext = retext.Replace("记账单位对象", temp);
temp = jsonTingYe(modstart, modend);
retext = retext.Replace("停业对象", temp);
text = "{\"err\":{\"ok\":true,\"mes\":\"\"}}";
context.Response.Write(retext);
}
/// <summary>
/// 补签考勤操作 取签到时间
/// </summary>
/// <param name="context"></param>
public void KQ_XG_BuQiang(HttpContext context)
{
string index = context.Request["index"];
string ID = context.Request["ID"];
string SQL = "SELECT convert(varchar(20), @替换@,120) as 时间 FROM [CNGTZ].[dbo].[TZ_人员_考勤表] where ID =";
if (index == "4")
{
SQL = SQL.Replace("@替换@", "上班时间") + ID;
}
if (index == "5")
{
SQL = SQL.Replace("@替换@", "下班时间") + ID;
}
DAL dal = new DAL();
string retext = dal.RunSqlGetID(SQL);
context.Response.Write(retext);
}
//返回 最后处理考勤的时间
public void KQ_time(HttpContext context)
{
DAL dal = new DAL();
string SQL = "SELECT TOP 1 convert(varchar(20),[时间],120) as 时间 FROM [CNGTZ].[dbo].[TZ_人员_考勤处理时间] ORDER BY 时间 DESC";
string val = dal.RunSqlGetID(SQL);
if (val==null)
{
val = "";
}
context.Response.Write(val);
}
/// <summary>
/// 删除版本控制记录
/// </summary>
/// <param name="context"></param>
public void VerDel(HttpContext context)
{
string SQL = "DELETE FROM [WebApp].[dbo].[WEB_版本控制] WHERE 版本号=@版本号";
string ver = context.Request["ver"];
string conf = context.Server.MapPath("~/Public/CNGconfig.xml");
string oldver = Sys.xmlRead(conf, "缓存配置", "全局版本", "1.00");//读取版本号
string retext = "{{ \"ok\":{0},\"msg\":\"{1}\",\"data\":@替换对象@ }}";
DAL dal = new DAL();
SqlParameter par1 = new SqlParameter("版本号", ver);
dal.RunSql(SQL, new SqlParameter[] { par1 });
if (dal.ExecuteRowCount == 1)
{
if (double.Parse(ver) >= double.Parse(oldver))
{
SQL = "SELECT TOP 1 [版本号] FROM [WebApp].[dbo].[WEB_版本控制] ORDER BY 版本号 DESC";
oldver = dal.RunSqlGetID(SQL) ?? "1.00";
Sys.xmlAdd(conf, "缓存配置", "全局版本", oldver);
}
retext = string.Format(retext, "true", "更改数据成功，当前版本号为：" + oldver);
retext = retext.Replace("@替换对象@", "0");
}
else
{
retext = string.Format(retext, "false", "错误信息：" + dal.ErrorMsg);
retext = retext.Replace("@替换对象@", "0");
}
context.Response.Write(retext);
}
/// <summary>
/// 设置修正值
/// </summary>
/// <param name="context"></param>
public void SetXzz(HttpContext context)
{
int zd = int.Parse(context.Request["zd"]);
int bz = int.Parse(context.Request["bz"]);
DateTime day = DateTime.Parse(context.Request["day"]);
string name = context.Request["name"];
double val = double.Parse(context.Request["val"]);
DAL dal = new DAL();
string SQL = "SELECT [ID] FROM [CNGQT].[dbo].[CW_每班修正] where 日期=@日期 and 站点=@站点 and 班组=@班组";
SqlParameter par1 = new SqlParameter("日期", day);
SqlParameter par2 = new SqlParameter("站点", zd);
SqlParameter par3 = new SqlParameter("班组", bz);
SqlParameter par4 = new SqlParameter("val", val);
string id = dal.RunSqlGetID(SQL, new SqlParameter[] { par1, par2, par3 });
if (id == null)
{
SQL = "INSERT INTO [CNGQT].[dbo].[CW_每班修正](日期,站点,班组,@参数@,更新时间)VALUES(@日期,@站点,@班组,@val,getdate())";
SQL = SQL.Replace("@参数@", name);
dal.RunSql(SQL, new SqlParameter[] { par1, par2, par3, par4 });
}
else
{
SQL = "UPDATE [CNGQT].[dbo].[CW_每班修正] SET @参数@ = @val,更新时间=getdate() WHERE ID=@ID";
SQL = SQL.Replace("@参数@", name);
dal.RunSql(SQL, new SqlParameter[] { new SqlParameter("ID", id) ,par4});
}
context.Response.Write("ok");
}
/// <summary>
/// 库存明细查询
/// </summary>
/// <param name="context"></param>
public void KC_mingxi(HttpContext context)
{
int row = int.Parse(context.Request["rows"].ToString());//每页函数
int page = int.Parse(context.Request["page"].ToString());//第几页
string code = context.Request["code"];
DateTime date1 = DateTime.Parse(context.Request["date1"].ToString());//起始时间
DateTime date2 = DateTime.Parse(context.Request["date2"].ToString());//结束时间
string SQL = @"SELECT CONVERT(varchar(10), b.[制单日期], 120) as 日期
,a.[单号]
,b.[单据类型]
,b.[进仓库]
,b.[出仓库]
,a.[物品编号]
,a.[物品名称]
,a.[规格]
,a.[单位]
,a.[数量]
,a.[单价]
,a.[小计]
,a.[备注]
FROM [CNGCL].[dbo].[CL_仓库明细] a LEFT JOIN [CNGCL].[dbo].[CL_仓库单据] b ON a.单号=b.单号
where a.[物品编号]=@物品编号 and b.[制单日期] between @起始时间 and @结束时间 order by b.[制单日期]";
string SQL_rk = @"SELECT SUM(a.[数量]) AS 入库数 FROM [CNGCL].[dbo].[CL_仓库明细] a LEFT JOIN [CNGCL].[dbo].[CL_仓库单据] b ON a.单号=b.单号 where a.[物品编号]=@物品编号 and b.[单据类型]='采购收货' and b.[制单日期] between @起始时间 and @结束时间";
string SQL_ck = @"SELECT SUM(a.[数量]) AS 出库数 FROM [CNGCL].[dbo].[CL_仓库明细] a LEFT JOIN [CNGCL].[dbo].[CL_仓库单据] b ON a.单号=b.单号 where a.[物品编号]=@物品编号 and b.[单据类型]='物资出库' and b.[制单日期] between @起始时间 and @结束时间";
double sum_rk = 0, sum_ck = 0;
string temp = "", rowdx = "[]", footdx = "[]";
DAL dal = new DAL();
SqlParameter par1 = new SqlParameter("@物品编号", code);
SqlParameter par2 = new SqlParameter("@起始时间", date1);
SqlParameter par3 = new SqlParameter("@结束时间", date2);
DataTable dt = dal.RunSqlDataTable(SQL, new SqlParameter[] { par1, par2, par3 });
string rowtext = "{\"total\":" + dt.Rows.Count.ToString() + ",\"rows\":@行数据对象@,\"footer\":@脚对象@}";
if (dt.Rows.Count > 0)
{
rowdx = Json.TableToJson(BLL.tools.GetPagedTable(dt, page, row));// 行对象
temp = dal.RunSqlGetID(SQL_rk, new SqlParameter[] { par1, par2, par3 });
if (temp != null && temp != "")
{
sum_rk = double.Parse(temp);//入库合计数
}
temp = dal.RunSqlGetID(SQL_ck, new SqlParameter[] { par1, par2, par3 });
if (temp != null && temp != "")
{
sum_ck = double.Parse(temp);//出库合计数
}
footdx = string.Format("[{{ \"单号\":\"入库合计:\", \"单据类型\":{0:f}, \"出仓库\":\"出库合计:\", \"物品编号\":{1:f}, \"物品名称\":\"结存数:\", \"规格\":{2:f} }}]", sum_rk, sum_ck, sum_rk - sum_ck);
}
rowtext = rowtext.Replace("@行数据对象@", rowdx);
rowtext = rowtext.Replace("@脚对象@", footdx);
Sys.DebugMes(rowtext);
context.Response.Write(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="context"></param>
public void RK_next(HttpContext context)
{
string retext = "";
string code = context.Request["code"];
string SQL = "SELECT top 1 [单号] FROM [CNGCL].[dbo].[CL_仓库单据] where 进出='入库' and 单据类型 ='采购收货' order by 单号 desc";
DAL dal = new DAL();
string max = dal.RunSqlGetID(SQL);
if (code == max)
{
retext = "{\"err\":{\"ISok\":false,\"msg\":\"已经是最后一笔单据!\"},\"data\":[],\"minxi\":{\"total\":0,\"rows\":[] }}";
context.Response.Write(retext);
return;
}
SQL = "SELECT top 1 [单号] FROM [CNGCL].[dbo].[CL_仓库单据] where 进出='入库' and 单据类型 ='采购收货' and 单号>'" + code + "' order by 单号";
code = dal.RunSqlGetID(SQL);
retext = RK_json(code);
context.Response.Write(retext);
}
/// <summary>
/// 取 入库单据 json
/// </summary>
/// <param name="ISmx">是否明细</param>
/// <returns></returns>
private string getDJ_rk(bool ISmx, DateTime date1, DateTime date2, int row, int page)
{
string SQL = "", SQLsum = "", footdx = "";
if (ISmx) //判断是否是明细
{
SQL = @"SELECT CONVERT(varchar(10), b.[制单日期], 120) as 日期
,a.[单号]
,b.[来往单位] as 供货单位
,b.[进仓库] as 入库仓库
,a.[物品名称]
,a.[规格]
,a.[单位]
,a.[数量]
,a.[单价]
,a.[小计]
,b.[经手人]
,a.[备注]
FROM [CNGCL].[dbo].[CL_仓库明细] a LEFT JOIN [CNGCL].[dbo].[CL_仓库单据] b ON a.单号=b.单号
where b.[制单日期] between @起始时间 and @结束时间 and [进出]='入库' order by a.[单号]";
SQLsum = "SELECT sum(a.[小计]) as 总金额 FROM [CNGCL].[dbo].[CL_仓库明细] a LEFT JOIN [CNGCL].[dbo].[CL_仓库单据] b ON a.单号=b.单号 where b.[制单日期] between @起始时间 and @结束时间 and [进出]='入库' ";
footdx = "[{{ \"单价\":\"合计:\",\"小计\":{0:f} }}]";
}
else
{
SQL = @"SELECT CONVERT(varchar(10),[制单日期],120) 日期
,[单号]
,[来往单位]
,[进仓库] as 入库仓库
,[总金额]
,[经手人]
,[备注]
FROM [CNGCL].[dbo].[CL_仓库单据]
where 单据类型='采购收货' and 制单日期 between @起始时间 and @结束时间
order by 制单日期";
SQLsum = "SELECT sum([总金额]) as 总金额 FROM [CNGCL].[dbo].[CL_仓库单据] where 单据类型='采购收货' and 制单日期 between @起始时间 and @结束时间";
footdx = "[{{ \"入库仓库\":\"合计：\",\"总金额\":{0:f} }}]";
}
double Zsum = 0;
string rowdx = "[]";
DAL dal = new DAL();
SqlParameter par1 = new SqlParameter("@起始时间", date1);
SqlParameter par2 = new SqlParameter("@结束时间", date2);
DataTable dt = dal.RunSqlDataTable(SQL, new SqlParameter[] { par1, par2 });
string rowtext = "{\"total\":" + dt.Rows.Count.ToString() + ",\"rows\":@行数据对象@,\"footer\":@脚对象@}";
if (dt.Rows.Count > 0)
{
Zsum = double.Parse(dal.RunSqlGetID(SQLsum, new SqlParameter[] { par1, par2 })); //求和
rowdx = Json.TableToJson(BLL.tools.GetPagedTable(dt, page, row));// 行对象
//rowtext = rowtext.Replace("@行数据对象@", Json.TableToJson(BLL.tools.GetPagedTable(dt, page, row)));
}
footdx = string.Format(footdx, Zsum); //脚对象
rowtext = rowtext.Replace("@行数据对象@", rowdx);
rowtext = rowtext.Replace("@脚对象@", footdx);
return rowtext;
}
private string Chart_三站年度电量(string year)
{
string retext = "{ \"ok\":true,\"msg\":\"\",\"ds\":@替换对象1@,\"zd\":@替换对象2@,\"sum\":@替换对象3@ }";
string SQL = @"SELECT sum(([电量总峰]+[电量总平]+[电量总谷])*[电系数]) as 耗电量
FROM [CNGQT].[dbo].[CW_月度统计]
where 年=@年度@ GROUP BY 月 order by 月";
SQL = SQL.Replace("@年度@", year);
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL);
if (dt == null)
{
retext = retext.Replace(":true", ":false");
return retext;
}
double[] ds = new double[12];//存储 三站点每月合计销售
for (int i = 0; i < dt.Rows.Count; i++)
{
ds[i] = double.Parse(dt.Rows[i]["耗电量"].ToString());
}
站点数据类型 zd = new 站点数据类型(0);
SQL = @"SELECT sum(([电量总峰]+[电量总平]+[电量总谷])*[电系数]) as 耗电量
FROM [CNGQT].[dbo].[CW_月度统计]
where 年=@年度@ and 站点=@站点@ GROUP BY 月 order by 月";
SQL = SQL.Replace("@年度@", year);
string zdSQL = SQL.Replace("@站点@", "2");
dt = dal.RunSqlDataTable(zdSQL);
for (int i = 0; i < dt.Rows.Count; i++)
{
zd.南高[i] = double.Parse(dt.Rows[i]["耗电量"].ToString());
}
zdSQL = SQL.Replace("@站点@", "3");
dt = dal.RunSqlDataTable(zdSQL);
for (int i = 0; i < dt.Rows.Count; i++)
{
zd.西路[i] = double.Parse(dt.Rows[i]["耗电量"].ToString());
}
zdSQL = SQL.Replace("@站点@", "4");
dt = dal.RunSqlDataTable(zdSQL);
for (int i = 0; i < dt.Rows.Count; i++)
{
zd.麻柳沱[i] = double.Parse(dt.Rows[i]["耗电量"].ToString());
}
SQL = @"SELECT sum(([电量总峰]+[电量总平]+[电量总谷])*[电系数]) as 耗电量
FROM [CNGQT].[dbo].[CW_月度统计]
where 年=@年度@ ";
SQL = SQL.Replace("@年度@", year);
string yearSum = dal.RunSqlGetID(SQL);
string dsJson = Json.Json_转换到json<double[]>(ds);
string zdJson = Json.Json_转换到json<站点数据类型>(zd);
retext = retext.Replace("@替换对象1@", dsJson);
retext = retext.Replace("@替换对象2@", zdJson);
retext = retext.Replace("@替换对象3@", yearSum);
return retext;
}
/// <summary>
/// 查询 调拨单 明细 或 汇总,返回 json row数据
/// </summary>
/// <returns></returns>
private string HZ_mx_db(int row, int page, DateTime date1, DateTime date2, bool ISmx, string ckck, string rkck)
{
string SQL = "", SQLsum = "", footdx = "", rowdx = "[]";
if (ISmx)
{
SQL = @"SELECT CONVERT(varchar(10), b.[制单日期], 120) as 日期
,a.[单号]
,b.[出仓库] as 出库仓库
,b.[进仓库] as 入库仓库
,a.[物品编号]
,a.[物品名称]
,a.[规格]
,a.[单位]
,a.[数量]
,a.[单价]
,a.[小计]
,b.[经手人]
,a.[备注]
FROM [CNGCL].[dbo].[CL_仓库明细] a left join [CNGCL].[dbo].[CL_仓库单据] b on a.[单号]=b.[单号]
where b.[单据类型]='物资调拨' and b.[进仓库]=@入库仓库名 and b.[出仓库]=@出库仓库名 and b.[制单日期] between @起始时间 and @结束时间 order by a.[单号] ";
SQLsum = @"SELECT SUM(a.[小计]) as 合计 FROM [CNGCL].[dbo].[CL_仓库明细] a left join [CNGCL].[dbo].[CL_仓库单据] b on a.[单号]=b.[单号]
where b.[单据类型]='物资调拨' and b.[进仓库]=@入库仓库名 and b.[出仓库]=@出库仓库名 and b.[制单日期] between @起始时间 and @结束时间 ";
footdx = "[{{ \"物品名称\":\"合计金额:\",\"规格\":{0:f} }}]";
}
else
{
SQL = @"SELECT a.[物品名称],a.[规格]
,a.[单位]
,sum(a.[数量]) as 累计数量
,sum(a.[小计]) as 小计
FROM [CNGCL].[dbo].[CL_仓库明细] a left join [CNGCL].[dbo].[CL_仓库单据] b on a.[单号]=b.[单号]
where b.[单据类型]='物资调拨' and b.[进仓库]=@入库仓库名 and b.[出仓库]=@出库仓库名 and b.[制单日期] between @起始时间 and @结束时间
GROUP BY a.物品名称,a.规格,a.单位 ORDER BY a.物品名称,a.规格";
SQLsum = @"SELECT SUM(a.[小计]) FROM [CNGCL].[dbo].[CL_仓库明细] a left join [CNGCL].[dbo].[CL_仓库单据] b on a.[单号]=b.[单号]
where b.[单据类型]='物资调拨' and b.[进仓库]=@入库仓库名 and b.[出仓库]=@出库仓库名 and b.[制单日期] between @起始时间 and @结束时间 ";
footdx = "[{{ \"累计数量\":\"合计金额:\",\"小计\":{0:f} }}]";
}
DAL dal = new DAL();
SqlParameter par1 = new SqlParameter("@入库仓库名", rkck.Trim());
SqlParameter par2 = new SqlParameter("@出库仓库名", ckck.Trim());
SqlParameter par3 = new SqlParameter("@起始时间", date1);
SqlParameter par4 = new SqlParameter("@结束时间", date2);
double mxSum = 0;
DataTable dt = dal.RunSqlDataTable(SQL, new SqlParameter[] { par1, par2, par3, par4 });
string rowtext = "{\"total\":" + dt.Rows.Count.ToString() + ",\"rows\":@行数据对象@,\"footer\":@脚对象@}";
if (dt.Rows.Count > 0)
{
mxSum = double.Parse(dal.RunSqlGetID(SQLsum, new SqlParameter[] { par1, par2, par3, par4 }));
rowdx = Json.TableToJson(BLL.tools.GetPagedTable(dt, page, row));// 行对象
}
footdx = string.Format(footdx, mxSum); //脚对象
rowtext = rowtext.Replace("@行数据对象@", rowdx);
rowtext = rowtext.Replace("@脚对象@", footdx);
return rowtext;
}
private string Chart_站点年度电量(string year, string zd)
{
string retext = "{ \"ok\":true,\"msg\":\"\",\"sc1\":@替换对象1@,\"sc2\":@替换对象2@,\"sum\":@替换对象3@ }";
string SQL = @"SELECT sum(([电量总峰]+[电量总平]+[电量总谷])*[电系数]) as 耗电量
,'单位电耗'=
CASE sum([生产气量]) when 0 then 0
else round(sum(([电量总峰]+[电量总平]+[电量总谷])*[电系数]) /sum([生产气量]),3) end
FROM [CNGQT].[dbo].[CW_月度统计]
where 年=@年度@ and 站点=@站点@ GROUP BY 月 order by 月";
SQL = SQL.Replace("@年度@", year);
SQL = SQL.Replace("@站点@", zd);
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL);
if (dt == null)
{
retext = retext.Replace(":true", ":false");
return retext;
}
double[] sc1 = new double[12];//耗电量
double[] sc2 = new double[12];//单位电耗
for (int i = 0; i < dt.Rows.Count; i++)
{
sc1[i] = double.Parse(dt.Rows[i]["耗电量"].ToString());
sc2[i] = double.Parse(dt.Rows[i]["单位电耗"].ToString());
}
SQL = "SELECT sum(([电量总峰]+[电量总平]+[电量总谷])*[电系数]) as 耗电量 FROM [CNGQT].[dbo].[CW_月度统计] where 年=@年度@ and 站点=@站点@";
SQL = SQL.Replace("@年度@", year);
SQL = SQL.Replace("@站点@", zd);
string yearSum = dal.RunSqlGetID(SQL);
string sc1Json = Json.Json_转换到json<double[]>(sc1);
string sc2Json = Json.Json_转换到json<double[]>(sc2);
retext = retext.Replace("@替换对象1@", sc1Json);
retext = retext.Replace("@替换对象2@", sc2Json);
retext = retext.Replace("@替换对象3@", yearSum);
return retext;
}
//通过 日期 找入库单数据
public void CK_date(HttpContext context)
{
string retext = "{\"err\":{\"ISok\":true,\"msg\":\"未找到入库单数据!\"},\"data\":订单数据对象,\"minxi\":明细对象}";
string date = context.Request["date"];
string cksx = context.Request["cksx"];//仓库缩写
string SQL = "SELECT TOP 1 [单号] FROM [CNGCL].[dbo].[CL_仓库单据] where 进出='出库' and 单据类型 ='物资出库' and 单号 like 'LL" + cksx + "%' and 制单日期>='" + date + "' ORDER BY 单号";
DAL dal = new DAL();
string code = dal.RunSqlGetID(SQL);
if (code == null)
{
retext = "{\"err\":{\"ISok\":false,\"msg\":\"未找到单据数据!\"},\"data\":[],\"minxi\":{\"total\":0,\"rows\":[] }}";
context.Response.Write(retext);
return;
}
retext = CK_json(code);
context.Response.Write(retext);
}
/// <summary>
/// 通过用户ID 查询相片ID
/// </summary>
/// <param name="context"></param>
public void Img_getID(HttpContext context)
{
string ID = context.Request["ID"];//用户ID
string SQL = "SELECT isnull([相片ID],'0') as 相片ID FROM [CNGTZ].[dbo].[TZ_人员_基本信息] where ID=" + ID;
DAL dal = new DAL();
string tpID = dal.RunSqlGetID(SQL);
if (tpID == null)
{
tpID = "0";
}
context.Response.Write(tpID);
}
/// <summary>
/// 最后一笔出库单
/// </summary>
/// <param name="context"></param>
public void CK_max(HttpContext context)
{
string retext = "";
string code = context.Request["code"];
string cksx = context.Request["cksx"];//仓库缩写
string SQL = "SELECT top 1 [单号] FROM [CNGCL].[dbo].[CL_仓库单据] where 进出='出库' and 单据类型 ='物资出库' and 单号 like 'LL" + cksx + "%' order by 单号 desc";
DAL dal = new DAL();
string max = dal.RunSqlGetID(SQL);
if (code == max)
{
retext = "{\"err\":{\"ISok\":false,\"msg\":\"已经是最后一笔单据!\"},\"data\":[],\"minxi\":{\"total\":0,\"rows\":[] }}";
context.Response.Write(retext);
return;
}
retext = CK_json(max);
context.Response.Write(retext);
}
/// <summary>
/// 取站点积分公告
/// </summary>
/// <param name="context"></param>
public void JF_GG_get(HttpContext context)
{
string zd = context.Request["zd"];
string SQL = "SELECT [积分公告] FROM [CNGTZ].[dbo].[TZ_站点] where ID=" + zd;
DAL dal = new DAL();
string txt = dal.RunSqlGetID(SQL);
if (txt == null)
{
txt = "读取公告失败";
}
context.Response.Write(txt);
}
/// <summary>
/// 第一笔定购单
/// </summary>
/// <param name="context"></param>
public void DB_min(HttpContext context)
{
string retext = "";
string code = context.Request["code"];
string SQL = "SELECT top 1 [单号] FROM [CNGCL].[dbo].[CL_仓库单据] where 进出='调拨' and 单据类型 ='物资调拨' order by 单号";
DAL dal = new DAL();
string min = dal.RunSqlGetID(SQL);
if (code == min)
{
retext = "{\"err\":{\"ISok\":false,\"msg\":\"已经是第一笔单据!\"},\"data\":[],\"minxi\":{\"total\":0,\"rows\":[] }}";
context.Response.Write(retext);
return;
}
retext = DB_json(min);
context.Response.Write(retext);
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
context.Response.Charset = "utf-8";
HttpPostedFile file = context.Request.Files["Filedata"];//文件 对象
string name = context.Request["name"];//上传用户
string title = context.Request["title"];//分类
string path = context.Request["path"];//文件路径
if (path == null) { path = ""; }
string fuPaht = "";
string Suser = "";
switch (title)
{
case "公司文件": fuPaht = "E:\\CNGfile\\公司文件" + path; break;
case "车用燃气事业部文档": fuPaht = "E:\\CNGfile\\车用燃气事业部" + path; break;
case "用户个人文档": fuPaht = "E:\\CNGfile\\个人文档\\" + name + "\\" + path; Suser = name; break;
default:
break;
}
DAL dal = new DAL();
if (file != null)
{
if (!Directory.Exists(fuPaht))
{
Directory.CreateDirectory(fuPaht);
}
file.SaveAs(fuPaht + "\\" + file.FileName);
FileItem fi = FileManager.GetItemInfo(fuPaht + "\\" + file.FileName);
if (fi.Name.Length > 0)//判断创建文件是否完成
{
string SQL = "SELECT [ID] FROM [WebApp].[dbo].[FI_公司文件] where 路径='" + fi.FullName + "'";
string ID = dal.RunSqlGetID(SQL);
if (ID != null)
{
dal.RunSql("DELETE FROM [WebApp].[dbo].[FI_公司文件] WHERE ID=" + ID);
}
SQL = "INSERT INTO [WebApp].[dbo].[FI_公司文件]([名称],[路径],[修改日期],[文件大小],[上传用户],[目录],[所属用户],[分类])VALUES(@名称,@路径,@修改日期,@文件大小,@上传用户,@目录,@所属用户,@分类)";
SqlParameter par1 = new SqlParameter("@名称", SqlDbType.VarChar, 200);
par1.Value = fi.Name;
SqlParameter par2 = new SqlParameter("@路径", SqlDbType.VarChar, 800);
par2.Value = fi.FullName;
SqlParameter par3 = new SqlParameter("@修改日期", fi.LastWriteDate);
SqlParameter par4 = new SqlParameter("@文件大小", fi.Size);
SqlParameter par5 = new SqlParameter("@上传用户", name);
SqlParameter par6 = new SqlParameter("@目录", path);
SqlParameter par7 = new SqlParameter("@所属用户", name);
SqlParameter par8 = new SqlParameter("@分类", title);
Sys.DebugMes(par6.Size.ToString());
dal.RunSql(SQL, new SqlParameter[] { par1, par2, par3, par4, par5, par6, par7, par8 });
Sys.addSteps(name, "上传:[" + fi.Name + "]", "文档操作");
//下面这句代码缺少的话，上传成功后上传队列的显示不会自动消失
context.Response.Write("1");
}
}
else
{
context.Response.Write("0");
}
}
/// <summary>
/// 签到时间生成
/// </summary>
/// <param name="dt">结束日期</param>
/// <param name="dd">处理天数</param>
/// <param name="wc">指纹刷机误差小时</param>
/// <param name="cdwc">迟到误差分钟</param>
/// <param name="ztwc">早退误差分钟</param>
public void KQ_签到处理(DateTime dt, int dd, int wc, int cdwc, int ztwc)
{
//dt = DateTime.Parse(dt.ToShortDateString());
DateTime 起始时间 = dt.AddDays(0 - dd);
//签到处理
string SQL = "SELECT [ID],[姓名],[上班时间],[下班时间] FROM [CNGTZ].[dbo].[TZ_人员_考勤表] where 上班签到 IS NULL and 日期 between @起始 and @结束";
SqlParameter par1 = new SqlParameter("起始", 起始时间);
SqlParameter par2 = new SqlParameter("结束", dt);
//Sys.DebugMes(起始时间.ToString() + " " + dt.ToString());
DAL dal = new DAL();
DataTable renName = dal.RunSqlDataTable(SQL, new SqlParameter[] { par1, par2 });//取得人员列表
foreach (DataRow row in renName.Rows)
{
DateTime t = DateTime.Parse(row["上班时间"].ToString());
SqlParameter p1 = new SqlParameter("起始", t.AddHours(0 - wc));
SqlParameter p2 = new SqlParameter("结束", t.AddHours(wc));
SqlParameter p3 = new SqlParameter("姓名", row["姓名"].ToString());
if (row["姓名"].ToString() == "陈兴华")
{
Sys.DebugMes("XXX");
}
SQL = "SELECT top 1 [刷机时间] FROM [CNGTZ].[dbo].[TZ_人员_考勤_刷机时间] where 姓名=@姓名 AND 刷机时间 BETWEEN @起始 AND @结束 ORDER BY 刷机时间";
string qdsj = dal.RunSqlGetID(SQL, new SqlParameter[] { p1, p2, p3 });//找到最早指纹签到时间
if (qdsj == null)
{
continue;//如果没有找到签到时间 就到下一条记录
}
int cd = 0;//判断是否迟到
DateTime tt = DateTime.Parse(qdsj);//签到时间
TimeSpan temp = tt.Subtract(t);
cd = temp.Minutes;//迟到分钟
cd = (cd > cdwc) ? 1 : 0;
SQL = "UPDATE [CNGTZ].[dbo].[TZ_人员_考勤表] SET [上班签到] = @签到时间,[实到] =[实到]+ 1,[迟到]= " + cd.ToString() + " WHERE ID =@ID";
SqlParameter p4 = new SqlParameter("ID", int.Parse(row["ID"].ToString()));
SqlParameter p5 = new SqlParameter("签到时间", DateTime.Parse(qdsj));
dal.RunSql(SQL, new SqlParameter[] { p4, p5 });//更新签到时间列表
}
//进行签退处理
SQL = "SELECT [ID],[姓名],[上班时间],[下班时间] FROM [CNGTZ].[dbo].[TZ_人员_考勤表] where 下班签退 IS NULL and 日期 between @起始 and @结束";
par1 = new SqlParameter("起始", 起始时间);
par2 = new SqlParameter("结束", dt);
renName = dal.RunSqlDataTable(SQL, new SqlParameter[] { par1, par2 });//取得人员列表
foreach (DataRow row in renName.Rows)
{
DateTime t = DateTime.Parse(row["下班时间"].ToString());
SqlParameter p1 = new SqlParameter("起始", t.AddHours(0 - wc));
SqlParameter p2 = new SqlParameter("结束", t.AddHours(wc));
SqlParameter p3 = new SqlParameter("姓名", row["姓名"].ToString());
SQL = "SELECT top 1 [刷机时间] FROM [CNGTZ].[dbo].[TZ_人员_考勤_刷机时间] where 姓名=@姓名 AND 刷机时间 BETWEEN @起始 AND @结束 ORDER BY 刷机时间 desc";
string qdsj = dal.RunSqlGetID(SQL, new SqlParameter[] { p1, p2, p3 });//找到最后下班指纹签到时间
if (qdsj == null)
{
continue;//如果没有找到签到时间 就到下一条记录
}
int zt = 0;//判断是否早退
DateTime tt = DateTime.Parse(qdsj);
TimeSpan temp = t.Subtract(tt);
zt = temp.Minutes;
zt = (zt > ztwc) ? 1 : 0;
SQL = "UPDATE [CNGTZ].[dbo].[TZ_人员_考勤表] SET [下班签退] = @签到时间,[实到] =[实到]+ 1,[早退]= " + zt.ToString() + " WHERE ID =@ID";
SqlParameter p4 = new SqlParameter("ID", int.Parse(row["ID"].ToString()));
SqlParameter p5 = new SqlParameter("签到时间", DateTime.Parse(qdsj));
dal.RunSql(SQL, new SqlParameter[] { p4, p5 });//更新签到时间列表
}
//记录处理时间
SQL = "INSERT INTO [CNGTZ].[dbo].[TZ_人员_考勤处理时间]([时间]) VALUES ('" + DateTime.Now.ToString() + "')";
dal.RunSql(SQL);
if (this.SJ_指纹处理 != null)
{
Even_指纹系统处理 e = new Even_指纹系统处理();
e.mesg = "处理考勤签到【" + 起始时间.ToShortDateString() + " 至 " + dt.ToShortDateString() + "】到完成！";
//触发事件
this.SJ_指纹处理(this, e);
}
}
