public static SearchCondition AddDateCondition(this SearchCondition searchCondition, string fieldName, string fieldValue)
{
if (!string.IsNullOrEmpty(fieldValue))
{
string[] itemArray = fieldValue.Split('~');
if (itemArray != null)
{
DateTime value;
bool result = false;
if (itemArray.Length > 0)
{
result = DateTime.TryParse(itemArray[0].Trim(), out value);
if (result)
{
searchCondition.AddCondition(fieldName, value, SqlOperator.MoreThanOrEqual);
}
}
if (itemArray.Length > 1)
{
result = DateTime.TryParse(itemArray[1].Trim(), out value);
if (result)
{
searchCondition.AddCondition(fieldName, value.AddDays(1), SqlOperator.LessThan);
}
}
}
}
return(searchCondition);
}
public ActionResult SubmitRemoveLog(string keepTime)
{
SearchCondition condition = new SearchCondition();
switch (keepTime)
{
case "0": //全部删除
break;
case "3": //保留近一周
condition.AddCondition("F_Date", DateTime.Now.AddDays(-7).ToString("yyyy-MM-dd"), SqlOperator.LessThan);
break;
case "2": //保留近一个月
condition.AddCondition("F_Date", DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd"), SqlOperator.LessThan);
break;
case "1": //保留近三个月
condition.AddCondition("F_Date", DateTime.Now.AddMonths(-3).ToString("yyyy-MM-dd"), SqlOperator.LessThan);
break;
}
baseBLL.DeleteByCondition(GetConditionStr(condition));
return(Success("清空成功。"));
}
/// <summary>
/// 根据查询条件构造查询语句
/// </summary>
private string GetConditionSql()
{
//如果存在高级查询对象信息，则使用高级查询条件，否则使用主表条件查询
SearchCondition condition = advanceCondition;
if (condition == null)
{
condition = new SearchCondition();
condition.AddDateCondition("NG替换时间", this.txtNg替换时间1, this.txtNg替换时间2); //日期类型
condition.AddCondition("物料生产批次号", this.txt物料生产批次号.Text.Trim(), SqlOperator.Like);
condition.AddNumericCondition("设备ID", this.txt设备id1, this.txt设备id2); //数值类型
condition.AddCondition("设备名称", this.txt设备名称.Text.Trim(), SqlOperator.Like);
condition.AddCondition("工位号", this.txt工位号.Text.Trim(), SqlOperator.Like);
condition.AddCondition("物料GUID", this.txt物料guid.Text.Trim(), SqlOperator.Like);
condition.AddCondition("替换前治具GUID", this.txt替换前治具guid.Text.Trim(), SqlOperator.Like);
condition.AddCondition("替换前治具RFID", this.txt替换前治具rfid.Text.Trim(), SqlOperator.Like);
condition.AddNumericCondition("替换前治具孔号", this.txt替换前治具孔号1, this.txt替换前治具孔号2); //数值类型
condition.AddCondition("前治具生产批次号", this.txt前治具生产批次号.Text.Trim(), SqlOperator.Like);
condition.AddCondition("替换后治具GUID", this.txt替换后治具guid.Text.Trim(), SqlOperator.Like);
condition.AddCondition("替换后治具RFID", this.txt替换后治具rfid.Text.Trim(), SqlOperator.Like);
condition.AddNumericCondition("替换后治具孔号", this.txt替换后治具孔号1, this.txt替换后治具孔号2); //数值类型
condition.AddCondition("后治具生产批次号", this.txt后治具生产批次号.Text.Trim(), SqlOperator.Like);
}
string where = condition.BuildConditionSql().Replace("Where", "");
return(where);
}
/// <summary>
/// 根据查询条件构造查询语句
/// </summary>
private string GetConditionSql()
{
//如果存在高级查询对象信息，则使用高级查询条件，否则使用主表条件查询
SearchCondition condition = advanceCondition;
if (condition == null)
{
condition = new SearchCondition();
condition.AddCondition("Number", this.txtNumber.Text.Trim(), SqlOperator.Like);
condition.AddCondition("Name", this.txtName.Text.Trim(), SqlOperator.Like);
condition.AddCondition("IdentityCard", this.txtIdentityCard.Text.Trim(), SqlOperator.Like);
var dep = this.depTree.GetSelectedObject();
if (dep != null)
{
var departments = CallerFactory <IDepartmentService> .Instance.FindWithChildren(dep.Id);
var idList = departments.Select(r => r.Id).ToList();
string ids = string.Join(",", idList);
ids = ids.TransSQLInStrFormat();
condition.AddCondition("DepartmentId", ids, SqlOperator.In);
}
}
string where = condition.BuildConditionSql().Replace("Where", "");
return(where);
}
/// <summary>
/// 根据查询条件构造查询语句
/// </summary>
private string GetConditionSql()
{
//如果存在高级查询对象信息，则使用高级查询条件，否则使用主表条件查询
SearchCondition condition = new SearchCondition();
condition.AddCondition("LoginName", this.txtLoginName.Text.Trim(), SqlOperator.Like);
condition.AddCondition("TableName", this.txtTableName.Text.Trim(), SqlOperator.Like);
condition.AddCondition("OperationType", this.txtOperationType.Text.Trim(), SqlOperator.Like);
condition.AddDateCondition("CreateTime", this.txtCreateTime1, this.txtCreateTime2); //日期类型
//如果是公司管理员，增加公司标识
if (Portal.gc.UserInRole(RoleInfo.CompanyAdminName))
{
condition.AddCondition("Company_ID", Portal.gc.UserInfo.Company_ID, SqlOperator.Equal);
}
string where = condition.BuildConditionSql().Replace("Where", "");
//如果是单击节点得到的条件，则使用树列表的，否则使用查询条件的
if (!string.IsNullOrEmpty(treeConditionSql))
{
where = treeConditionSql;
}
// 增加系统可以访问的公司部门的权限
where += " and (Company_ID " + canOptCompanyID + ")";
return(where);
}
private void BindData()
{
string DisplayColumns = "ID,HandNo,OperationType,Manufacture,WareHouse,Dept,CostCenter,Note,CreateDate,Creator,PickingPeople";
this.winGridView1.DisplayColumns = DisplayColumns;
#region 添加别名解析
this.winGridView1.AddColumnAlias("ID", "编号");
this.winGridView1.AddColumnAlias("HandNo", "货单编号");
this.winGridView1.AddColumnAlias("OperationType", "入库/出库");
this.winGridView1.AddColumnAlias("Manufacture", "供货商");
this.winGridView1.AddColumnAlias("WareHouse", "仓库名称");
this.winGridView1.AddColumnAlias("Dept", "入库部门");
this.winGridView1.AddColumnAlias("CostCenter", "成本中心");
this.winGridView1.AddColumnAlias("Note", "备注信息");
this.winGridView1.AddColumnAlias("CreateDate", "出库日期");
this.winGridView1.AddColumnAlias("Creator", "操作员");
this.winGridView1.AddColumnAlias("PickingPeople", "领料人");
#endregion
SearchCondition condition = new SearchCondition();
condition.AddCondition("h.Manufacture", this.txtSearchManufacture.Text, SqlOperator.Like)
.AddCondition("h.CostCenter", this.txtSearchCostCenter.Text, SqlOperator.Like)
.AddCondition("h.Creator", this.txtSearchOperator.Text, SqlOperator.Like)
.AddCondition("h.WareHouse", this.txtSearchWareHouse.Text.Replace("所有仓库", ""), SqlOperator.Like)
.AddCondition("h.OperationType", "出库", SqlOperator.Equal)
.AddCondition("d.Dept", this.txtSearchDept.Text, SqlOperator.Like)
.AddCondition("d.ItemName", this.txtName.Text, SqlOperator.Like)
.AddCondition("d.ItemNo", this.txtItemNo.Text, SqlOperator.LikeStartAt);
if (this.dtStart.Text.Length > 0)
{
condition.AddCondition("h.CreateDate", this.dtStart.DateTime, SqlOperator.MoreThanOrEqual);
}
if (this.dtEnd.Text.Length > 0)
{
condition.AddCondition("h.CreateDate", this.dtEnd.DateTime, SqlOperator.LessThanOrEqual);
}
string filter = condition.BuildConditionSql();
DataTable dt = BLLFactory <PurchaseHeader> .Instance.GetPurchaseReport(filter);
DataTable dtNew = new DataTable();
foreach (DataColumn col in dt.Columns)
{
dtNew.Columns.Add(col.ColumnName, col.DataType);
}
Dictionary <string, string> dict = new Dictionary <string, string>();
foreach (DataRow row in dt.Rows)
{
if (!dict.ContainsKey(row["ID"].ToString()))
{
dtNew.ImportRow(row);
dict.Add(row["ID"].ToString(), row["ID"].ToString());
}
}
this.winGridView1.DataSource = dtNew.DefaultView;
this.winGridView1.PrintTitle = Portal.gc.gAppUnit + " -- " + "出库单查询报表";
}
private void treeGoods_AfterSelect(object sender, TreeViewEventArgs e)
{
TreeNode node = e.Node;
string filter = " Where 1= 1";
if (node.Tag != null && node.Tag.GetType() == typeof(CustomTreeData))
{
CustomTreeData nodeData = (CustomTreeData)node.Tag;
if (nodeData != null && nodeData.TreeType != CustomTreeType.Unkown)
{
#region MyRegion
SearchCondition condition = new SearchCondition();
if (nodeData.TreeType == CustomTreeType.Ware)
{
condition.AddCondition("t.WareHouse", nodeData.TreeData, SqlOperator.Equal);
}
else if (nodeData.TreeType == CustomTreeType.Dept)
{
//condition.AddCondition("1", "2", SqlOperator.Equal);
condition.AddCondition("d.Dept", nodeData.TreeData, SqlOperator.Equal);
}
else if (nodeData.TreeType == CustomTreeType.UsagePos)
{
condition.AddCondition("d.UsagePos", nodeData.TreeData, SqlOperator.Equal);
}
else if (nodeData.TreeType == CustomTreeType.ItemType)
{
condition.AddCondition("d.ItemType", nodeData.TreeData, SqlOperator.Equal);
}
else if (nodeData.TreeType == CustomTreeType.ItemBigType)
{
condition.AddCondition("d.ItemBigType", nodeData.TreeData, SqlOperator.Equal);
}
filter = condition.BuildConditionSql();
#endregion
}
}
DataTable dt = BLLFactory <Stock> .Instance.GetCurrentStockReport(filter);
this.lvwDetail.Items.Clear();
foreach (DataRow row in dt.Rows)
{
ListViewItem item = new ListViewItem(row["ItemNo"].ToString());
item.SubItems.Add(row["ItemName"].ToString());
item.SubItems.Add(row["ItemBigType"].ToString());
item.SubItems.Add(row["ItemType"].ToString());
item.SubItems.Add(row["Unit"].ToString());
item.SubItems.Add(Convert.ToDecimal(row["Price"]).ToString("C2"));
item.SubItems.Add(Convert.ToInt32(row["StockQuantity"]).ToString());
item.SubItems.Add(Convert.ToDecimal(row["StockAmount"]).ToString("C2"));
item.SubItems.Add(row["UsagePos"].ToString());
item.SubItems.Add(row["WareHouse"].ToString());
item.SubItems.Add(row["Dept"].ToString());
this.lvwDetail.Items.Add(item);
}
}
/// <summary>根据查询条件构造查询语句
///
/// </summary>
/// <returns>查询条件</returns>
private string GetSearchSql()
{
SearchCondition condition = new SearchCondition();
condition.AddCondition("SetKey", this.txtQuerySetKey.Text, SqlOperator.Like);
condition.AddCondition("SetText", this.txtQuerySetText.Text, SqlOperator.Like);
return(condition.BuildConditionSql().Replace("Where", ""));
}
/// <summary>根据查询条件构造查询语句
///
/// </summary>
/// <returns>查询条件</returns>
private string GetSearchSql()
{
SearchCondition condition = new SearchCondition();
condition.AddCondition("TableName", this.txtQueryTableName.Text, SqlOperator.Like);
condition.AddCondition("Remark", this.txtQueryRemark.Text, SqlOperator.Like);
return(condition.BuildConditionSql().Replace("Where", ""));
}
/// <summary>根据查询条件构造查询语句
///
/// </summary>
/// <returns>查询条件</returns>
private string GetSearchSql()
{
SearchCondition condition = new SearchCondition();
condition.AddCondition("DepartmentName", this.txtQueryDepartmentName.Text, SqlOperator.Like);
condition.AddCondition("Sortnum", this.txtQuerySortnum.Text, SqlOperator.Like);
return(condition.BuildConditionSql().Replace("Where", ""));
}
/// <summary>根据查询条件构造查询语句
///
/// </summary>
/// <returns>查询条件</returns>
private string GetSearchSql()
{
SearchCondition condition = new SearchCondition();
condition.AddCondition("RoleName", this.txtQueryRoleName.Text, SqlOperator.Like);
condition.AddCondition("Sortnum", this.txtQuerySortnum.Text, SqlOperator.Like);
condition.AddCondition("Remark", this.cbkQueryRemark.CheckedItemValues.Trim(), SqlOperator.In);
return(condition.BuildConditionSql().Replace("Where", ""));
}
/// <summary>
/// 准备数据内容
/// </summary>
/// <returns></returns>
private DataTable CreateData()
{
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("类型"));
List <int> totalInList = new List <int>();
List <int> totalOutList = new List <int>();
for (int i = 1; i <= 12; i++)
{
dt.Columns.Add(new DataColumn(string.Format("{0}-{1}月", this.txtYear.Text, i), typeof(decimal)));
DateTime dtStart = Convert.ToDateTime(string.Format("{0}-{1}-1", this.txtYear.Text, i));
DateTime dtEnd = dtStart.AddMonths(1);
SearchCondition condition = new SearchCondition();
condition.AddCondition("h.WareHouse", this.txtWareHouse.Text, SqlOperator.Like);//增加h为指定特定的表
condition.AddCondition("CreateDate", dtStart, SqlOperator.MoreThanOrEqual);
condition.AddCondition("CreateDate", dtEnd, SqlOperator.LessThan);
//入库数量
int totalIn = BLLFactory <PurchaseHeader> .Instance.GetPurchaseQuantity(condition.BuildConditionSql().Replace("Where", ""), true);
totalInList.Add(totalIn);
condition = new SearchCondition();
condition.AddCondition("h.WareHouse", this.txtWareHouse.Text, SqlOperator.Like);//增加h为指定特定的表
condition.AddCondition("CreateDate", dtStart, SqlOperator.MoreThanOrEqual);
condition.AddCondition("CreateDate", dtEnd, SqlOperator.LessThan);
//出库数量
int totalOut = BLLFactory <PurchaseHeader> .Instance.GetPurchaseQuantity(condition.BuildConditionSql().Replace("Where", ""), false);
totalOutList.Add(totalOut);
}
DataRow dr = dt.NewRow();
dr[0] = "入库数量";
int j = 1;
foreach (int count in totalInList)
{
dr[j++] = count;
}
dt.Rows.Add(dr);
dr = dt.NewRow();
dr[0] = "出库数量";
j = 1;
foreach (int count in totalOutList)
{
dr[j++] = count;
}
dt.Rows.Add(dr);
return(dt);
}
/// <summary>
/// 根据查询条件构造查询语句
/// </summary>
private string GetConditionSql()
{
//如果存在高级查询对象信息，则使用高级查询条件，否则使用主表条件查询
SearchCondition condition = new SearchCondition();
condition.AddCondition("StrValue", txtSearch.Text.Trim(), SqlOperator.Like, true, "SearchData");
condition.AddCondition("Name", txtSearch.Text.Trim(), SqlOperator.Like, true, "SearchData");
condition.AddCondition("SysValue", txtSearch.Text.Trim(), SqlOperator.Like, true, "SearchData");
return(condition.BuildConditionSql().Replace("Where", ""));
}
/// <summary>根据查询条件构造查询语句
///
/// </summary>
/// <returns>查询条件</returns>
private string GetSearchSql()
{
string strLoadType = cbkID.CheckedItemValues;
SearchCondition condition = new SearchCondition();
condition.AddCondition("UserName", this.txtQueryUserName.Text, SqlOperator.Like);
condition.AddCondition("TrueName", cboQueryTrueName.Text, SqlOperator.Like);
condition.AddCondition("User_Id", strLoadType.Trim(','), SqlOperator.In);
return(condition.BuildConditionSql().Replace("Where", ""));
}
/// <summary>
/// 根据查询条件构造查询语句
/// </summary>
private string GetConditionSql()
{
//如果存在高级查询对象信息，则使用高级查询条件，否则使用主表条件查询
SearchCondition condition = new SearchCondition();
condition.AddCondition("Name", this.txtName.Text.Trim(), SqlOperator.Like);
condition.AddCondition("SysId", ccbbSysIds.GetCheckedComboBoxValue(), SqlOperator.In);
string where = condition.BuildConditionSql().Replace("Where", "");
return(where);
}
protected override ResultModel DoAfterUploadFile(string fullFileName)
{
try
{
ResultModel result = new ResultModel();
result.message = "";
//读取文件 excel
DataTable dt = KGMFramework.WebApp.Library.ExcelHelper.ExcelImport(fullFileName, 0);
if (dt == null || dt.Rows.Count == 0)
{
result.bSuccess = false;
result.message = "获取文件信息失败";
return(result);
}
List <Mst_CustomerInfo> CuStList = new List <Mst_CustomerInfo>();
for (int i = 0; i < dt.Rows.Count; i++)
{
Mst_CustomerInfo info = new Mst_CustomerInfo();
info.F_EnCode = dt.Rows[i]["客户编码"].ToString();
info.F_FullName = dt.Rows[i]["客户名称"].ToString();
info.F_Contacts = dt.Rows[i]["联系人"].ToString();
info.F_TelePhone = dt.Rows[i]["电话"].ToString();
info.F_WeChat = dt.Rows[i]["微信"].ToString();
info.F_Description = dt.Rows[i]["备注"].ToString();
//info.F_Fax = dt.Rows[i]["传真"].ToString();
info.F_Address = dt.Rows[i]["地址"].ToString();
SearchCondition search = new SearchCondition();
search.AddCondition("F_EnCode", dt.Rows[i]["客户编码"].ToString(), SqlOperator.Equal);
search.AddCondition("F_FullName", dt.Rows[i]["客户名称"].ToString(), SqlOperator.Equal);
List <Mst_GoodsInfo> infoList = BLLFactory <Mst_Goods> .Instance.Find(GetConditionStr(search));
if (infoList.Count > 0)
{
info.F_Id = infoList[0].F_Id;
}
CuStList.Add(info);
}
BLLFactory <Mst_Customer> .Instance.InsertRange(CuStList);
result.bSuccess = true;
result.message = "导入成功";
return(result);
}
catch (Exception)
{
ResultModel result = new ResultModel();
result.bSuccess = true;
result.message = "导入失败";
return(result);
}
}
/// <summary>
/// 根据查询条件构造查询语句
/// </summary>
private string GetConditionSql()
{
//如果存在高级查询对象信息，则使用高级查询条件，否则使用主表条件查询
SearchCondition condition = new SearchCondition();
condition.AddCondition("ID", this.txtID.Text.Trim(), SqlOperator.Equal);
condition.AddCondition("FullName", this.txtFullName.Text.Trim(), SqlOperator.Equal);
condition.AddCondition("MobilePhone", this.txtMobilePhone.Text.Trim(), SqlOperator.Equal);
string where = condition.BuildConditionSql().Replace("Where", "");
return(where);
}
/// <summary>
/// 获取页面列表信息
/// </summary>
/// <param name="page">页面Url</param>
/// <param name="gridId">表格Id</param>
/// <returns></returns>
public virtual ActionResult GetGridColumns(string page, string gridId, string sortFiled = " Order By F_SortCode ")
{
SearchCondition condition = new SearchCondition();
condition.AddCondition("F_Page", page, SqlOperator.Equal);
condition.AddCondition("F_GridId", gridId, SqlOperator.Equal);
condition.AddCondition("F_ParentId", 0, SqlOperator.NotEqual);
var data = BLLFactory <Sys_GridColumnSetting> .Instance.Find(GetConditionStr(condition), sortFiled);
return(Content(JsonAppHelper.ToJson(data)));
}
/// <summary>
/// 查询库存数量
/// </summary>
/// <returns></returns>
public ActionResult GetNum()
{
SO_StockMakeBodyInfo bodyInfo = BLLFactory <SO_StockMakeBody> .Instance.FindByID(selectId);
SearchCondition condition = new SearchCondition();
condition.AddCondition("F_GoodsId", bodyInfo.F_GoodsId, SqlOperator.Equal);
condition.AddCondition("F_WarehouseId", bodyInfo.F_WarehouseId, SqlOperator.Equal);
condition.AddCondition("F_CargoPositionId", bodyInfo.F_CargoPositionId, SqlOperator.Equal);
Sys_StockInfo Info = BLLFactory <Sys_Stock> .Instance.Find(GetConditionStr(condition))[0];
return(Content(JsonAppHelper.ToJson(Info)));
}
