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();
}
}
}
