public object Get(JObject req) { using (var db = new LUOLAI1401Context()) { db.Database.Connection.Open(); try { var cmd = db.Database.Connection.CreateCommand(); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "proc_SearchList"; cmd.Parameters.Add(new SqlParameter("@tableName", "wq_goodsreport GD LEFT JOIN wq_termPop PO ON GD.DealerCode=PO.PopCode")); cmd.Parameters.Add(new SqlParameter("@fieldNames", "PO.PopName AS PopName,PO.Address AS Address,GD.RPdtCode AS RPdtCode,GD.PrmDate AS PrmDate,GD.Remark AS Remark,GD.No AS No")); cmd.Parameters.Add(new SqlParameter("@fieldSort", "GD.PrmDate")); var sbCondition = new StringBuilder(); sbCondition.Append(string.Format("{0}{1}{2}{3}{4}{5}{6}", "((GD.UserCode IN (SELECT UserCode FROM dbo.sys_user WHERE OrganizeName LIKE '", System.Web.HttpContext.Current.Session["OrganizeName"], "%') AND GD.CompCode='", (System.Web.HttpContext.Current.Session["sys_user"] as sys_user).CompCode, "') or ('", (System.Web.HttpContext.Current.Session["sys_user"] as sys_user).UserCode, "'='super ' ))")); sbCondition.Append(" AND "); SerachCondition.TextBox(sbCondition, "PopName", "PopName", "varchar"); SerachCondition.TextBox(sbCondition, "Address", "Address", "varchar"); SerachCondition.TextBox(sbCondition, "RPdtCode", "RPdtCode", "varchar"); SerachCondition.Date(sbCondition, "PrmDate", "PrmDate", "varchar"); if (sbCondition.Length > 5) { sbCondition.Length -= 5; cmd.Parameters.Add(new SqlParameter("@condition", sbCondition.ToString())); } if (HttpContext.Current.Request.Get("_report_") == "1") { // 报表请求条件合成 return(sbCondition.ToString()); } SqlParameter rowTotalParameter = null; int rowTotal = 0; int.TryParse(HttpContext.Current.Request.Get("total"), out rowTotal); var rq = new PageRequest(); if (rq.Page.HasValue) { var pageSize = rq.Rows.HasValue ? rq.Rows.Value : 20; var rowStart = (rq.Page.Value - 1) * pageSize + 1; var rowEnd = rq.Page.Value * pageSize; cmd.Parameters.Add(new SqlParameter("@rowStart", rowStart)); cmd.Parameters.Add(new SqlParameter("@rowEnd", rowEnd)); if (rowStart == 1) { cmd.Parameters.Add(rowTotalParameter = new SqlParameter("@rowTotal", System.Data.SqlDbType.Int, 8, System.Data.ParameterDirection.Output, true, 0, 0, null, System.Data.DataRowVersion.Default, 0)); } } string jsonArrayString = null; using (var reader = cmd.ExecuteReader()) { jsonArrayString = reader.ToJsonArrayString(); } if (rowTotalParameter != null) { rowTotal = (int)rowTotalParameter.Value; } return(JObject.Parse("{rows:" + jsonArrayString + ", total:" + rowTotal + "}")); } finally { db.Database.Connection.Close(); } } }
public object Get(JObject req) { using (var db = new LUOLAI1401Context()) { db.Database.Connection.Open(); try { var cmd = db.Database.Connection.CreateCommand(); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "proc_SearchList"; cmd.Parameters.Add(new SqlParameter("@tableName", "Distribution INNER JOIN attendTrack_v")); cmd.Parameters.Add(new SqlParameter("@fieldNames", "Distribution.UserCode AS UserCode,Distribution.UserName AS UserName,Distribution.CreateDate AS CreateDate,attendTrack_v.TckLong AS TckLong,attendTrack_v.TckLat AS TckLat,attendTrack_v.Address AS Address")); cmd.Parameters.Add(new SqlParameter("@fieldSort", "Distribution.UserCode")); var sbCondition = new StringBuilder(); SerachCondition.TextBox(sbCondition, "UserCode", "UserCode", ""); SerachCondition.TextBox(sbCondition, "UserName", "UserName", ""); SerachCondition.Date(sbCondition, "CreateDate", "CreateDate", ""); if (sbCondition.Length > 5) { sbCondition.Length -= 5; cmd.Parameters.Add(new SqlParameter("@condition", sbCondition.ToString())); } SqlParameter rowTotalParameter = null; int rowTotal = 0; int.TryParse(HttpContext.Current.Request.Get("total"), out rowTotal); var rq = new PageRequest(); if (rq.Page.HasValue) { var pageSize = rq.Rows.HasValue ? rq.Rows.Value : 20; var rowStart = (rq.Page.Value - 1) * pageSize + 1; var rowEnd = rq.Page.Value * pageSize; cmd.Parameters.Add(new SqlParameter("@rowStart", rowStart)); cmd.Parameters.Add(new SqlParameter("@rowEnd", rowEnd)); if (rowStart == 1) { cmd.Parameters.Add(rowTotalParameter = new SqlParameter("@rowTotal", System.Data.SqlDbType.Int, 8, System.Data.ParameterDirection.Output, true, 0, 0, null, System.Data.DataRowVersion.Default, 0)); } } string jsonArrayString = null; using (var reader = cmd.ExecuteReader()) { jsonArrayString = reader.ToJsonArrayString(); } if (rowTotalParameter != null) { rowTotal = (int)rowTotalParameter.Value; } return(JObject.Parse("{rows:" + jsonArrayString + ", total:" + rowTotal + "}")); } finally { db.Database.Connection.Close(); } } }
public object Get() { var sbCondition = new System.Text.StringBuilder(); SerachCondition.Date(sbCondition, "caldate", "a.caldate", ""); if (sbCondition.Length > 4) { sbCondition.Length -= 4; } var pageReq = new PageRequest(); using (var db = new LUOLAI1401Context()) { return(pageReq.ToPageList <fw_calendarinfoListModel>(db.Database, "a.calcode ,a.caldate ,a.starttime ,a.endtime ", "fw_calendarinfo a ", sbCondition.ToString(), "a.calcode", "desc")); } }
public object Get() { var sbCondition = new System.Text.StringBuilder(); SerachCondition.Date(sbCondition, "BeginDate", "a.BeginDate", ""); SerachCondition.Date(sbCondition, "EndDate", "a.EndDate", ""); if (sbCondition.Length > 4) { sbCondition.Length -= 4; } var pageReq = new PageRequest(); using (var db = new LUOLAI1401Context()) { return(pageReq.ToPageList <PM_ContractFilesListModel>(db.Database, "a.FID ,a.PManager ,a.type ,a.PID ,a.Amt ,a.Owner ,a.ProjName ,a.BeginDate ,a.EndDate ,a.projAddress ", "PM_ContractFiles a ", sbCondition.ToString(), "a.FID", "desc")); } }
public object Get() { var sbCondition = new System.Text.StringBuilder(); SerachCondition.TextBox(sbCondition, "RPdtName", "a.RPdtName", "varchar"); SerachCondition.Date(sbCondition, "CreateDate", "a.CreateDate", "datetime"); if (sbCondition.Length > 4) { sbCondition.Length -= 4; } var pageReq = new PageRequest(); using (var db = new LUOLAI1401Context()) { return(pageReq.ToPageList <wq_baseRptProductListModel>(db.Database, "a.RPdtCode ,a.RPdtName ,a.Description ,a.CreatePerson ,a.CreateDate ,a.UpdatePerson ,a.UpdateDate ", "wq_baseRptProduct a ", sbCondition.ToString(), "RPdtCode", "desc")); } }
public object Get() { var sbCondition = new System.Text.StringBuilder(); SerachCondition.Date(sbCondition, "BorrowDate", "a.BorrowDate", ""); SerachCondition.Dropdown(sbCondition, "CustID", "a.CustID", ""); SerachCondition.Dropdown(sbCondition, "BorrowName", "a.BorrowName", ""); if (sbCondition.Length > 4) { sbCondition.Length -= 4; } var pageReq = new PageRequest(); using (var db = new LUOLAI1401Context()) { return(pageReq.ToPageList <HR_BorrowsListModel>(db.Database, "a.FID ,a.BorrowDate ,b.Text CustID_RefText ,a.CustID ,c.Name BorrowName_RefText ,a.BorrowName ,a.Used ,a.BorrowPerson ,a.BorrowPhone ,a.ReturePerson ", "HR_Borrows a LEFT JOIN [Bcp.Sysy].dbo.sys_code b ON a.CustID = b.Value AND (b.CodeType='FType') LEFT JOIN v_Certificate c ON a.BorrowName = c.FID ", sbCondition.ToString(), "a.FID", "desc")); } }
public object Get() { var sbCondition = new System.Text.StringBuilder(); sbCondition.AppendFormat("({0}) AND ", string.Format("{0}{1}{2}", "a.CompCode= '", (System.Web.HttpContext.Current.Session["sys_user"] as sys_user).CompCode, "'")); SerachCondition.TextBox(sbCondition, "CateName", "a.CateName", "varchar"); SerachCondition.Date(sbCondition, "CreateDate", "a.CreateDate", "datetime"); if (sbCondition.Length > 4) { sbCondition.Length -= 4; } var pageReq = new PageRequest(); using (var db = new LUOLAI1401Context()) { return(pageReq.ToPageList <base_productCateListModel>(db.Database, "a.CateCode ,a.CateName ,a.DelFlag ,a.CreatePerson ,a.CreateDate ,a.UpdatePerson ,a.UpdateDate ", "base_productCate a ", sbCondition.ToString(), "CateCode", "desc")); } }
public object Get() { var sbCondition = new System.Text.StringBuilder(); sbCondition.AppendFormat("({0}) AND ", string.Format("{0}{1}{2}{3}{4}{5}{6}", "((a.UserCode IN (SELECT UserCode FROM dbo.sys_user WHERE OrganizeName LIKE '", System.Web.HttpContext.Current.Session["OrganizeName"], "%') AND a.CompCode='", (System.Web.HttpContext.Current.Session["sys_user"] as sys_user).CompCode, "') or ('", (System.Web.HttpContext.Current.Session["sys_user"] as sys_user).UserCode, "'='super ' ))")); SerachCondition.Date(sbCondition, "Month", "a.Month", "datetime"); SerachCondition.PopupSelect(sbCondition, "UserCode", "a.UserCode", "varchar"); if (sbCondition.Length > 4) { sbCondition.Length -= 4; } var pageReq = new PageRequest(); using (var db = new LUOLAI1401Context()) { return(pageReq.ToPageList <wq_incomeListModel>(db.Database, "a.id ,b.PopName PopCode_RefText ,a.PopCode ,a.Month ,a.IncomeAmt ,c.name UserCode_RefText ,a.UserCode ,a.Remark ", "wq_income a LEFT JOIN wq_termPop b ON a.PopCode = b.PopCode LEFT JOIN PersonInfo c ON a.UserCode = c.psncode ", sbCondition.ToString(), "id", "desc")); } }
public object Get() { var sbCondition = new System.Text.StringBuilder(); sbCondition.AppendFormat("({0}) AND ", string.Format("{0}{1}{2}{3}{4}{5}{6}", "((a.UserCode IN (SELECT UserCode FROM dbo.sys_user WHERE OrganizeName LIKE '", System.Web.HttpContext.Current.Session["OrganizeName"], "%') AND a.CompCode='", (System.Web.HttpContext.Current.Session["sys_user"] as sys_user).CompCode, "') or ('", (System.Web.HttpContext.Current.Session["sys_user"] as sys_user).UserCode, "'='super ' ))")); SerachCondition.PopupSelect(sbCondition, "UserCode", "a.UserCode", ""); SerachCondition.Date(sbCondition, "AttDateTime", "a.AttDateTime", ""); SerachCondition.Date(sbCondition, "CreateDate", "a.CreateDate", ""); if (sbCondition.Length > 4) { sbCondition.Length -= 4; } var pageReq = new PageRequest(); using (var db = new LUOLAI1401Context()) { return(pageReq.ToPageList <wq_dailyLogListModel>(db.Database, "a.code ,b.name UserCode_RefText ,a.UserCode ,a.AttDateTime ,a.DailySummary ,a.DailyPlan ,a.DailyExper ,a.UpdateDate ,a.UpdatePerson ,a.CreateDate ,a.CreatePerson ", "wq_dailyLog a LEFT JOIN PersonInfo b ON a.UserCode = b.psncode ", sbCondition.ToString(), "code", "desc")); } }
public object Get() { var sbCondition = new System.Text.StringBuilder(); SerachCondition.PopupSelect(sbCondition, "CustID", "a.CustID", ""); SerachCondition.Date(sbCondition, "BeginDate", "a.BeginDate", ""); SerachCondition.Date(sbCondition, "EndDate", "a.EndDate", ""); SerachCondition.Dropdown(sbCondition, "projl", "a.projl", ""); if (sbCondition.Length > 4) { sbCondition.Length -= 4; } var pageReq = new PageRequest(); using (var db = new LUOLAI1401Context()) { return(pageReq.ToPageList <PM_ProjectInfosListModel>(db.Database, "a.FID ,a.appNumber ,a.Code ,a.ProjName ,b.Contact CustID_RefText ,a.CustID ,a.Owner ,a.BeginDate ,a.EndDate ,a.BaDate ,c.Text bClose_RefText ,a.bClose ,a.exDays ,a.proje ,d.empID projl_RefText ,a.projl ", "PM_ProjectInfos a LEFT JOIN BD_Customers b ON a.CustID = b.FID LEFT JOIN [Bcp.Sysy].dbo.sys_code c ON a.bClose = c.Value AND (c.CodeType='yes/no') LEFT JOIN HR_PractiseCerts d ON a.projl = d.FID ", sbCondition.ToString(), "a.FID", "desc")); } }
public object Get() { var sbCondition = new System.Text.StringBuilder(); SerachCondition.TextBox(sbCondition, "directtitle", "a.directtitle", ""); SerachCondition.TextBox(sbCondition, "catid0", "a.catid0", ""); SerachCondition.Date(sbCondition, "directtime0", "a.directtime0", ""); SerachCondition.Dropdown(sbCondition, "isshow", "a.isshow", ""); if (sbCondition.Length > 4) { sbCondition.Length -= 4; } var pageReq = new PageRequest(); using (var db = new LUOLAI1401Context()) { return(pageReq.ToPageList <fw_directpriceListModel>(db.Database, "a.directid ,a.directtitle ,a.catid0 ,a.directtime0 ,a.directprice0 ,b.Text isshow_RefText ,a.isshow ,a.floatprice0 ,a.floatrate0 ", "fw_directprice a LEFT JOIN [SYS_YLW].dbo.sys_code b ON a.isshow = b.Value AND (b.CodeType='YN') ", sbCondition.ToString(), "a.directid", "desc")); } }
public object Get() { var sbCondition = new System.Text.StringBuilder(); sbCondition.AppendFormat("({0}) AND ", string.Format("{0}{1}{2}{3}{4}", "(a.CompCode +'`' +a.UserCode IN (SELECT UserCode+'`'+CompCode FROM dbo.sys_user WHERE OrganizeName LIKE '", System.Web.HttpContext.Current.Session["OrganizeName"], "%')) or ('", (System.Web.HttpContext.Current.Session["sys_user"] as sys_user).UserCode, "'='super')")); SerachCondition.PopupSelect(sbCondition, "UserCode", "a.UserCode", ""); SerachCondition.Date(sbCondition, "PrmDate", "a.PrmDate", ""); SerachCondition.PopupSelect(sbCondition, "DealerCode", "a.DealerCode", ""); SerachCondition.TextBox(sbCondition, "RPdtCode", "a.RPdtCode", ""); if (sbCondition.Length > 4) { sbCondition.Length -= 4; } var pageReq = new PageRequest(); using (var db = new LUOLAI1401Context()) { return(pageReq.ToPageList <wq_goodsreportListModel>(db.Database, "b.name UserCode_RefText ,a.UserCode ,a.PrmDate ,c.PopName DealerCode_RefText ,a.DealerCode ,a.RPdtCode ,a.Remark ", "wq_goodsreport a LEFT JOIN PersonInfo b ON a.UserCode = b.psncode LEFT JOIN wq_termPop c ON a.DealerCode = c.PopCode ", sbCondition.ToString(), "RPdtCode", "desc")); } }
public object Get() { var sbCondition = new System.Text.StringBuilder(); SerachCondition.Dropdown(sbCondition, "Corp", "a.Corp", ""); SerachCondition.Dropdown(sbCondition, "SZType", "a.SZType", ""); SerachCondition.PopupSelect(sbCondition, "CustID", "a.CustID", ""); SerachCondition.Date(sbCondition, "Fdate", "a.Fdate", ""); SerachCondition.Dropdown(sbCondition, "payType", "a.payType", ""); if (sbCondition.Length > 4) { sbCondition.Length -= 4; } var pageReq = new PageRequest(); using (var db = new LUOLAI1401Context()) { return(pageReq.ToPageList <FA_ReceiptsListModel>(db.Database, "a.FID ,b.Text Corp_RefText ,a.Corp ,c.Text SZType_RefText ,a.SZType ,d.Contact CustID_RefText ,a.CustID ,a.Fdate ,a.Amt ,e.Text payType_RefText ,a.payType ,a.Remark ,a.CreatePerson ,a.CreateDate ,a.UpdateDate ", "FA_Receipts a LEFT JOIN [Bcp.Sysy].dbo.sys_code b ON a.Corp = b.Value AND (b.CodeType='HtCorp') LEFT JOIN [Bcp.Sysy].dbo.sys_code c ON a.SZType = c.Value AND (c.CodeType='SHZHType') LEFT JOIN BD_Customers d ON a.CustID = d.FID LEFT JOIN [Bcp.Sysy].dbo.sys_code e ON a.payType = e.Value AND (e.CodeType='PayKind') ", sbCondition.ToString(), "a.FID", "desc")); } }
public object Get() { var sbCondition = new System.Text.StringBuilder(); SerachCondition.Dropdown(sbCondition, "Corp", "a.Corp", ""); SerachCondition.Dropdown(sbCondition, "Type", "a.Type", ""); SerachCondition.Dropdown(sbCondition, "CustID", "a.CustID", ""); SerachCondition.TextBox(sbCondition, "Owner", "a.Owner", ""); SerachCondition.Dropdown(sbCondition, "Region", "a.Region", ""); SerachCondition.Date(sbCondition, "FDate", "a.FDate", ""); SerachCondition.Dropdown(sbCondition, "Scan1", "a.Scan1", ""); if (sbCondition.Length > 4) { sbCondition.Length -= 4; } var pageReq = new PageRequest(); using (var db = new LUOLAI1401Context()) { return(pageReq.ToPageList <PM_ContractsListModel>(db.Database, "a.Code ,b.Text Corp_RefText ,a.Corp ,c.Text Type_RefText ,a.Type ,a.Amt ,d.Contact CustID_RefText ,a.CustID ,a.Owner ,a.Situation ,a.taxAmt ,e.Text Region_RefText ,a.Region ,a.FDate ,a.Remark ,f.Text Scan1_RefText ,a.Scan1 ,a.Scan2,a.ArchStatus ,a.CreatePerson ,a.CreateDate ,a.UpdateDate ,a.affiliatedPM ", "PM_Contracts a LEFT JOIN [Bcp.Sysy].dbo.sys_code b ON a.Corp = b.Value AND (b.CodeType='HtCorp') LEFT JOIN [Bcp.Sysy].dbo.sys_code c ON a.Type = c.Value AND (c.CodeType='HtType') LEFT JOIN BD_Customers d ON a.CustID = d.FID LEFT JOIN [Bcp.Sysy].dbo.sys_code e ON a.Region = e.Value AND (e.CodeType='Region') LEFT JOIN [Bcp.Sysy].dbo.sys_code f ON a.Scan1 = f.Value AND (f.CodeType='GCLB') ", sbCondition.ToString(), "a.Code", "desc")); } }
public object Get() { var sbCondition = new System.Text.StringBuilder(); SerachCondition.TextBox(sbCondition, "empID", "a.empID", ""); SerachCondition.Dropdown(sbCondition, "certType", "a.certType", ""); SerachCondition.TextBox(sbCondition, "certOrgan", "a.certOrgan", ""); SerachCondition.TextBox(sbCondition, "certNo", "a.certNo", ""); SerachCondition.TextBox(sbCondition, "regNo", "a.regNo", ""); SerachCondition.Date(sbCondition, "expireDate", "a.expireDate", ""); SerachCondition.Dropdown(sbCondition, "bUsed", "a.bUsed", ""); SerachCondition.Dropdown(sbCondition, "CustID", "a.CustID", ""); if (sbCondition.Length > 4) { sbCondition.Length -= 4; } var pageReq = new PageRequest(); using (var db = new LUOLAI1401Context()) { return(pageReq.ToPageList <HR_PractiseCertsListModel>(db.Database, "a.FID ,a.certName ,b.Text Corp_RefText ,a.Corp ,a.empID ,c.Text certType_RefText ,a.certType ,a.CodeID ,a.certOrgan ,a.certNo ,a.regNo ,a.expireDate ,a.onProject ,a.START_TIME ,a.END_TIME ,a.GAmt ,d.Text bUsed_RefText ,a.bUsed ,a.trainStatus ,e.Text CustID_RefText ,a.CustID ", "HR_PractiseCerts a LEFT JOIN [Bcp.Sysy].dbo.sys_code b ON a.Corp = b.Value AND (b.CodeType='HtCorp') LEFT JOIN [Bcp.Sysy].dbo.sys_code c ON a.certType = c.Value AND (c.CodeType='zhengshutype') LEFT JOIN [Bcp.Sysy].dbo.sys_code d ON a.bUsed = d.Value AND (d.CodeType='yw') LEFT JOIN [Bcp.Sysy].dbo.sys_code e ON a.CustID = e.Value AND (e.CodeType='yw') ", sbCondition.ToString(), "a.FID", "desc")); } }
public object Get() { var sbCondition = new System.Text.StringBuilder(); SerachCondition.Dropdown(sbCondition, "catid", "a.catid", ""); SerachCondition.TextBox(sbCondition, "proname", "a.proname", ""); SerachCondition.Dropdown(sbCondition, "memid", "a.memid", ""); SerachCondition.Dropdown(sbCondition, "sellmode", "a.sellmode", ""); SerachCondition.Dropdown(sbCondition, "isactive", "a.isactive", ""); SerachCondition.Dropdown(sbCondition, "issale", "a.issale", ""); SerachCondition.Date(sbCondition, "CreateDate", "a.CreateDate", ""); SerachCondition.Dropdown(sbCondition, "ispassed", "a.ispassed", ""); if (sbCondition.Length > 4) { sbCondition.Length -= 4; } var pageReq = new PageRequest(); using (var db = new LUOLAI1401Context()) { return(pageReq.ToPageList <fw_productinfoListModel>(db.Database, "a.Fid ,b.catname catid_RefText ,a.catid ,a.proname ,c.Text spec_RefText ,a.spec ,d.realname memid_RefText ,a.memid ,a.price ,e.Text sellmode_RefText ,a.sellmode ,a.grade ,a.minprodcount ,a.stock ,f.Text isactive_RefText ,a.isactive ,a.depositseller ,a.depositbuyer ,g.Text issale_RefText ,a.issale ,a.viewcount ,a.CreateDate ,h.Text ispassed_RefText ,a.ispassed ", "fw_productinfo a LEFT JOIN fw_categoryinfo b ON a.catid = b.catid AND (b.parentid !='0') LEFT JOIN [SYS_YLW].dbo.sys_code c ON a.spec = c.Value AND (c.CodeType='MeasureUnit') LEFT JOIN fw_memberinfo d ON a.memid = d.memid LEFT JOIN [SYS_YLW].dbo.sys_code e ON a.sellmode = e.Value AND (e.CodeType='TradingMode') LEFT JOIN [SYS_YLW].dbo.sys_code f ON a.isactive = f.Value AND (f.CodeType='YN') LEFT JOIN [SYS_YLW].dbo.sys_code g ON a.issale = g.Value AND (g.CodeType='forseek') LEFT JOIN [SYS_YLW].dbo.sys_code h ON a.ispassed = h.Value AND (h.CodeType='ShState') ", sbCondition.ToString(), "a.Fid", "desc")); } }
public object Get(JObject req) { using (var db = new LUOLAI1401Context()) { db.Database.Connection.Open(); try { var cmd = db.Database.Connection.CreateCommand(); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "proc_SearchList"; cmd.Parameters.Add(new SqlParameter("@tableName", "KZ_xftj")); cmd.Parameters.Add(new SqlParameter("@fieldNames", "KZ_xftj.部门 AS 部门,KZ_xftj.巡店日期 AS 巡店日期,KZ_xftj.所属角色 AS 所属角色,KZ_xftj.员工编号 AS 员工编号,KZ_xftj.员工姓名 AS 员工姓名,KZ_xftj.计划数 AS 计划数,KZ_xftj.计划外数 AS 计划外数,KZ_xftj.进店完成数 AS 进店完成数,KZ_xftj.生动化拍照 AS 生动化拍照,KZ_xftj.活动拍照 AS 活动拍照,KZ_xftj.库存销量 AS 库存销量,KZ_xftj.订单采集 AS 订单采集,KZ_xftj.竞品上报 AS 竞品上报,KZ_xftj.离店完成数 AS 离店完成数")); cmd.Parameters.Add(new SqlParameter("@fieldSort", "巡店日期")); var sbCondition = new StringBuilder(); sbCondition.Append(string.Format("{0}{1}{2}{3}{4}{5}{6}", "((员工编号 IN (SELECT UserCode FROM dbo.sys_user WHERE OrganizeName LIKE '", System.Web.HttpContext.Current.Session["OrganizeName"], "%')AND 所属公司='", (System.Web.HttpContext.Current.Session["sys_user"] as sys_user).CompCode, "') or ('", (System.Web.HttpContext.Current.Session["sys_user"] as sys_user).UserCode, "'='super'))")); sbCondition.Append(" AND "); SerachCondition.Dropdown(sbCondition, "部门", "部门", "varchar"); SerachCondition.Date(sbCondition, "巡店日期", "巡店日期", "varchar"); SerachCondition.TextBox(sbCondition, "所属角色", "所属角色", "varchar"); SerachCondition.TextBox(sbCondition, "员工编号", "员工编号", "varchar"); SerachCondition.TextBox(sbCondition, "员工姓名", "员工姓名", "varchar"); if (sbCondition.Length > 5) { sbCondition.Length -= 5; cmd.Parameters.Add(new SqlParameter("@condition", sbCondition.ToString())); } if (HttpContext.Current.Request.Get("_report_") == "1") { // 报表请求条件合成 return(sbCondition.ToString()); } SqlParameter rowTotalParameter = null; int rowTotal = 0; int.TryParse(HttpContext.Current.Request.Get("total"), out rowTotal); var rq = new PageRequest(); if (rq.Page.HasValue) { var pageSize = rq.Rows.HasValue ? rq.Rows.Value : 20; var rowStart = (rq.Page.Value - 1) * pageSize + 1; var rowEnd = rq.Page.Value * pageSize; cmd.Parameters.Add(new SqlParameter("@rowStart", rowStart)); cmd.Parameters.Add(new SqlParameter("@rowEnd", rowEnd)); if (rowStart == 1) { cmd.Parameters.Add(rowTotalParameter = new SqlParameter("@rowTotal", System.Data.SqlDbType.Int, 8, System.Data.ParameterDirection.Output, true, 0, 0, null, System.Data.DataRowVersion.Default, 0)); } } string jsonArrayString = null; using (var reader = cmd.ExecuteReader()) { jsonArrayString = reader.ToJsonArrayString(); } if (rowTotalParameter != null) { rowTotal = (int)rowTotalParameter.Value; } return(JObject.Parse("{rows:" + jsonArrayString + ", total:" + rowTotal + "}")); } finally { db.Database.Connection.Close(); } } }
public object Get(JObject req) { using (var db = new LUOLAI1401Context()) { db.Database.Connection.Open(); try { var cmd = db.Database.Connection.CreateCommand(); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "proc_SearchList"; cmd.Parameters.Add(new SqlParameter("@tableName", "R_Contract")); cmd.Parameters.Add(new SqlParameter("@fieldNames", "R_Contract.合同编号 AS 合同编号,R_Contract.单位 AS 单位,R_Contract.分部负责人 AS 分部负责人,R_Contract.发包单位 AS 发包单位,R_Contract.工程类别 AS 工程类别,R_Contract.开始日期 AS 开始日期")); cmd.Parameters.Add(new SqlParameter("@fieldSum", "SUM(R_Contract.造价) AS 造价,SUM(R_Contract.印花税) AS 印花税")); cmd.Parameters.Add(new SqlParameter("@fieldSort", "开始日期")); var sbCondition = new StringBuilder(); SerachCondition.Dropdown(sbCondition, "单位", "单位", "varchar"); SerachCondition.Dropdown(sbCondition, "分部负责人", "分部负责人", "varchar"); SerachCondition.Dropdown(sbCondition, "工程类别", "工程类别", "varchar"); SerachCondition.Date(sbCondition, "开始日期", "开始日期", "datetime"); if (sbCondition.Length > 5) { sbCondition.Length -= 5; cmd.Parameters.Add(new SqlParameter("@condition", sbCondition.ToString())); } if (HttpContext.Current.Request.Get("_report_") == "1") { // 报表请求条件合成 return(sbCondition.ToString()); } SqlParameter rowTotalParameter = null; int rowTotal = 0; int.TryParse(HttpContext.Current.Request.Get("total"), out rowTotal); var rq = new PageRequest(); if (rq.Page.HasValue) { var pageSize = rq.Rows.HasValue ? rq.Rows.Value : 20; var rowStart = (rq.Page.Value - 1) * pageSize + 1; var rowEnd = rq.Page.Value * pageSize; cmd.Parameters.Add(new SqlParameter("@rowStart", rowStart)); cmd.Parameters.Add(new SqlParameter("@rowEnd", rowEnd)); if (rowStart == 1) { cmd.Parameters.Add(rowTotalParameter = new SqlParameter("@rowTotal", System.Data.SqlDbType.Int, 8, System.Data.ParameterDirection.Output, true, 0, 0, null, System.Data.DataRowVersion.Default, 0)); } } string jsonArrayString = null; using (var reader = cmd.ExecuteReader()) { jsonArrayString = reader.ToJsonArrayString(); } if (rowTotalParameter != null) { rowTotal = (int)rowTotalParameter.Value; } if (!rq.Page.HasValue || rq.Page.Value > 1) { return(JObject.Parse("{rows:" + jsonArrayString + ", total:" + rowTotal + "}")); } // 合计值请求 cmd = db.Database.Connection.CreateCommand(); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "proc_SearchList"; cmd.Parameters.Add(new SqlParameter("@sumField", 1)); cmd.Parameters.Add(new SqlParameter("@tableName", "R_Contract")); cmd.Parameters.Add(new SqlParameter("@fieldSum", "SUM(R_Contract.造价) AS 造价,SUM(R_Contract.印花税) AS 印花税")); if (sbCondition.Length > 0) { cmd.Parameters.Add(new SqlParameter("@condition", sbCondition.ToString())); } using (var reader = cmd.ExecuteReader()) { return(JObject.Parse("{rows:" + jsonArrayString + ", total:" + rowTotal + ", footer:" + reader.ToJsonArrayString() + "}")); } } finally { db.Database.Connection.Close(); } } }
public object Get(JObject req) { using (var db = new LUOLAI1401Context()) { db.Database.Connection.Open(); try { var cmd = db.Database.Connection.CreateCommand(); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "PicsSerch"; cmd.Parameters.Add(new SqlParameter("@fieldSort", "活动编号")); var sbCondition = new StringBuilder(); sbCondition.Append(string.Format("{0}{1}{2}{3}{4}{5}{6}", "((员工编号 IN (SELECT UserCode FROM dbo.sys_user WHERE OrganizeName LIKE '", System.Web.HttpContext.Current.Session["OrganizeName"], "%')AND 所属公司='", (System.Web.HttpContext.Current.Session["sys_user"] as sys_user).CompCode, "') or ('", (System.Web.HttpContext.Current.Session["sys_user"] as sys_user).UserCode, "'='super'))")); sbCondition.Append(" AND "); SerachCondition.TextBox(sbCondition, "省份", "省份", ""); SerachCondition.TextBox(sbCondition, "城市", "城市", ""); SerachCondition.TextBox(sbCondition, "经销商编号", "经销商编号", ""); SerachCondition.TextBox(sbCondition, "经销商名称", "经销商名称", ""); SerachCondition.TextBox(sbCondition, "活动编号", "活动编号", ""); SerachCondition.TextBox(sbCondition, "活动名称", "活动名称", ""); SerachCondition.Date(sbCondition, "巡店时间", "巡店时间", ""); SerachCondition.Dropdown(sbCondition, "照片类型", "照片类型", ""); SerachCondition.Dropdown(sbCondition, "经销商审核结果", "经销商审核结果", ""); SerachCondition.Date(sbCondition, "总部巡店时间", "总部巡店时间", ""); SerachCondition.Dropdown(sbCondition, "总部照片类型", "总部照片类型", ""); SerachCondition.Dropdown(sbCondition, "审核结果", "审核结果", ""); if (sbCondition.Length > 5) { sbCondition.Length -= 5; cmd.Parameters.Add(new SqlParameter("@condition", sbCondition.ToString())); } if (HttpContext.Current.Request.Get("_report_") == "1") { // 报表请求条件合成 return(sbCondition.ToString()); } SqlParameter rowTotalParameter = null; int rowTotal = 0; int.TryParse(HttpContext.Current.Request.Get("total"), out rowTotal); var rq = new PageRequest(); if (rq.Page.HasValue) { var pageSize = rq.Rows.HasValue ? rq.Rows.Value : 20; var rowStart = (rq.Page.Value - 1) * pageSize + 1; var rowEnd = rq.Page.Value * pageSize; cmd.Parameters.Add(new SqlParameter("@rowStart", rowStart)); cmd.Parameters.Add(new SqlParameter("@rowEnd", rowEnd)); if (rowStart == 1) { cmd.Parameters.Add(rowTotalParameter = new SqlParameter("@rowTotal", System.Data.SqlDbType.Int, 8, System.Data.ParameterDirection.Output, true, 0, 0, null, System.Data.DataRowVersion.Default, 0)); } } string jsonArrayString = null; using (var reader = cmd.ExecuteReader()) { jsonArrayString = reader.ToJsonArrayString(); } if (rowTotalParameter != null) { rowTotal = (int)rowTotalParameter.Value; } return(JObject.Parse("{rows:" + jsonArrayString + ", total:" + rowTotal + "}")); } finally { db.Database.Connection.Close(); } } }
public object Get(JObject req) { using (var db = new LUOLAI1401Context()) { db.Database.Connection.Open(); try { var cmd = db.Database.Connection.CreateCommand(); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "proc_SearchList"; cmd.Parameters.Add(new SqlParameter("@tableName", "FA_Deposit ")); cmd.Parameters.Add(new SqlParameter("@fieldNames", "FA_Deposit .FID AS FID,FA_Deposit .PID AS PID,FA_Deposit .depAmtStatus AS depAmtStatus,FA_Deposit .depAmt AS depAmt,FA_Deposit .CreatorID AS CreatorID,FA_Deposit .CreatedTime AS CreatedTime")); cmd.Parameters.Add(new SqlParameter("@fieldSort", "CreatedTime desc")); var sbCondition = new StringBuilder(); var PIDS = HttpContext.Current.Session["PIDS"].ToString(); if (!string.IsNullOrEmpty(PIDS)) { sbCondition.AppendFormat("{0} like '%{1}%'", "PID", PIDS); sbCondition.Append(" AND "); } SerachCondition.TextBox(sbCondition, "PID", "PID", ""); SerachCondition.Dropdown(sbCondition, "depAmtStatus", "depAmtStatus", ""); SerachCondition.TextBox(sbCondition, "depAmt", "depAmt", ""); SerachCondition.Date(sbCondition, "CreatedTime", "CreatedTime", ""); if (sbCondition.Length > 5) { sbCondition.Length -= 5; cmd.Parameters.Add(new SqlParameter("@condition", sbCondition.ToString())); } if (HttpContext.Current.Request.Get("_report_") == "1") { // 报表请求条件合成 return(sbCondition.ToString()); } SqlParameter rowTotalParameter = null; int rowTotal = 0; int.TryParse(HttpContext.Current.Request.Get("total"), out rowTotal); var rq = new PageRequest(); if (rq.Page.HasValue) { var pageSize = rq.Rows.HasValue ? rq.Rows.Value : 20; var rowStart = (rq.Page.Value - 1) * pageSize + 1; var rowEnd = rq.Page.Value * pageSize; cmd.Parameters.Add(new SqlParameter("@rowStart", rowStart)); cmd.Parameters.Add(new SqlParameter("@rowEnd", rowEnd)); if (rowStart == 1) { cmd.Parameters.Add(rowTotalParameter = new SqlParameter("@rowTotal", System.Data.SqlDbType.Int, 8, System.Data.ParameterDirection.Output, true, 0, 0, null, System.Data.DataRowVersion.Default, 0)); } } string jsonArrayString = null; using (var reader = cmd.ExecuteReader()) { jsonArrayString = reader.ToJsonArrayString(); } if (rowTotalParameter != null) { rowTotal = (int)rowTotalParameter.Value; } return(JObject.Parse("{rows:" + jsonArrayString + ", total:" + rowTotal + "}")); } finally { db.Database.Connection.Close(); } } }
public object Get(JObject req) { using (var db = new LUOLAI1401Context()) { db.Database.Connection.Open(); try { var cmd = db.Database.Connection.CreateCommand(); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "proc_SearchList"; cmd.Parameters.Add(new SqlParameter("@tableName", "HR_HousFundRecords")); cmd.Parameters.Add(new SqlParameter("@fieldNames", "HR_HousFundRecords.FID AS FID,HR_HousFundRecords.FYear AS FYear,HR_HousFundRecords.FMonth AS FMonth,HR_HousFundRecords.HFCode AS HFCode,HR_HousFundRecords.HFType AS HFType,HR_HousFundRecords.empCode AS empCode,HR_HousFundRecords.corpFund AS corpFund,HR_HousFundRecords.indvFund AS indvFund,HR_HousFundRecords.HFAmt AS HFAmt,HR_HousFundRecords.Total AS Total,HR_HousFundRecords.CreatePerson AS CreatePerson,HR_HousFundRecords.Remark AS Remark,HR_HousFundRecords.CreateDate AS CreateDate")); cmd.Parameters.Add(new SqlParameter("@fieldSort", "FMonth,FYear desc")); var sbCondition = new StringBuilder(); SerachCondition.TextBox(sbCondition, "FID", "FID", ""); SerachCondition.TextBox(sbCondition, "FYear", "FYear", ""); SerachCondition.TextBox(sbCondition, "FMonth", "FMonth", ""); SerachCondition.TextBox(sbCondition, "HFCode", "HFCode", ""); SerachCondition.Dropdown(sbCondition, "HFType", "HFType", ""); SerachCondition.PopupSelect(sbCondition, "empCode", "empCode", ""); SerachCondition.Date(sbCondition, "CreateDate", "CreateDate", ""); if (sbCondition.Length > 5) { sbCondition.Length -= 5; cmd.Parameters.Add(new SqlParameter("@condition", sbCondition.ToString())); } if (HttpContext.Current.Request.Get("_report_") == "1") { // 报表请求条件合成 return(sbCondition.ToString()); } SqlParameter rowTotalParameter = null; int rowTotal = 0; int.TryParse(HttpContext.Current.Request.Get("total"), out rowTotal); var rq = new PageRequest(); if (rq.Page.HasValue) { var pageSize = rq.Rows.HasValue ? rq.Rows.Value : 20; var rowStart = (rq.Page.Value - 1) * pageSize + 1; var rowEnd = rq.Page.Value * pageSize; cmd.Parameters.Add(new SqlParameter("@rowStart", rowStart)); cmd.Parameters.Add(new SqlParameter("@rowEnd", rowEnd)); if (rowStart == 1) { cmd.Parameters.Add(rowTotalParameter = new SqlParameter("@rowTotal", System.Data.SqlDbType.Int, 8, System.Data.ParameterDirection.Output, true, 0, 0, null, System.Data.DataRowVersion.Default, 0)); } } string jsonArrayString = null; using (var reader = cmd.ExecuteReader()) { jsonArrayString = reader.ToJsonArrayString(); } if (rowTotalParameter != null) { rowTotal = (int)rowTotalParameter.Value; } return(JObject.Parse("{rows:" + jsonArrayString + ", total:" + rowTotal + "}")); } finally { db.Database.Connection.Close(); } } }
public object Get(JObject req) { using (var db = new LUOLAI1401Context()) { db.Database.Connection.Open(); try { var cmd = db.Database.Connection.CreateCommand(); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "proc_SearchList"; cmd.Parameters.Add(new SqlParameter("@tableName", "wq_dailyLog D INNER JOIN sys_user U ON U.UserCode=D.UserCode and D.CompCode=U.CompCode ")); cmd.Parameters.Add(new SqlParameter("@fieldNames", "D.UserCode AS UserCode,U.UserName AS UserName,U.OrganizeCode AS OrganizeCode,U.bumenName AS bumenName,D.comments AS comments,D.states AS states,D.No AS No,D.CreatePerson AS CreatePerson,D.CreateDate,D.DailyExper AS DailyExper,D.DailyPlan AS DailyPlan,D.DailySummary AS DailySummary")); cmd.Parameters.Add(new SqlParameter("@fieldSort", "D.CreateDate")); var sbCondition = new StringBuilder(); sbCondition.Append(string.Format("{0}{1}{2}{3}{4}{5}{6}", "((D.UserCode IN (SELECT UserCode FROM dbo.sys_user WHERE OrganizeName LIKE '", System.Web.HttpContext.Current.Session["OrganizeName"], "%') AND D.CompCode='", (System.Web.HttpContext.Current.Session["sys_user"] as sys_user).CompCode, "') or ('", (System.Web.HttpContext.Current.Session["sys_user"] as sys_user).UserCode, "'='super ' ))")); sbCondition.Append(" AND "); SerachCondition.PopupSelect(sbCondition, "UserCode", "UserCode", ""); SerachCondition.TextBox(sbCondition, "UserName", "UserName", ""); SerachCondition.PopupSelect(sbCondition, "OrganizeCode", "OrganizeCode", ""); SerachCondition.Dropdown(sbCondition, "states", "states", ""); SerachCondition.Date(sbCondition, "D_CreateDate", "D.CreateDate", ""); if (sbCondition.Length > 5) { sbCondition.Length -= 5; cmd.Parameters.Add(new SqlParameter("@condition", sbCondition.ToString())); } if (HttpContext.Current.Request.Get("_report_") == "1") { // 报表请求条件合成 return(sbCondition.ToString()); } SqlParameter rowTotalParameter = null; int rowTotal = 0; int.TryParse(HttpContext.Current.Request.Get("total"), out rowTotal); var rq = new PageRequest(); if (rq.Page.HasValue) { var pageSize = rq.Rows.HasValue ? rq.Rows.Value : 20; var rowStart = (rq.Page.Value - 1) * pageSize + 1; var rowEnd = rq.Page.Value * pageSize; cmd.Parameters.Add(new SqlParameter("@rowStart", rowStart)); cmd.Parameters.Add(new SqlParameter("@rowEnd", rowEnd)); if (rowStart == 1) { cmd.Parameters.Add(rowTotalParameter = new SqlParameter("@rowTotal", System.Data.SqlDbType.Int, 8, System.Data.ParameterDirection.Output, true, 0, 0, null, System.Data.DataRowVersion.Default, 0)); } } string jsonArrayString = null; using (var reader = cmd.ExecuteReader()) { jsonArrayString = reader.ToJsonArrayString(); } if (rowTotalParameter != null) { rowTotal = (int)rowTotalParameter.Value; } return(JObject.Parse("{rows:" + jsonArrayString + ", total:" + rowTotal + "}")); } finally { db.Database.Connection.Close(); } } }