protected WhereConditon ListWhereSql(ListCondition condition)
{
WhereConditon wcd = new WhereConditon();
wcd.DBPara = new Dictionary <string, object>();
EntryModel model = JsonHelper.Deserialize <EntryModel>(condition.ListModelString);
if (model == null)
{
return(wcd);
}
if (DataConvert.ToString(model.AssetsMaintainNo) != "")
{
wcd.Sql += @" and AssetsMaintain.assetsMaintainNo like '%'+@assetsMaintainNo+'%'";
wcd.DBPara.Add("assetsMaintainNo", model.AssetsMaintainNo);
}
if (DataConvert.ToString(model.AssetsMaintainName) != "")
{
wcd.Sql += @" and AssetsMaintain.assetsMaintainName like '%'+@assetsMaintainName+'%'";
wcd.DBPara.Add("assetsMaintainName", model.AssetsMaintainName);
}
if (DataConvert.ToString(model.AssetsNo) != "")
{
wcd.Sql += @" and Assets.assetsNo like '%'+@assetsNo+'%'";
wcd.DBPara.Add("assetsNo", model.AssetsNo);
}
if (DataConvert.ToString(model.AssetsName) != "")
{
wcd.Sql += @" and Assets.assetsName like '%'+@assetsName+'%'";
wcd.DBPara.Add("assetsName", model.AssetsName);
}
if (DataConvert.ToString(model.MaintainDate1) != "")
{
wcd.Sql += @" and AssetsMaintainDetail.maintainDate>=@maintainDate1";
wcd.DBPara.Add("maintainDate1", DataConvert.ToString(model.MaintainDate1) + " 00:00:00");
}
if (DataConvert.ToString(model.MaintainDate2) != "")
{
wcd.Sql += @" and AssetsMaintainDetail.maintainDate<=@maintainDate2";
wcd.DBPara.Add("maintainDate2", DataConvert.ToString(model.MaintainDate2) + " 23:59:59");
}
return(wcd);
}
protected override WhereConditon ListWhereSql(ListCondition condition)
{
WhereConditon wcd = new WhereConditon();
wcd.DBPara = new Dictionary <string, object>();
if (AppMember.UsePeopleControlLevel == UsePeopleControlLevel.Low)
{
wcd.Sql += @" and AppUser.isSysUser='******'";
}
if (condition.SysUser.UserNo != "sa" && condition.SysUser.IsHeaderOffice != "Y")
{
wcd.Sql += @" and D.companyId=@companyId";
wcd.DBPara.Add("companyId", condition.SysUser.CompanyId);
}
ListModel model = JsonHelper.Deserialize <ListModel>(condition.ListModelString);
if (model == null)
{
return(wcd);
}
if (DataConvert.ToString(model.UserNo) != "")
{
wcd.Sql += @" and AppUser.userNo like '%'+@userNo+'%'";
wcd.DBPara.Add("userNo", model.UserNo);
}
if (DataConvert.ToString(model.UserName) != "")
{
wcd.Sql += @" and AppUser.userName like '%'+@userName+'%'";
wcd.DBPara.Add("userName", model.UserName);
}
if (DataConvert.ToString(model.GroupId) != "")
{
wcd.Sql += @" and AppUser.groupId like '%'+ @groupId +'%'";
wcd.DBPara.Add("groupId", model.GroupId);
}
if (DataConvert.ToString(model.DepartmentId) != "")
{
wcd.Sql += @" and AppUser.departmentId like '%'+ @departmentId +'%'";
wcd.DBPara.Add("departmentId", model.DepartmentId);
}
return(wcd);
}
protected override WhereConditon ListWhereSql(ListCondition condition)
{
WhereConditon wcd = new WhereConditon();
wcd.DBPara = new Dictionary<string, object>();
ApproveListCondition acondition = condition as ApproveListCondition;
if (DataConvert.ToString(acondition.ListMode) != "")
wcd.DBPara.Add("approver", acondition.Approver);
ListModel model = JsonHelper.Deserialize<ListModel>(acondition.ListModelString);
if (model == null) return wcd;
if (DataConvert.ToString(model.AssetsSplitNo) != "")
{
wcd.Sql += @" and AssetsSplit.assetsSplitNo like '%'+@assetsSplitNo+'%'";
wcd.DBPara.Add("assetsSplitNo", model.AssetsSplitNo);
}
if (DataConvert.ToString(model.AssetsSplitName) != "")
{
wcd.Sql += @" and AssetsSplit.assetsSplitName like '%'+@assetsSplitName+'%'";
wcd.DBPara.Add("assetsSplitName", model.AssetsSplitName);
}
return wcd;
}
protected WhereConditon ListWhereSql(ListCondition condition)
{
WhereConditon wcd = new WhereConditon();
wcd.DBPara = new Dictionary <string, object>();
EntryModel model = JsonHelper.Deserialize <EntryModel>(condition.ListModelString);
if (model == null)
{
return(wcd);
}
if (DataConvert.ToString(model.AssetsNo) != "")
{
wcd.Sql += @" and Assets.assetsNo like '%'+@assetsNo+'%'";
wcd.DBPara.Add("assetsNo", model.AssetsNo);
}
if (DataConvert.ToString(model.AssetsName) != "")
{
wcd.Sql += @" and Assets.assetsName like '%'+@assetsName+'%'";
wcd.DBPara.Add("assetsName", model.AssetsName);
}
if (DataConvert.ToString(model.DepartmentId) != "")
{
wcd.Sql += @" and Assets.departmentId=@departmentId";
wcd.DBPara.Add("departmentId", model.DepartmentId);
}
if (DataConvert.ToString(model.StoreSiteId) != "")
{
wcd.Sql += @" and Assets.storeSiteId=@storeSiteId";
wcd.DBPara.Add("storeSiteId", model.StoreSiteId);
}
if (DataConvert.ToString(model.RemindDays) != "")
{
DateTime time = IdGenerator.GetServerDate();
wcd.Sql += @" and datediff(day,'" + time.ToShortDateString() + "', DATEADD(dd,Assets.guaranteeDays,Assets.purchaseDate))<@remindDays ";
wcd.DBPara.Add("remindDays", model.RemindDays);
}
return(wcd);
}
public virtual DataTable GetEntryGridSource(EntryModel model)
{
WhereConditon wcd = EntryGridWhereSql(model);
string sql = string.Format(@"select Assets.assetsId assetsId,
Assets.assetsNo assetsNo,
Assets.assetsName assetsName,
(select assetsClassName from AssetsClass where Assets.assetsClassId=AssetsClass.assetsClassId) assetsClassId,
(select assetsTypeName from AssetsType where Assets.assetsTypeId=AssetsType.assetsTypeId) assetsTypeId,
(select assetsUsesName from AssetsUses where Assets.assetsUsesId=AssetsUses.assetsUsesId) assetsUsesId,
(select departmentName from AppDepartment C where D.companyId=C.departmentId ) companyId,
Assets.departmentId departmentId,
D.departmentName departmentName,
Assets.storeSiteId storeSiteId,
(select storeSiteName from StoreSite where Assets.storeSiteId=StoreSite.storeSiteId) storeSiteName,
Assets.usePeople usePeople,
isnull((select userName from AppUser where Assets.usePeople=AppUser.userId),Assets.usePeople) usePeopleName,
Assets.keeper keeper,
isnull((select userName from AppUser where Assets.keeper=AppUser.userId),Assets.keeper) keeperName,
Assets.assetsValue assetsValue,
Assets.assetsNetValue assetsNetValue,
Assets.imgDefault imgDefault,
Assets.assetsBarcode assetsBarcode,
Assets.purchaseDate purchaseDate,
(select CodeTable.codeName from CodeTable where Assets.assetsState=CodeTable.codeNo and CodeTable.codeType='AssetsState' and CodeTable.languageVer='{0}' ) assetsState,
(select equityOwnerName from EquityOwner where Assets.equityOwnerId=EquityOwner.equityOwnerId) equityOwnerName,
(select projectManageName from ProjectManage where Assets.projectManageId=ProjectManage.projectManageId) projectManageName,
Assets.spec spec,
Assets.remark remark,
'' ApproveState,
Assets.CreateId CreateId,
Assets.CreateTime CreateTime
from Assets
left join AppDepartment D on Assets.departmentId=D.departmentId
where 1=1 {1} ", AppMember.AppLanguage.ToString(), wcd.Sql);
DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql, wcd.DBPara).Tables[0];
return(dtGrid);
}
protected WhereConditon ListWhereSql(ListCondition condition)
{
WhereConditon wcd = new WhereConditon();
wcd.DBPara = new Dictionary <string, object>();
EntryModel model = JsonHelper.Deserialize <EntryModel>(condition.ListModelString);
if (model == null)
{
return(wcd);
}
if (DataConvert.ToString(model.AssetsTransferNo) != "")
{
wcd.Sql += @" and AssetsTransfer.assetsTransferNo like '%'+@assetsTransferNo+'%'";
wcd.DBPara.Add("assetsTransferNo", model.AssetsTransferNo);
}
if (DataConvert.ToString(model.AssetsTransferName) != "")
{
wcd.Sql += @" and AssetsTransfer.assetsTransferName like '%'+@assetsTransferName+'%'";
wcd.DBPara.Add("assetsTransferName", model.AssetsTransferName);
}
if (DataConvert.ToString(model.AssetsNo) != "")
{
wcd.Sql += @" and Assets.assetsNo like '%'+@assetsNo+'%'";
wcd.DBPara.Add("assetsNo", model.AssetsNo);
}
if (DataConvert.ToString(model.AssetsName) != "")
{
wcd.Sql += @" and Assets.assetsName like '%'+@assetsName+'%'";
wcd.DBPara.Add("assetsName", model.AssetsName);
}
if (DataConvert.ToString(model.TransferDate) != "")
{
wcd.Sql += @" and AssetsTransfer.transferDate like '%'+@transferDate+'%'";
wcd.DBPara.Add("transferDate", model.TransferDate);
}
return(wcd);
}
// public AssetsClassChangeReportDS GetReportSource(string querystring)
// {
// string sql =string.Format( @"select MonthRecord.*,AssetsClass.assetsClassPath,AssetsClass.assetsClassName
// from MonthRecord,AssetsClass
// where MonthRecord.assetsClassId=AssetsClass.assetsClassId {0}
// order by AssetsClass.assetsClassNo ", ListWhereSql(querystring).Sql);
// DataTable dt = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(querystring).DBPara).Tables[0];
// string sqlRpt = string.Format(@"select distinct AssetsClass.assetsClassId, AssetsClass.assetsClassNo,
// AssetsClass.assetsClassName ,AssetsClass.assetsClassPath
// from MonthRecord,AssetsClass
// where MonthRecord.assetsClassId=AssetsClass.assetsClassId {0}
// order by AssetsClass.assetsClassNo ", ListWhereSql(querystring).Sql);
// DataTable dtRpt = AppMember.DbHelper.GetDataSet(sqlRpt, ListWhereSql(querystring).DBPara).Tables[0];
// AssetsClassChangeReportDS ds = new AssetsClassChangeReportDS();
// AssetsClassChangeReportDS.AssetsClassChangeReportDataTable dtHeader = ds.AssetsClassChangeReport;
// foreach (DataRow dr in dtRpt.Rows)
// {
// AssetsClassChangeReportDS.AssetsClassChangeReportRow drHeader = dtHeader.NewAssetsClassChangeReportRow();
// drHeader.assetsClassId = DataConvert.ToString(dr["assetsClassId"]);
// drHeader.assetsClassName = DataConvert.ToString(dr["assetsClassName"]);
// if (DataConvert.ToString(dr["assetsClassPath"]) == ""
// || DataConvert.ToString(dr["assetsClassPath"]) == DataConvert.ToString(dr["assetsClassId"]))
// {
// drHeader.beginPeriodNum = DataConvert.ToInt32(dt.Compute("sum(beginPeriodNum)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
// drHeader.beginPeriodAmt = DataConvert.ToDouble(dt.Compute("sum(beginPeriodAmt)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
// drHeader.currentPeriodAddNum = DataConvert.ToInt32(dt.Compute("sum(currentPeriodAddNum)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
// drHeader.currentPeriodAddAmt = DataConvert.ToDouble(dt.Compute("sum(currentPeriodAddAmt)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
// drHeader.currentPeriodReduceNum = DataConvert.ToInt32(dt.Compute("sum(currentPeriodReduceNum)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
// drHeader.currentPeriodReduceAmt = DataConvert.ToDouble(dt.Compute("sum(currentPeriodReduceAmt)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
// drHeader.endPeriodNum = DataConvert.ToInt32(dt.Compute("sum(endPeriodNum)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
// drHeader.endPeriodAmt = DataConvert.ToDouble(dt.Compute("sum(endPeriodAmt)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
// }
// else
// {
// drHeader.beginPeriodNum = DataConvert.ToInt32(dt.Compute("sum(beginPeriodNum)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
// drHeader.beginPeriodAmt = DataConvert.ToDouble(dt.Compute("sum(beginPeriodAmt)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
// drHeader.currentPeriodAddNum = DataConvert.ToInt32(dt.Compute("sum(currentPeriodAddNum)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
// drHeader.currentPeriodAddAmt = DataConvert.ToDouble(dt.Compute("sum(currentPeriodAddAmt)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
// drHeader.currentPeriodReduceNum = DataConvert.ToInt32(dt.Compute("sum(currentPeriodReduceNum)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
// drHeader.currentPeriodReduceAmt = DataConvert.ToDouble(dt.Compute("sum(currentPeriodReduceAmt)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
// drHeader.endPeriodNum = DataConvert.ToInt32(dt.Compute("sum(endPeriodNum)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
// drHeader.endPeriodAmt = DataConvert.ToDouble(dt.Compute("sum(endPeriodAmt)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
// }
// dtHeader.Rows.Add(drHeader);
// }
// return ds;
// }
protected WhereConditon ListWhereSql(ListCondition condition)
{
WhereConditon wcd = new WhereConditon();
wcd.DBPara = new Dictionary <string, object>();
EntryModel model = JsonHelper.Deserialize <EntryModel>(condition.ListModelString);
if (model == null)
{
return(wcd);
}
if (DataConvert.ToString(model.FiscalYearId) != "")
{
wcd.Sql += @" and MonthRecord.fiscalYearId=@fiscalYearId";
wcd.DBPara.Add("fiscalYearId", model.FiscalYearId);
}
if (DataConvert.ToString(model.FiscalPeriodId) != "")
{
wcd.Sql += @" and MonthRecord.fiscalPeriodId=@fiscalPeriodId";
wcd.DBPara.Add("fiscalPeriodId", model.FiscalPeriodId);
}
if (DataConvert.ToString(model.AssetsClassId) != "")
{
wcd.Sql += @" and AssetsClass.assetsClassId=@assetsClassId";
wcd.DBPara.Add("assetsClassId", model.AssetsClassId);
}
if (DataConvert.ToString(model.DepartmentId) != "")
{
wcd.Sql += @" and MonthRecord.departmentId=@departmentId";
wcd.DBPara.Add("departmentId", model.DepartmentId);
}
if (DataConvert.ToString(model.StoreSiteId) != "")
{
wcd.Sql += @" and MonthRecord.storeSiteId=@storeSiteId";
wcd.DBPara.Add("storeSiteId", model.StoreSiteId);
}
return(wcd);
}
protected WhereConditon ListWhereSql(ListCondition condition)
{
WhereConditon wcd = new WhereConditon();
wcd.DBPara = new Dictionary <string, object>();
EntryModel model = JsonHelper.Deserialize <EntryModel>(condition.ListModelString);
if (model == null)
{
return(wcd);
}
if (DataConvert.ToString(model.UserName) != "")
{
wcd.Sql += @" and AppLog.UserId like '%'+@UserId+'%'";
wcd.DBPara.Add("UserId", model.UserName);
}
if (DataConvert.ToString(model.LogMessage) != "")
{
wcd.Sql += @" and AppLog.Message like '%'+@LogMessage+'%'";
wcd.DBPara.Add("LogMessage", model.LogMessage);
}
if (DataConvert.ToString(model.LogType) != "")
{
wcd.Sql += @" and AppLog.LogLevel=@LogLevel";
wcd.DBPara.Add("LogLevel", model.LogType);
}
if (DataConvert.ToString(model.LogDate1) != "")
{
wcd.Sql += @" and AppLog.LogDate>=@LogDate1";
wcd.DBPara.Add("LogDate1", DataConvert.ToString(model.LogDate1) + " 00:00:00");
}
if (DataConvert.ToString(model.LogDate2) != "")
{
wcd.Sql += @" and AppLog.LogDate<=@LogDate2";
wcd.DBPara.Add("LogDate2", DataConvert.ToString(model.LogDate2) + " 23:59:59");
}
return(wcd);
}
protected override WhereConditon ListWhereSql(ListCondition condition)
{
WhereConditon wcd = new WhereConditon();
wcd.DBPara = new Dictionary <string, object>();
ListModel model = JsonHelper.Deserialize <ListModel>(condition.ListModelString);
if (model == null)
{
return(wcd);
}
if (DataConvert.ToString(model.EquityOwnerNo) != "")
{
wcd.Sql += @" and EquityOwner.equityOwnerNo like '%'+@equityOwnerNo+'%'";
wcd.DBPara.Add("equityOwnerNo", model.EquityOwnerNo);
}
if (DataConvert.ToString(model.EquityOwnerName) != "")
{
wcd.Sql += @" and EquityOwner.equityOwnerName like '%'+@equityOwnerName+'%'";
wcd.DBPara.Add("equityOwnerName", model.EquityOwnerName);
}
return(wcd);
}
protected override WhereConditon ListWhereSql(ListCondition condition)
{
WhereConditon wcd = new WhereConditon();
wcd.DBPara = new Dictionary <string, object>();
ListModel model = JsonHelper.Deserialize <ListModel>(condition.ListModelString);
if (model == null)
{
return(wcd);
}
if (DataConvert.ToString(model.FiscalPeriodName) != "")
{
wcd.Sql += @" and FiscalPeriod.fiscalPeriodName like '%'+@fiscalPeriodName+'%'";
wcd.DBPara.Add("fiscalPeriodName", model.FiscalPeriodName);
}
if (DataConvert.ToString(model.FiscalYearId) != "")
{
wcd.Sql += @" and FiscalPeriod.fiscalYearId = ''+@fiscalYearId+''";
wcd.DBPara.Add("fiscalYearId", model.FiscalYearId);
}
return(wcd);
}
public virtual int GetEntryGridCount(Dictionary <string, object> paras, string formMode, string primaryKey, string formVar)
{
if (formMode == "new" || formMode == "new2")
{
string sql = string.Format(@"select * from Assets where 1=1 ", AppMember.AppLanguage.ToString());
WhereConditon wcd = EntryGridWhereSql(formVar);
DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql + wcd.Sql, wcd.DBPara).Tables[0];
return(dtGrid.Rows.Count);
}
else
{
if (!paras.ContainsKey("AssetsCheckId"))
{
paras.Add("AssetsCheckId", primaryKey);
}
string sql = string.Format(@"select AssetsCheckDetail.* from AssetsCheckDetail
inner join Assets on AssetsCheckDetail.assetsId=Assets.assetsId
left join AppDepartment D on AssetsCheckDetail.departmentId=D.departmentId
where AssetsCheckDetail.AssetsCheckId=@AssetsCheckId ");
DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql, paras).Tables[0];
return(dtGrid.Rows.Count);
}
}
protected WhereConditon EntryGridWhereSql(string formVar)
{
WhereConditon wcd = new WhereConditon();
wcd.DBPara = new Dictionary <string, object>();
if (formVar == null)
{
wcd.Sql += " and 1<>1 ";
}
EntryQueryModel model = JsonHelper.Deserialize <EntryQueryModel>(formVar);
if (model == null)
{
return(wcd);
}
if (DataConvert.ToString(model.AssetsClassId) != "")
{
//wcd.Sql += @" and Assets.assetsClassId=@assetsClassId";
wcd.Sql += @" and exists (select 1 from AssetsClass SS where Assets.assetsClassId=SS.assetsClassId and SS.assetsClassPath like '%'+@assetsClassId+'%' )";
wcd.DBPara.Add("assetsClassId", model.AssetsClassId);
}
if (DataConvert.ToString(model.DepartmentId) != "")
{
//wcd.Sql += @" and Assets.departmentId=@departmentId";
wcd.Sql += @" and exists (select 1 from AppDepartment SS where Assets.departmentId=SS.departmentId and SS.departmentPath like '%'+@departmentId+'%' )";
wcd.DBPara.Add("departmentId", model.DepartmentId);
}
if (DataConvert.ToString(model.StoreSiteId) != "")
{
//wcd.Sql += @" and Assets.storeSiteId=@storeSiteId";
wcd.Sql += @" and exists (select 1 from StoreSite SS where Assets.storeSiteId=SS.storeSiteId and SS.storeSitePath like '%'+@storeSiteId+'%' )";
wcd.DBPara.Add("storeSiteId", model.StoreSiteId);
}
return(wcd);
}
protected WhereConditon ListWhereSql(ListCondition condition)
{
WhereConditon wcd = new WhereConditon();
wcd.DBPara = new Dictionary <string, object>();
EntryModel model = JsonHelper.Deserialize <EntryModel>(condition.ListModelString);
if (model == null)
{
return(wcd);
}
if (DataConvert.ToString(model.AssetsScrapNo) != "")
{
wcd.Sql += @" and AssetsScrap.assetsScrapNo like '%'+@assetsScrapNo+'%'";
wcd.DBPara.Add("assetsScrapNo", model.AssetsScrapNo);
}
if (DataConvert.ToString(model.AssetsScrapName) != "")
{
wcd.Sql += @" and AssetsScrap.assetsScrapName like '%'+@assetsScrapName+'%'";
wcd.DBPara.Add("assetsScrapName", model.AssetsScrapName);
}
if (DataConvert.ToString(model.AssetsNo) != "")
{
wcd.Sql += @" and Assets.assetsNo like '%'+@assetsNo+'%'";
wcd.DBPara.Add("assetsNo", model.AssetsNo);
}
if (DataConvert.ToString(model.AssetsName) != "")
{
wcd.Sql += @" and Assets.assetsName like '%'+@assetsName+'%'";
wcd.DBPara.Add("assetsName", model.AssetsName);
}
if (DataConvert.ToString(model.AssetsTypeId) != "")
{
wcd.Sql += @" and Assets.assetsTypeId like '%'+@assetsTypeId+'%'";
wcd.DBPara.Add("assetsTypeId", model.AssetsTypeId);
}
if (DataConvert.ToString(model.ScrapTypeId) != "")
{
wcd.Sql += @" and AssetsScrapDetail.scrapTypeId like '%'+@scrapTypeId+'%'";
wcd.DBPara.Add("scrapTypeId", model.ScrapTypeId);
}
if (DataConvert.ToString(model.AssetsClassId) != "")
{
wcd.Sql += @" and Assets.assetsClassId like '%'+@assetsClassId+'%'";
wcd.DBPara.Add("assetsClassId", model.AssetsClassId);
}
if (DataConvert.ToString(model.DepartmentId) != "")
{
wcd.Sql += @" and Assets.departmentId like '%'+@departmentId+'%'";
wcd.DBPara.Add("departmentId", model.DepartmentId);
}
if (DataConvert.ToString(model.ScrapDate1) != "")
{
wcd.Sql += @" and AssetsScrapDetail.scrapDate>=@scrapDate1";
wcd.DBPara.Add("scrapDate1", DataConvert.ToString(model.ScrapDate1) + " 00:00:00");
}
if (DataConvert.ToString(model.ScrapDate2) != "")
{
wcd.Sql += @" and AssetsScrapDetail.scrapDate<=@scrapDate2";
wcd.DBPara.Add("scrapDate2", DataConvert.ToString(model.ScrapDate2) + " 23:59:59");
}
return(wcd);
}
public override DataTable GetEntryGridDataTable(Dictionary <string, object> paras, string formMode, string primaryKey, string formVar)
{
int pageIndex = DataConvert.ToInt32(paras["pageIndex"]);
int pageRowNum = DataConvert.ToInt32(paras["pageRowNum"]);
int start = 1 + pageRowNum * (pageIndex - 1);
int end = pageRowNum * pageIndex;
if (formMode == "new" || formMode == "new2")
{
WhereConditon wcd = EntryGridWhereSql(formVar);
string sql = string.Format(@"select Assets.assetsId assetsId,
Assets.assetsNo assetsNo,
Assets.assetsName assetsName,
(select assetsClassName from AssetsClass where Assets.assetsClassId=AssetsClass.assetsClassId) assetsClassId,
(select assetsTypeName from AssetsType where Assets.assetsTypeId=AssetsType.assetsTypeId) assetsTypeId,
(select assetsUsesName from AssetsUses where Assets.assetsUsesId=AssetsUses.assetsUsesId) assetsUsesId,
(select departmentName from AppDepartment C where D.companyId=C.departmentId ) companyId,
Assets.departmentId departmentId,
D.departmentName departmentName,
Assets.storeSiteId storeSiteId,
(select storeSiteName from StoreSite where Assets.storeSiteId=StoreSite.storeSiteId) storeSiteName,
Assets.usePeople usePeople,
isnull((select userName from AppUser where Assets.usePeople=AppUser.userId),Assets.usePeople) usePeopleName,
Assets.keeper keeper,
isnull((select userName from AppUser where Assets.keeper=AppUser.userId),Assets.keeper) keeperName,
Assets.assetsValue assetsValue,
Assets.assetsNetValue assetsNetValue,
Assets.imgDefault imgDefault,
Assets.assetsBarcode assetsBarcode,
convert(nvarchar(100), Assets.purchaseDate,23) purchaseDate,
(select CodeTable.codeName from CodeTable where Assets.assetsState=CodeTable.codeNo and CodeTable.codeType='AssetsState' and CodeTable.languageVer='{0}' ) assetsState,
(select equityOwnerName from EquityOwner where Assets.equityOwnerId=EquityOwner.equityOwnerId) equityOwnerName,
(select projectManageName from ProjectManage where Assets.projectManageId=ProjectManage.projectManageId) projectManageName,
Assets.spec spec,
Assets.remark remark,
'' ApproveState,
Assets.CreateId CreateId,
Assets.CreateTime CreateTime
from ( select * from (select *,Row_Number() OVER ( ORDER BY assetsno ) rnum from Assets where 1=1 {1}) Assets where rnum between {2} and {3} ) Assets
left join AppDepartment D on Assets.departmentId=D.departmentId
where 1=1 ", AppMember.AppLanguage.ToString(), wcd.Sql, start, end);
DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql, wcd.DBPara).Tables[0];
return(dtGrid);
}
else
{
if (!paras.ContainsKey("AssetsCheckId"))
{
paras.Add("AssetsCheckId", primaryKey);
}
string sql = string.Format(@"select Assets.assetsId assetsId,
Assets.assetsNo assetsNo,
Assets.assetsName assetsName,
(select assetsClassName from AssetsClass where Assets.assetsClassId=AssetsClass.assetsClassId) assetsClassId,
(select assetsTypeName from AssetsType where Assets.assetsTypeId=AssetsType.assetsTypeId) assetsTypeId,
(select departmentName from AppDepartment C where D.companyId=C.departmentId ) companyId,
AssetsCheckDetail.departmentId departmentId,
D.departmentName departmentName,
AssetsCheckDetail.storeSiteId storeSiteId,
(select storeSiteName from StoreSite where StoreSite.storeSiteId=AssetsCheckDetail.storeSiteId) storeSiteName,
AssetsCheckDetail.actualDepartmentId actualDepartmentId,
AssetsCheckDetail.actualDepartmentId actualDepartmentName,
(select departmentName from AppDepartment where AppDepartment.departmentId=AssetsCheckDetail.actualDepartmentId) actualDepartmentText,
'' ActualDepartmentBtn,
AssetsCheckDetail.actualStoreSiteId actualStoreSiteId,
AssetsCheckDetail.actualStoreSiteId actualStoreSiteName,
(select storeSiteName from StoreSite where StoreSite.storeSiteId=AssetsCheckDetail.actualStoreSiteId) actualStoreSiteText,
'' ActualStoreSiteBtn ,
Assets.assetsBarcode AssetsBarcode,
convert(nvarchar(100),AssetsCheckDetail.actualCheckDate,23) ActualCheckDate,
AssetsCheckDetail.checkResult CheckResultId,
AssetsCheckDetail.checkResult CheckResultName,
(select CodeTable.codeName from CodeTable where AssetsCheckDetail.checkResult=CodeTable.codeNo and CodeTable.codeType='CheckResult' and CodeTable.languageVer='{0}' ) CheckResultText,
AssetsCheckDetail.isFinished isFinished,
(select CodeTable.codeName from CodeTable where AssetsCheckDetail.isFinished=CodeTable.codeNo and CodeTable.codeType='BoolVal' and CodeTable.languageVer='{0}' ) isFinishedText,
AssetsCheckDetail.approveState ApproveState,
AssetsCheckDetail.createId CreateId,
AssetsCheckDetail.createTime CreateTime
from ( select * from (select *,Row_Number() OVER ( ORDER BY assetsId ) rnum from AssetsCheckDetail where AssetsCheckDetail.AssetsCheckId=@AssetsCheckId ) AssetsCheckDetail where rnum between {1} and {2} ) AssetsCheckDetail
left join Assets on AssetsCheckDetail.assetsId=Assets.assetsId
left join AppDepartment D on AssetsCheckDetail.departmentId=D.departmentId
where 1=1 ", AppMember.AppLanguage.ToString(), start, end);
DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql, paras).Tables[0];
return(dtGrid);
}
}
protected WhereConditon EntryGridWhereSql(string formVar)
{
WhereConditon wcd = new WhereConditon();
wcd.DBPara = new Dictionary <string, object>();
if (formVar == null)
{
wcd.Sql += " and 1<>1 ";
}
Entry2Model model = JsonHelper.Deserialize <Entry2Model>(formVar);
if (model == null)
{
return(wcd);
}
if (DataConvert.ToString(model.AssetsNo) != "")
{
wcd.Sql += @" and Assets.assetsNo like '%'+@assetsNo+'%'";
wcd.DBPara.Add("assetsNo", model.AssetsNo);
}
if (DataConvert.ToString(model.AssetsName) != "")
{
wcd.Sql += @" and Assets.assetsName like '%'+@assetsName+'%'";
wcd.DBPara.Add("assetsName", model.AssetsName);
}
if (DataConvert.ToString(model.AssetsBarcode) != "")
{
wcd.Sql += @" and Assets.assetsBarcode like '%'+@assetsBarcode+'%'";
wcd.DBPara.Add("assetsBarcode", model.AssetsBarcode);
}
if (DataConvert.ToString(model.AssetsClassId) != "")
{
wcd.Sql += @" and Assets.assetsClassId=@assetsClassId";
wcd.DBPara.Add("assetsClassId", model.AssetsClassId);
}
if (DataConvert.ToString(model.DepartmentId) != "")
{
wcd.Sql += @" and Assets.departmentId=@departmentId";
wcd.DBPara.Add("departmentId", model.DepartmentId);
}
if (DataConvert.ToString(model.StoreSiteId) != "")
{
wcd.Sql += @" and Assets.storeSiteId=@storeSiteId";
wcd.DBPara.Add("storeSiteId", model.StoreSiteId);
}
if (DataConvert.ToString(model.AssetsState) != "")
{
wcd.Sql += @" and Assets.assetsState=@assetsState";
wcd.DBPara.Add("assetsState", model.AssetsState);
}
if (DataConvert.ToString(model.UsePeople) != "")
{
wcd.Sql += @" and Assets.usePeople like '%'+@usePeople+'%'";
wcd.DBPara.Add("usePeople", model.UsePeople);
}
if (DataConvert.ToString(model.Keeper) != "")
{
wcd.Sql += @" and Assets.keeper like '%'+@keeper+'%'";
wcd.DBPara.Add("keeper", model.Keeper);
}
if (DataConvert.ToString(model.ProjectManageId) != "")
{
wcd.Sql += @" and Assets.projectManageId=@projectManageId";
wcd.DBPara.Add("projectManageId", model.ProjectManageId);
}
if (DataConvert.ToString(model.PurchaseDateFrom) != "")
{
wcd.Sql += @" and Assets.purchaseDate>=@purchaseDateFrom";
wcd.DBPara.Add("purchaseDateFrom", DataConvert.ToString(model.PurchaseDateFrom) + " 00:00:00");
}
if (DataConvert.ToString(model.PurchaseDateTo) != "")
{
wcd.Sql += @" and Assets.purchaseDate<=@purchaseDateTo";
wcd.DBPara.Add("purchaseDateTo", DataConvert.ToString(model.PurchaseDateTo) + " 23:59:59");
}
if (DataConvert.ToString(model.CompanyId) != "")
{
wcd.Sql += @" and D.companyId=@companyId";
wcd.DBPara.Add("companyId", model.CompanyId);
}
return(wcd);
}