private OverViewExportInfo GetModelFromDataReader(SqlDataReader dr)
{
OverViewExportInfo model = new OverViewExportInfo();
model.ID = dr["ID"].ToInt32();
model.TaskNo = dr["TaskNo"].ToDBString();
model.VoucherType = dr["VoucherType"].ToInt32();
model.TaskType = dr["TaskType"].ToInt32();
model.SupCusNo = dr["SupcusNo"].ToDBString();
model.SupCusName = dr["SupcusName"].ToDBString();
model.TaskStatus = dr["TaskStatus"].ToInt32();
model.TaskIssued = dr["TaskIssued"].ToDateTimeNull();
model.ReceiveUserNo = dr["ReceiveUserNo"].ToDBString();
model.CreateDateTime = dr["CreateDateTime"].ToDateTimeNull();
model.Remark = dr["Remark"].ToDBString();
model.Reason = dr["Reason"].ToDBString();
model.CreateUserNo = dr["CreateUserNo"].ToDBString();
model.IsShelvePost = dr["IsShelvePost"].ToInt32();
model.DeliveryNo = dr["DeliveryNo"].ToDBString();
model.IsQuality = dr["IsQuality"].ToInt32();
model.IsReceivePost = dr["IsReceivePost"].ToInt32();
model.Plant = dr["Plant"].ToDBString();
model.PlantName = dr["PlantName"].ToDBString();
model.StrVoucherType = dr["StrVoucherType"].ToDBString();
model.StrTaskType = dr["StrTaskType"].ToDBString();
model.StrIsQuality = dr["StrIsQuality"].ToDBString();
model.StrIsShelvePost = dr["StrIsShelvePost"].ToDBString();
model.StrIsReceivePost = dr["StrIsReceivePost"].ToDBString();
model.StrTaskStatus = dr["StrTaskStatus"].ToDBString();
model.WarehouseCode = dr["WarehouseCode"].ToDBString();
model.WarehouseName = dr["WarehouseName"].ToDBString();
model.ReceiveUserName = dr["ReceiveUserName"].ToDBString();
model.CreateUserName = dr["CreateUserName"].ToDBString();
model.PostStatus = dr["PostStatus"].ToInt32();
model.StrPostStatus = dr["StrPostStatus"].ToDBString();
model.MaterialDoc = dr["MaterialDoc"].ToDBString();
model.Task_ID = dr["Task_ID"].ToInt32();
model.ToAreaNo = dr["ToAreaNo"].ToDBString();
model.VoucherNo = dr["VoucherNo"].ToDBString();
model.RowNo = dr["RowNo"].ToDBString();
model.MaterialNo = dr["MaterialNo"].ToDBString();
model.MaterialDesc = dr["MaterialDesc"].ToDBString();
model.Status = dr["Status"].ToInt32();
model.TaskQty = dr["TaskQty"].ToDecimal();
model.QualityQty = dr["QualityQty"].ToDecimal();
model.RemainQty = dr["RemainQty"].ToDecimal();
model.ShelveQty = dr["ShelveQty"].ToDecimal();
model.UnQualityQty = dr["UnQualityQty"].ToDecimal();
model.PostQty = dr["PostQty"].ToDecimal();
model.Unit = dr["Unit"].ToDBString();
model.StrStatus = dr["StrStatus"].ToDBString();
model.IsQualityComp = dr["IsQualityComp"].ToInt32();
model.StrIsQualityComp = dr["StrIsQualityComp"].ToDBString();
model.OperatorUserNo = dr["OperatorUserNo"].ToDBString();
model.OperatorUserName = dr["OperatorUserName"].ToDBString();
model.CompleteDateTime = dr["CompleteDateTime"].ToDateTimeNull();
model.OperatorDateTime = dr["OperatorDateTime"].ToDateTimeNull();
switch (model.VoucherType)
{
case 50:
case 60:
if (string.IsNullOrEmpty(model.CreateUserName))
{
model.ReceiveUserNo = model.CreateUserNo;
model.ReceiveUserName = model.CreateUserName;
}
break;
}
return(model);
}
private string GetFilterSql(OverViewExportInfo model, UserInfo user)
{
try
{
string strSql = "";
bool hadWhere = false;
if (model.TaskType >= 1)
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " TaskType = " + model.TaskType + " ";
hadWhere = true;
}
if (model.VoucherType >= 1)
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " VoucherType = " + model.VoucherType + " ";
hadWhere = true;
}
if (model.TaskStatus >= 1)
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " TaskStatus = " + model.TaskStatus + " ";
hadWhere = true;
}
if (model.PostStatus >= 1)
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " PostStatus = " + model.PostStatus + " ";
hadWhere = true;
}
if (model.IsQuality >= 1)
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " IsQuality = " + model.IsQuality + " ";
hadWhere = true;
}
if (model.IsShelvePost >= 1)
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " IsShelvePost = " + model.IsShelvePost + " ";
hadWhere = true;
}
if (model.IsReceivePost >= 1)
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " IsReceivePost = " + model.IsReceivePost + " ";
hadWhere = true;
}
if (!string.IsNullOrEmpty(model.TaskNo))
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " TaskNo LIKE '%" + model.TaskNo + "%' ";
hadWhere = true;
}
if (!string.IsNullOrEmpty(model.DeliveryNo))
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " (case vouchertype when 50 then taskno when 60 then taskno else deliveryno end) LIKE '%" + model.DeliveryNo + "%' ";
hadWhere = true;
}
if (!string.IsNullOrEmpty(model.MaterialDoc))
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " MaterialDoc LIKE '%" + model.MaterialDoc + "%' ";
hadWhere = true;
}
if (!string.IsNullOrEmpty(model.ReceiveUserNo))
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " (ReceiveUserNo LIKE '%" + model.ReceiveUserNo + "%' OR ReceiveUserName LIKE '%" + model.ReceiveUserNo + "%') ";
hadWhere = true;
}
if (!string.IsNullOrEmpty(model.SupCusNo))
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " (SupCusNo LIKE '%" + model.SupCusNo + "%' OR SupCusName LIKE '%" + model.SupCusNo + "%') ";
hadWhere = true;
}
if (!string.IsNullOrEmpty(model.Plant))
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " (Plant LIKE '%" + model.Plant + "%' OR PlantName LIKE '%" + model.Plant + "%') ";
hadWhere = true;
}
if (model.StartTime != null)
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " CreateDateTime >= " + model.StartTime.ToDateTime().Date.ToSqlTimeString() + " ";
hadWhere = true;
}
if (model.EndTime != null)
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " CreateDateTime <= " + model.EndTime.ToDateTime().AddDays(1).Date.ToSqlTimeString() + " ";
hadWhere = true;
}
if (model.WarehouseID > 0)
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " Task_ID IN (SELECT TASK_ID FROM t_taskwarehouse WHERE WAREHOUSE_ID = " + model.WarehouseID + ") ";
hadWhere = true;
}
return(strSql);
}
catch
{
return(string.Empty);
}
}
public bool GetOverViewExportListByPage(ref List <OverViewExportInfo> modelList, OverViewExportInfo model, ref DividPage page, UserInfo user, ref string strError)
{
if (page == null)
{
page = new DividPage();
}
List <OverViewExportInfo> lstModel = new List <OverViewExportInfo>();
try
{
using (SqlDataReader dr = Common_DB.QueryByDividPage(ref page, "V_OverViewExport", GetFilterSql(model, user), "*", "Order By CreateDateTime Desc"))
{
while (dr.Read())
{
lstModel.Add(GetModelFromDataReader(dr));
}
}
modelList = lstModel;
return(true);
}
catch (Exception ex)
{
strError = ex.Message;
return(false);
}
finally
{
}
}