<%@ WebHandler Language = "C#" Class="MESCommonBase" %> using System; using System.Web; using Newtonsoft.Json; using System.Text; using System.Linq; using System.Collections.Generic; using System.IO; using System.Web.Script.Serialization; using System.Data; //using BizDataAccess; using System.Data.SqlClient;//uploadInvoiceScan.ashx using DataLinkMesWork; using NPOI.HSSF.UserModel; using NPOI.SS.Formula.Functions; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; //using DbCallData; using BasicData; //using LitJson; using DataLinkMesWork; using System.Text.RegularExpressions; public class MESCommonBase : IHttpHandler { public bool IsReusable { get { return false; } } public void ProcessRequest(HttpContext context) { context.Response.ContentType = "application/json"; var data = context.Request; var stream = new StreamReader(data.InputStream).ReadToEnd(); string InvoiceNum; //JsonData jsonData; string responseText = "NULL"; byte[] bytes = null; string fileName = "test.xsl"; try { JsonData jsonData=null; try { jsonData = JsonMapper.ToObject(stream); } catch(Exception err) { } int type=0; try { if(jsonData!=null) { if(jsonData.ContainsKey("Type")) { type = Int32.Parse( jsonData["Type"].ToString()); }else if(jsonData.ContainsKey("type")) { type = Int32.Parse( jsonData["type"].ToString()); } } } catch { } jsonobj dataobj=null; try { dataobj = new JavaScriptSerializer().Deserialize(stream); if(dataobj!=null) { type = Int32.Parse(dataobj.Type); } } catch(Exception err) { dataobj = null; } if(dataobj==null) { try { InvoiceNum = HttpContext.Current.Request["param"]; if(InvoiceNum!=null) { jsonData = JsonMapper.ToObject(InvoiceNum); if(jsonData.ContainsKey("Type")) { type = Int32.Parse( jsonData["Type"].ToString()); }else if(jsonData.ContainsKey("type")) { type = Int32.Parse( jsonData["type"].ToString()); } } } catch { jsonData = null; } } string fileExtension=""; HttpFileCollection files; string suffix; string name; switch(type) { case 3001: try { //responseText = DataLinkMesWork.DbCallType1003_SqlCmd.SqlExec(dataobj.Param, jsonData); } catch (Exception err){} context.Response.Write(responseText); break; case 2001: //DataLink.LogJsonData(jsonData); MESDownloadExcel.ExcelWebCall.ExcelFile(jsonData,out bytes,out fileName,ref fileExtension); fileName = fileName + "."+fileExtension; HttpContext.Current.Response.ContentType = "application/octet-stream"; //HttpContext.Current.Response.ContentType = getContentType(extension); //通知浏览器下载文件而不是打开 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)); HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition"); HttpContext.Current.Response.BinaryWrite(bytes); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); break; case 2201: string Sn = jsonData["name"].ToString(); if (Sn.Length < 1) return; //DataLink.LogJsonData(jsonData); fileName = Sn + ".xls"; Excel_SY_OutPut2201(Sn, out bytes); HttpContext.Current.Response.ContentType = "application/octet-stream"; //HttpContext.Current.Response.ContentType = getContentType(extension); //通知浏览器下载文件而不是打开 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)); HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition"); HttpContext.Current.Response.BinaryWrite(bytes); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); break; case 2002: //DataLink.LogJsonData(jsonData); MESDownloadExcel.ExcelWebCall.ExcelFilePdf(jsonData,out bytes,out fileName,out fileExtension); fileName = fileName + "."+fileExtension; HttpContext.Current.Response.ContentType = "application/octet-stream"; //HttpContext.Current.Response.ContentType = getContentType(extension); //通知浏览器下载文件而不是打开 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)); HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition"); HttpContext.Current.Response.BinaryWrite(bytes); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); break; case 2003:// 合成excel图片，下载 string[] dataimg; if(context.Request.Form.Keys.Count>0) { dataimg = data.Form.GetValues(0); MESDownloadExcel.ExcelWebCall.ExcelFile(jsonData,out bytes,out fileName,ref dataimg[0]); if (bytes == null) return; fileName = fileName + "."+dataimg[0]; HttpContext.Current.Response.ContentType = "application/octet-stream"; //HttpContext.Current.Response.ContentType = getContentType(extension); //通知浏览器下载文件而不是打开 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)); HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition"); HttpContext.Current.Response.BinaryWrite(bytes); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.Close(); //HttpContext.Current.Response.End(); //if(DataLinkMesWork.DataLink.isBakup=="1") //{ // DataLinkMesWork.DataLink.ExePROCEDURE_Type16_Bakup(jsonData, out bytes, out fileName, out suffix); //} } break; case 2004: //DataLink.LogJsonData(jsonData); DataLinkMesWork.DataLink.ExePROCEDURE_Type2004(jsonData, out bytes, out fileName, out fileExtension); //MESDownloadExcel.ExcelWebCall.ExcelFile(jsonData,out bytes,out fileName,ref fileExtension); fileName = fileName + "."+fileExtension; HttpContext.Current.Response.ContentType = "application/octet-stream"; //HttpContext.Current.Response.ContentType = getContentType(extension); //通知浏览器下载文件而不是打开 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)); HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition"); HttpContext.Current.Response.BinaryWrite(bytes); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); break; case 15://上传文件 files = context.Request.Files; if (files.Count > 0) { fileName = files[0].FileName; bytes = new byte[files[0].InputStream.Length]; files[0].InputStream.Read(bytes, 0, bytes.Length); suffix = fileName.Substring(fileName.LastIndexOf(".")+1); name = fileName.Substring(0,fileName.LastIndexOf(".")); responseText = DataLinkMesWork.DataLink.ExePROCEDURE_Type15(jsonData, name, suffix, bytes); context.Response.Write(responseText); //if(DataLinkMesWork.DataLink.isBakup=="1") //{ // DataLinkMesWork.DataLink.ExePROCEDURE_Type15_Bakup(jsonData, name, suffix, bytes); //} } else { fileName = ""; bytes = new byte[1]; suffix = ""; name = ""; responseText = DataLinkMesWork.DataLink.ExePROCEDURE_Type15(jsonData, name, suffix, bytes); context.Response.Write(responseText); //if(DataLinkMesWork.DataLink.isBakup=="1") //{ // DataLinkMesWork.DataLink.ExePROCEDURE_Type15_Bakup(jsonData, name, suffix, bytes); //} } break; case 16://上传文件 DataLink.LogJsonData(jsonData); DataLinkMesWork.DataLink.ExePROCEDURE_Type16(jsonData, out bytes, out fileName, out suffix); if (bytes == null) return; fileName = fileName + "."+suffix; HttpContext.Current.Response.ContentType = "application/octet-stream"; //HttpContext.Current.Response.ContentType = getContentType(extension); //通知浏览器下载文件而不是打开 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)); HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition"); HttpContext.Current.Response.BinaryWrite(bytes); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); //if(DataLinkMesWork.DataLink.isBakup=="1") //{ // DataLinkMesWork.DataLink.ExePROCEDURE_Type16_Bakup(jsonData, out bytes, out fileName, out suffix); //} break; case 4000: string userIP; HttpRequest Request = HttpContext.Current.Request; // 如果使用代理，获取真实IP if (context.Request.ServerVariables["HTTP_X_FORWARDED_FOR"] != "") userIP = context.Request.ServerVariables["REMOTE_ADDR"]; else userIP = context.Request.ServerVariables["HTTP_X_FORWARDED_FOR"]; if (userIP == null || userIP == "") userIP = context.Request.UserHostAddress; context.Response.Write(userIP); break; default: DataLink.LogJsonData(jsonData); responseText = DataLinkMesWork.DataLink.SqlWebCall(type,jsonData,dataobj); context.Response.Write(responseText); //if(DataLinkMesWork.DataLink.isBakup=="1") //{ // DataLinkMesWork.DataLink.SqlWebCall_Bakup(type,jsonData,dataobj); //} break; } } catch(Exception err) { context.Response.Write(responseText); } } static public void Excel_SY_OutPut2201(string Sn, out byte[] bytes) { DataTable dt1 = new DataTable(); SqlParameter[] thisParms1 = new SqlParameter[1]; thisParms1[0] = new SqlParameter("总成号", Sn); string proName1 = "质量数据报表_表头数据查询"; DataLink.ExecuteStoredProcedure(proName1, ref thisParms1, out dt1); DataTable dt2 = new DataTable(); SqlParameter[] thisParms2 = new SqlParameter[1]; thisParms2[0] = new SqlParameter("总成号", Sn); string proName2 = "质量数据报表_质量数据"; DataLink.ExecuteStoredProcedure(proName2, ref thisParms2, out dt2); DataTable dt3 = new DataTable(); SqlParameter[] thisParms3 = new SqlParameter[1]; thisParms3[0] = new SqlParameter("总成号", Sn); string proName3 = "质量数据报表_过站数据"; DataLink.ExecuteStoredProcedure(proName3, ref thisParms3, out dt3); DataTable dt4 = new DataTable(); var res = Func( dt1, dt2, dt3, dt4,out bytes); //WriteExcel1001(ref wk, ds, firstBodyRow, ishasbottom); //OutExcelFileMemoryStream(wk, out bytesOut); } static bool Func( DataTable dt1, DataTable dt2, DataTable dt3, DataTable dt4,out byte[] bytes) { bytes = null; try { string filePath = AppDomain.CurrentDomain.BaseDirectory+"\\TemplateMES.xls"; //Server.MapPath(“~”) \\ Server.MapPath("/WebSite1") // IWorkbook wk = null;// --GetHSSFWorkbook(bytes, fileExtension); //var filePath = "TemplateMES.xls"; // File.Copy(filePath, outPath, true); using (FileStream fs = File.OpenRead(filePath)) { IWorkbook workbook = null; // 2007+ if (filePath.IndexOf(".xlsx") > 0) { workbook = new XSSFWorkbook(fs); } // 2003- else if (filePath.IndexOf(".xls") > 0) { workbook = new HSSFWorkbook(fs); } ISheet sheet = workbook.GetSheetAt(0); // 从后往前写，避免增加行的问题 sheet.GetRow(34).GetCell(0).SetCellValue("制单人：" + dt1.Rows[0]["制单人"].ToString()); sheet.GetRow(34).GetCell(3).SetCellValue("版本号：" + dt1.Rows[0]["版本号"].ToString()); sheet.GetRow(34).GetCell(7).SetCellValue("保存期限：" + dt1.Rows[0]["保存期限"].ToString()); sheet.GetRow(35).GetCell(0).SetCellValue("制单日期：" + dt1.Rows[0]["制单日期"].ToString()); sheet.GetRow(35).GetCell(3).SetCellValue("表单编号：" + dt1.Rows[0]["表单编号"].ToString()); for (int i = 0; i < dt3.Rows.Count; i++) { sheet.CopyRow(28, 28 + i + 1); } try { for (int i = 0; i < dt3.Rows.Count; i++) { sheet.GetRow(28 + i).GetCell(0).SetCellValue(i+1); sheet.GetRow(28 + i).GetCell(1).SetCellValue(dt3.Rows[i]["工位号"].ToString()); sheet.GetRow(28 + i).GetCell(2).SetCellValue(dt3.Rows[i]["工位名称"].ToString()); sheet.GetRow(28 + i).GetCell(3).SetCellValue(dt3.Rows[i]["在线时间"].ToString()); sheet.GetRow(28 + i).GetCell(4).SetCellValue(dt3.Rows[i]["到达时间"].ToString()); sheet.GetRow(28 + i).GetCell(6).SetCellValue(dt3.Rows[i]["离开时间"].ToString()); sheet.GetRow(28 + i).GetCell(9).SetCellValue(dt3.Rows[i]["是否合格"].ToString()); sheet.GetRow(28 + i).GetCell(10).SetCellValue(dt3.Rows[i]["操作者工号"].ToString()); sheet.GetRow(28 + i).GetCell(11).SetCellValue(dt3.Rows[i]["说明"].ToString()); } } catch { } try { sheet.GetRow(17).GetCell(0).SetCellValue("检测时间：" + dt1.Rows[0]["检测时间"].ToString()); sheet.GetRow(18).GetCell(0).SetCellValue("机型代码：" + dt1.Rows[0]["机型代码"].ToString()); sheet.GetRow(18).GetCell(3).SetCellValue("1挡速比：" + dt1.Rows[0]["1挡速比"].ToString()); sheet.GetRow(18).GetCell(5).SetCellValue("1挡自学习：" + dt1.Rows[0]["1挡自学习"].ToString()); sheet.GetRow(18).GetCell(7).SetCellValue("1挡NVH：" + dt1.Rows[0]["1挡NVH"].ToString()); sheet.GetRow(18).GetCell(9).SetCellValue("倒挡开关：" + dt1.Rows[0]["倒挡开关"].ToString()); sheet.GetRow(19).GetCell(0).SetCellValue("检测结果：" + dt1.Rows[0]["检测结果"].ToString()); sheet.GetRow(19).GetCell(3).SetCellValue("2挡速比：" + dt1.Rows[0]["2挡速比"].ToString()); sheet.GetRow(19).GetCell(5).SetCellValue("2挡自学习：" + dt1.Rows[0]["2挡自学习"].ToString()); sheet.GetRow(19).GetCell(7).SetCellValue("2挡NVH：" + dt1.Rows[0]["2挡NVH"].ToString()); sheet.GetRow(19).GetCell(9).SetCellValue("空挡开关：" + dt1.Rows[0]["空挡开关"].ToString()); sheet.GetRow(20).GetCell(3).SetCellValue("3挡速比：" + dt1.Rows[0]["3挡速比"].ToString()); sheet.GetRow(20).GetCell(5).SetCellValue("3挡自学习：" + dt1.Rows[0]["3挡自学习"].ToString()); sheet.GetRow(20).GetCell(7).SetCellValue("3挡NVH：" + dt1.Rows[0]["3挡NVH"].ToString()); sheet.GetRow(20).GetCell(9).SetCellValue("取力器PTO：" + dt1.Rows[0]["取力器PTO"].ToString()); sheet.GetRow(21).GetCell(3).SetCellValue("4挡速比：" + dt1.Rows[0]["4挡速比"].ToString()); sheet.GetRow(21).GetCell(5).SetCellValue("4挡自学习：" + dt1.Rows[0]["4挡自学习"].ToString()); sheet.GetRow(21).GetCell(7).SetCellValue("4挡NVH：" + dt1.Rows[0]["4挡NVH"].ToString()); sheet.GetRow(21).GetCell(9).SetCellValue("里程表：" + dt1.Rows[0]["里程表"].ToString()); sheet.GetRow(22).GetCell(3).SetCellValue("5挡速比：" + dt1.Rows[0]["5挡速比"].ToString()); sheet.GetRow(22).GetCell(5).SetCellValue("5挡自学习：" + dt1.Rows[0]["5挡自学习"].ToString()); sheet.GetRow(22).GetCell(7).SetCellValue("5挡NVH：" + dt1.Rows[0]["5挡NVH"].ToString()); sheet.GetRow(22).GetCell(9).SetCellValue("换挡平顺性：" + dt1.Rows[0]["换挡平顺性"].ToString()); sheet.GetRow(23).GetCell(3).SetCellValue("6挡速比：" + dt1.Rows[0]["6挡速比"].ToString()); sheet.GetRow(23).GetCell(5).SetCellValue("6挡自学习：" + dt1.Rows[0]["6挡自学习"].ToString()); sheet.GetRow(23).GetCell(7).SetCellValue("6挡NVH：" + dt1.Rows[0]["6挡NVH"].ToString()); sheet.GetRow(24).GetCell(3).SetCellValue("R挡速比：" + dt1.Rows[0]["R挡速比"].ToString()); sheet.GetRow(24).GetCell(5).SetCellValue("R挡自学习：" + dt1.Rows[0]["R挡自学习"].ToString()); sheet.GetRow(24).GetCell(7).SetCellValue("R挡NVH：" + dt1.Rows[0]["R挡NVH"].ToString()); } catch { } for (int i = 0; i < dt2.Rows.Count; i++) { sheet.CopyRow(10, 10 + i + 1); } try { for (int i = 0; i < dt2.Rows.Count; i++) { sheet.GetRow(10 + i).GetCell(0).SetCellValue(i+1); sheet.GetRow(10 + i).GetCell(1).SetCellValue(dt2.Rows[i]["测量工位号"].ToString()); sheet.GetRow(10 + i).GetCell(2).SetCellValue(dt2.Rows[i]["测量项目描述"].ToString()); sheet.GetRow(10 + i).GetCell(3).SetCellValue(dt2.Rows[i]["测量单位"].ToString()); sheet.GetRow(10 + i).GetCell(4).SetCellValue(dt2.Rows[i]["检验类型"].ToString()); sheet.GetRow(10 + i).GetCell(5).SetCellValue(dt2.Rows[i]["实测值"].ToString()); sheet.GetRow(10 + i).GetCell(6).SetCellValue(dt2.Rows[i]["标准值"].ToString()); sheet.GetRow(10 + i).GetCell(7).SetCellValue(dt2.Rows[i]["上限值"].ToString()); sheet.GetRow(10 + i).GetCell(8).SetCellValue(dt2.Rows[i]["下限值"].ToString()); sheet.GetRow(10 + i).GetCell(9).SetCellValue(dt2.Rows[i]["检验结果"].ToString()); sheet.GetRow(10 + i).GetCell(10).SetCellValue(dt2.Rows[i]["员工工号"].ToString()); sheet.GetRow(10 + i).GetCell(11).SetCellValue(dt2.Rows[i]["说明"].ToString()); } } catch { } try { sheet.GetRow(4).GetCell(0).SetCellValue("订单号：" + dt1.Rows[0]["订单号"].ToString()); sheet.GetRow(4).GetCell(2).SetCellValue("SN号：" + dt1.Rows[0]["SN号"].ToString()); sheet.GetRow(5).GetCell(0).SetCellValue("零件号：" + dt1.Rows[0]["零件号"].ToString()); sheet.GetRow(5).GetCell(2).SetCellValue("福田零件号：" + dt1.Rows[0]["福田零件号"].ToString()); sheet.GetRow(5).GetCell(5).SetCellValue("产品型号：" + dt1.Rows[0]["产品型号"].ToString()); sheet.GetRow(6).GetCell(0).SetCellValue("序列号：" + dt1.Rows[0]["序列号"].ToString()); sheet.GetRow(6).GetCell(2).SetCellValue("生产日期：" + dt1.Rows[0]["生产日期"].ToString()); sheet.GetRow(7).GetCell(0).SetCellValue("检验日期：" + dt1.Rows[0]["检验日期"].ToString()); } catch { } MemoryStream ms = new MemoryStream(); workbook.Write(ms); bytes = ms.ToArray(); //using (FileStream localFs = File.OpenWrite(outPath)) //{ // workbook.Write(localFs); // localFs.Close(); //} } return true; } catch (Exception err) { return false; } } public class SqlCmd { //public Insert Insert; //public Select Select; //public Select_check Select_check; //public Select_pages Select_pages; //public Select_pages_check Select_pages_check; //public Delete Delete; //public Update Update; static public string SqlExec(string sqlCmd) { var cmd = JsonConvert.DeserializeObject(sqlCmd); string result = "{ }"; switch (cmd.cmd) { case Cmd.Insert: result = Insert(sqlCmd); break; case Cmd.Select: result = Select(sqlCmd); break; case Cmd.Select_check: result = Select_check(sqlCmd); break; case Cmd.Select_pages: result = Select_pages(sqlCmd); break; case Cmd.Select_pages_check: result = Select_pages_check(sqlCmd); break; case Cmd.Delete: result = Delete(sqlCmd); break; case Cmd.Update: result = Update(sqlCmd); break; } return result; } static private string Select(string sqlCmd) { string result = "{ }"; string sql = SelectSql(sqlCmd); DataTable dt; SqlCall.ExecuteDataTable(sql, out dt); result = JsonConvert.SerializeObject(dt); return result; } static private string Select_check(string sqlCmd) { string result = "{ }"; string sql = SelectSql_check(sqlCmd); DataTable dt; SqlCall.ExecuteDataTable(sql, out dt); result = JsonConvert.SerializeObject(dt); return result; } static private string Select_pages(string sqlCmd) { string tbname; string fieldkey; string where; string fieldshow; string fieldorder; DataTable dt = new DataTable(); string result = "{ }"; var item = JsonConvert.DeserializeObject(sqlCmd); int pagecurrent = item.PageCurrent; int pagesize = item.PageSize; int pagecount; int itemcount; where = GetWhereString(item.Where, item.WhereTime); where = where.Replace("WHERE", ""); fieldorder = GetOrderPage(item.fieldOrder); fieldshow = Get_fieldshow(item.fieldshow); fieldkey = item.fieldkey; tbname = item.tbname; SqlCall.ExecPageQuery(tbname, fieldkey, where, fieldshow, fieldorder, pagecurrent, pagesize, out pagecount, out itemcount, out dt); result = JsonConvert.SerializeObject(dt); string resultOutput = "[{ \"ItemCount\":\"" + itemcount + "\",\"PageCount\":\"" + pagecount + "\"}]"; string jsonStr = "{\"result\":" + result + "," + "\"output\":" + resultOutput + "}"; result = "{\"result\":" + result + "," + "\"output\":" + resultOutput + "}"; return result; } static private string Select_pages_check(string sqlCmd) { string tbname; string fieldkey; string where; string fieldshow; string fieldorder; int pagecurrent = 1; int pagesize = 15; int pagecount; int itemcount; DataTable dt = new DataTable(); string result = "{ }"; var item = JsonConvert.DeserializeObject(sqlCmd); where = GetWhereString_check(item.Where, item.WhereTime); where = where.Replace("WHERE", ""); fieldorder = GetOrderPage(item.fieldOrder); fieldshow = Get_fieldshow(item.fieldshow); fieldkey = "'" + item.fieldkey + "'"; tbname = item.tbname; SqlCall.ExecPageQuery(tbname, fieldkey, where, fieldshow, fieldorder, pagecurrent, pagesize, out pagecount, out itemcount, out dt); result = JsonConvert.SerializeObject(dt); string resultOutput = "[{ \"ItemCount\":\"" + itemcount + "\",\"PageCount\":\"" + pagecount + "\"}]"; string jsonStr = "{\"result\":" + result + "," + "\"output\":" + resultOutput + "}"; result = "{\"result\":" + result + "," + "\"output\":" + resultOutput + "}"; return result; } static private string Insert(string sqlCmd) { string result = "[{ \"result\":\"0\"}]"; string sql; sql = InsertSql(sqlCmd); string errorMessage; SqlCall.ExecuteNonQuery(sql, out errorMessage); if (errorMessage == "") { result = "[{ \"result\":\"1\"}]"; } return result; } static private string Delete(string sqlCmd) { string result = "[{ \"result\":\"0\"}]"; string sql; sql = DeleteSql(sqlCmd); string errorMessage; SqlCall.ExecuteNonQuery(sql, out errorMessage); if (errorMessage == "") { result = "[{ \"result\":\"1\"}]"; } return result; } static private string Update(string sqlCmd) { string result = "[{ \"result\":\"0\"}]"; string sql; sql = UpdateSql(sqlCmd); if(sql.Length<1) return result; string errorMessage; SqlCall.ExecuteNonQuery(sql, out errorMessage); if (errorMessage == "") { result = "[{ \"result\":\"1\"}]"; } return result; } static private string InsertSql(string sqlCmd) { var itemSql = JsonConvert.DeserializeObject(sqlCmd); string sql = ""; string field=""; string value=""; foreach(var item in itemSql.fieldshow) { field = field + item + ","; } field = field.Remove(field.LastIndexOf(","), 1); field = "(" + field + ")"; foreach (var item in itemSql.values) { if(item is string) { if (cheackValueSpecialFormat(item.ToString())) { value = value + " "+item + " , "; } else { value = value + " '"+item + "' , "; } } else { value = value + item + ","; } } value = value.Remove(value.LastIndexOf(","), 1); value = "(" + value + ")"; sql = " INSERT INTO " + itemSql.tbname + field + " VALUES " + value; return sql; } static private string DeleteSql(string sqlCmd) { var item = JsonConvert.DeserializeObject(sqlCmd); string where; where = GetWhereString(item.Where, item.WhereTime); if (where.Length < 1) return ""; string sql; sql = "DELETE FROM " + item.tbname; if (where.Length > 1) { sql = sql + " where " + where; } return sql; } static private string UpdateSql(string sqlCmd) { var itemSql = JsonConvert.DeserializeObject(sqlCmd); string field = ""; if(itemSql.fieldshow.Count<1) return ""; if(itemSql.fieldshow.Count!= itemSql.values.Count) return ""; for (int i=0;i< itemSql.fieldshow.Count;i++) { if(itemSql.values[i] is string) { if (cheackValueSpecialFormat(itemSql.values[i].ToString())) { field = field + itemSql.fieldshow[i] + " = " + itemSql.values[i]+" , "; } else { field = field + itemSql.fieldshow[i] + " = '" + itemSql.values[i]+"' , "; } } else { field = field + itemSql.fieldshow[i] + " = " + itemSql.values[i]+" , "; } } field = field.Remove(field.LastIndexOf(","), 1); string where; where = GetWhereString(itemSql.Where, itemSql.WhereTime); if (where.Length < 1) return ""; string sql; sql = "UPDATE " + itemSql.tbname + " SET " + field; if (where.Length > 1) { sql = sql+ " where " + where; } return sql; } static private bool cheackValueSpecialFormat(string fieldValue) { bool isSpecialFormat = false; fieldValue = fieldValue.ToLower(); if (fieldValue.Contains("getdate")) { isSpecialFormat = true; } if (fieldValue.Contains("convert(")) { isSpecialFormat = true; } if (fieldValue.Contains("left(")) { isSpecialFormat = true; } if (fieldValue.Contains("rank()")) { isSpecialFormat = true; } if (fieldValue.Contains("case ")) { isSpecialFormat = true; } if (fieldValue.Contains("row_number ")) { isSpecialFormat = true; } return isSpecialFormat; } static private string SelectSql(string sqlCmd) { var item = JsonConvert.DeserializeObject(sqlCmd); string where; string orderby; where = GetWhereString(item.where, item.WhereTime); string field=""; field = Get_fieldshow( item.fieldshow); string sql; sql = "SELECT " + field + " FROM " + item.tbname; if(where.Length>1) { sql = sql + " WHERE " + where; if(item.fieldOrder.Count>0) { orderby = GetOrder(item.fieldOrder); sql = sql + orderby; } } return sql; } static private string Get_fieldshow(List fieldshow) { string field=""; foreach(var item in fieldshow) { field = field + item + ", "; } field = field.Remove(field.LastIndexOf(","), 1); return field; } static private string SelectSql_check(string sqlCmd) { var item = JsonConvert.DeserializeObject(sqlCmd); string where; string orderby; string field=""; field = Get_fieldshow( item.fieldshow); where = GetWhereString_check(item.where_check, item.WhereTime); string sql; sql = "SELECT " + field + " FROM " + item.tbname; if (where.Length > 1) { sql = sql + where; if (item.fieldOrder.Count > 0) { orderby = GetOrder(item.fieldOrder); sql = sql + orderby; } } return sql; } static string GetOrder(List item) { string order = ""; foreach(var s in item) { order = order + s+","; } order = order.Remove(order.LastIndexOf(","), 1); order = " ORDER BY " + order; return order; } static string GetOrderPage(List item) { string order = ""; foreach (var s in item) { order = order + s + ","; } order = order.Remove(order.LastIndexOf(","), 1); return order; } static string GetWhereString(List Where, List WhereTime) { string where = " 1 = 1 "; string whereTime = ""; if(Where.Count > 0) { where = where + GetWhere(Where); if(WhereTime.Count > 0) { whereTime = GetWhereTime(WhereTime); where = where + " AND " + whereTime; } } else { if (WhereTime.Count > 0) { where = GetWhereTime(WhereTime); } } return where; } static string GetWhere(List Where) { string where=""; for (int i=0; i WhereTime) { string where = ""; for (int i = 0; i < WhereTime.Count; i++) { string item; item = GetWhereTimeItem(WhereTime[i]); if (i == 0) { where = where + item; } else { where = where + " AND " + item; } } return where; } static string GetOrder(List Where) { string where = ""; for (int i = 0; i < Where.Count; i++) { string item; item = GetWhereItem(Where[i]); if (i == 0) { where = " WHERE " + item; } else { where = where + " AND " + item; } } return where; } static string GetWhereString_check(List Where, List WhereTime) { string where = ""; string whereTime = ""; int itemCount=0; for (int i = 0; i < Where.Count; i++) { string item; if (Where[i].IsCheck) { item = GetWhereItem(Where[i]); if (itemCount == 0) { where = " WHERE " + item; } else { where = where + " AND " + item; } itemCount++; } } if(itemCount>0) { if(WhereTime.Count > 0) { //whereTime = GetWhereTime(WhereTime); where = where + " AND " + whereTime; } } else { if (WhereTime.Count > 0) { //whereTime = GetWhereTime(WhereTime); where = " WHERE " + whereTime; } } return where; } static private string GetWhereItem(Where_check item) { string where; if (item.value is string) { where = " (" + item.fieldname + "='" + item.value + "') "; } else { where = " (" + item.fieldname + "=" + item.value + ") "; } return where; } static private string GetWhereTimeItem(WhereTime item) { string where = " ("+item.fieldname + ">='" + item.start + "' AND " + item.fieldname + "<='" + item.end + "') "; return where; } static private string GetWhereItem(Where item) { string where; if (item.value is string) { if(item.logic==null) { where = "(" + item.fieldname + "='" + item.value + "')"; } else { where = "(" + item.fieldname + item.logic + "'" +item.value + "')"; } } else { if(item.logic==null) { where = "(" + item.fieldname + "=" + item.value + ")"; } else { where = "(" + item.fieldname + item.logic + item.value + ")"; } } return where; } } public class SqlCall { public static bool ExecuteDataTable(string sql, out DataTable dt) { dt = new DataTable(); try { DataSet ds = new DataSet(); bool r = DataLink.ExecuteDataset(sql, out ds); dt = ds.Tables[0]; return r; } catch { return false; } } public static bool ExecuteNonQuery(string sql, out string errorMessage) { return DataLink.ExecuteNonQuery(sql, out errorMessage); } public static bool ExecPageQuery(string tbname, string fieldkey, string where, string fieldshow, string fieldorder, int pagecurrent, int pagesize, out int pagecount, out int itemcount, out DataTable dt) { dt = new DataTable(); SqlParameter[] thisParms = new SqlParameter[9]; thisParms[0] = new SqlParameter("@tbname", tbname); thisParms[0].Direction = ParameterDirection.Input; thisParms[1] = new SqlParameter("@fieldkey", fieldkey); thisParms[1].Direction = ParameterDirection.Input; thisParms[2] = new SqlParameter("@where", where); thisParms[2].Direction = ParameterDirection.Input; thisParms[3] = new SqlParameter("@fieldshow", fieldshow); thisParms[3].Direction = ParameterDirection.Input; thisParms[4] = new SqlParameter("@fieldorder", fieldorder); thisParms[4].Direction = ParameterDirection.Input; thisParms[5] = new SqlParameter("@pagecurrent", pagecurrent); thisParms[5].Direction = ParameterDirection.Input; thisParms[6] = new SqlParameter("@pagesize", pagesize); thisParms[6].Direction = ParameterDirection.Input; thisParms[7] = new SqlParameter("@pagecount", SqlDbType.Int, 32); thisParms[7].Direction = ParameterDirection.Output; thisParms[8] = new SqlParameter("@itemcount", SqlDbType.Int, 32); thisParms[8].Direction = ParameterDirection.Output; pagecount = 0; itemcount = 0; string procedureName = "sp_xt_pagesplit1"; if (DataLink.ExecuteStoredProcedure(procedureName, ref thisParms, out dt)) { pagecount = Convert.ToInt32(thisParms[7].Value.ToString()); itemcount = Convert.ToInt32(thisParms[8].Value.ToString()); return true; } return false; } } public class InsertType { public string type; public Insert value; } public class SelectType { public string type; public Select value; } public class Select_checkType { public string type; public Select_check value; } public class Select_pagesType { public string type; public Select_pages value; } public class Select_pages_checkType { public string type; public Select_pages_check value; } public class DeleteType { public string type; public Delete value; } public class UpdateType { public string type; public Update value; } public class Cmd { public string cmd; public const string Insert = "Insert"; public const string Select = "Select"; public const string Select_check = "Select_check"; public const string Select_pages = "Select_pages"; public const string Select_pages_check = "Select_pages_check"; public const string Delete = "Delete"; public const string Update = "Update"; } public class Insert { public string cmd; public string tbname; public List fieldshow = new List(); public List