public GetSettlementByConditionResponse ExportSettlementBySettlementNumber(SettlementSearchCondition wc)
{
string Mess = string.Empty;
GetSettlementByConditionResponse response = new GetSettlementByConditionResponse();
try
{
//保存结算主子表
DataTable dt = new DataTable();
#region 查询条件
string sb = string.Empty;
if (!string.IsNullOrEmpty(wc.SettlementNumber))
{
sb += " and SettlementNumber='" + wc.SettlementNumber.Trim() + "' ";
}
#endregion
DbParam[] dbParams = new DbParam[] {
new DbParam("@Where", DbType.String, sb, ParameterDirection.Input)
};
DataSet ds = this.ExecuteDataSet("Proc_WMS_ExportSettlementBySettlementNumber", dbParams);
response.SettlementDetailCollection = ds.Tables[0].ConvertToEntityCollection <SettlementDetail>();
return(response);
}
catch (Exception)
{
return(null);
}
}
public GetSettlementByConditionResponse GetHiltiList(WMS_HiltibjSettled wc, DateTime?StartSettlementdate, DateTime?EndSettlementdate, string DateTime1, int Cid, out int rowCount)
{
string Mess = string.Empty;
rowCount = 0;
GetSettlementByConditionResponse response = new GetSettlementByConditionResponse();
try
{
DataTable dt = new DataTable();
#region 查询条件
//string sb = string.Empty;
StringBuilder sb = new StringBuilder();
if (Cid != 0)
{
sb.Append(" and c.WarehouseID=" + Cid + "");
}
if (StartSettlementdate != null)
{
sb.Append(" AND c.CreateTime >='").Append(StartSettlementdate.DateTimeToString("d")).Append(" 00:00:00'");
}
if (EndSettlementdate != null)
{
sb.Append(" AND c.CreateTime <='").Append(EndSettlementdate.DateTimeToString("d")).Append(" 23:59:59'");
}
if (!string.IsNullOrEmpty(wc.SettlementNumber))
{
sb.Append(" and c.SettlementNumber like '%" + wc.SettlementNumber.Trim() + "%' ");
}
if (!string.IsNullOrEmpty(DateTime1))
{
sb.Append(" and c.str1 ='" + DateTime1.Trim() + "' ");
}
#endregion
int tempRowCount = 0;
DbParam[] dbParams = new DbParam[] {
new DbParam("@Where", DbType.String, sb.ToString(), ParameterDirection.Input),
new DbParam("@PageIndex", DbType.Int32, wc.PageIndex, ParameterDirection.Input),
new DbParam("@PageSize", DbType.Int32, wc.PageSize, ParameterDirection.Input),
new DbParam("@RowCount", DbType.Int32, tempRowCount, ParameterDirection.Output)
};
DataSet ds = ExecuteDataSet("Proc_GetHiltiByCondition", dbParams);
rowCount = (int)dbParams[3].Value;
response.HilSettlementCollection = ds.Tables[0].ConvertToEntityCollection <WMS_HiltibjSettled>();
return(response);
}
catch (Exception ex)
{
return(null);
}
}
//批量导出
public DataSet SummaryExportSettlementBySettlementNumber_b(SettlementSearchCondition wc)
{
string Mess = string.Empty;
GetSettlementByConditionResponse response = new GetSettlementByConditionResponse();
try
{
//保存结算主子表
DataTable dt = new DataTable();
#region 查询条件
string sb = string.Empty;
if (!string.IsNullOrEmpty(wc.CustomerID.ToString()))
{
sb += " and c.CustomerID='" + wc.CustomerID.ToString().Trim() + "' ";
}
if (!string.IsNullOrEmpty(wc.WarehouseID.ToString()))
{
sb += " and c.WarehouseID='" + wc.WarehouseID.ToString().Trim() + "' ";
}
if (!string.IsNullOrEmpty(wc.SettlementNumber))
{
if (wc.SettlementNumber.Contains(","))
{
sb += " and c.SettlementNumber in (";
string[] strs = wc.SettlementNumber.Split(',');
foreach (var str in strs)
{
sb += "'" + str + "',";
}
sb = sb.Substring(0, sb.Length - 1);
sb += ")";
}
else
{
sb += " and c.SettlementNumber='" + wc.SettlementNumber.Trim() + "' ";
}
}
#endregion
DbParam[] dbParams = new DbParam[] {
new DbParam("@Where", DbType.String, sb, ParameterDirection.Input)
};
DataSet ds = this.ExecuteDataSet("Proc_WMS_GetSettlementBySettlementNumber_Export", dbParams);
return(ds);
}
catch (Exception Ex)
{
return(null);
}
}
public DataTable ExportSettlement(WMS_HiltibjSettled wc, DateTime?StartSettlementdate, DateTime?EndSettlementdate, string DateTime1, out int rowCount)
{
string Mess = string.Empty;
rowCount = 0;
GetSettlementByConditionResponse response = new GetSettlementByConditionResponse();
try
{
DataTable dt = new DataTable();
#region 查询条件
//string sb = string.Empty;
StringBuilder sb = new StringBuilder();
if (StartSettlementdate != null)
{
sb.Append(" AND c.CreateTime >='").Append(StartSettlementdate.DateTimeToString("d")).Append(" 00:00:00'");
}
if (EndSettlementdate != null)
{
sb.Append(" AND c.CreateTime <='").Append(EndSettlementdate.DateTimeToString("d")).Append(" 23:59:59'");
}
if (!string.IsNullOrEmpty(wc.SettlementNumber))
{
sb.Append(" and c.SettlementNumber like '%" + wc.SettlementNumber.Trim() + "%' ");
}
if (!string.IsNullOrEmpty(DateTime1))
{
sb.Append(" and c.str1 ='" + DateTime1.Trim() + "' ");
}
#endregion
int tempRowCount = 0;
DbParam[] dbParams = new DbParam[] {
new DbParam("@Where", DbType.String, sb.ToString(), ParameterDirection.Input),
new DbParam("@PageIndex", DbType.Int32, wc.PageIndex, ParameterDirection.Input),
new DbParam("@PageSize", DbType.Int32, wc.PageSize, ParameterDirection.Input),
new DbParam("@RowCount", DbType.Int32, tempRowCount, ParameterDirection.Output)
};
return(base.ExecuteDataTable("Proc_GetHiltiByCondition", dbParams));
}
catch (Exception ex)
{
return(null);
}
}
public GetSettlementByConditionResponse GetPrintSettlementBySettlementNumberNike(string Settlementnumber)
{
GetSettlementByConditionResponse response = new GetSettlementByConditionResponse();
string str = " AND SettlementNumber = '" + Settlementnumber + "'";
DbParam[] dbparams = new DbParam[] {
new DbParam("@Where", DbType.String, str, ParameterDirection.Input)
};
DataSet ds = this.ExecuteDataSet("[Proc_WMS_GetPrintSettlementBySettlementNumberNike]", dbparams);
response.Settlement = ds.Tables[0].ConvertToEntity <Settlement>();
response.SettlementDetailCollection = ds.Tables[1].ConvertToEntityCollection <SettlementDetail>();
return(response);
}
public GetSettlementByConditionResponse GetSettlementPreview(SettlementSearchCondition wc)
{
string Mess = string.Empty;
GetSettlementByConditionResponse response = new GetSettlementByConditionResponse();
DataTable dt = new DataTable();
try
{
string Roles = string.Empty;
#region 查询条件
StringBuilder sb = new StringBuilder();
if (!string.IsNullOrEmpty(wc.CustomerID.ToString()))
{
sb.Append(" AND c.CustomerID=").Append(wc.CustomerID.ToString()).Append(" ");
}
if (!string.IsNullOrEmpty(wc.WarehouseID.ToString()))
{
sb.Append(" AND c.WarehouseID=").Append(wc.WarehouseID.ToString()).Append(" ");
}
if (wc.StartCompleteDate != null)
{
sb.Append(" AND c.CompleteDate >='").Append(wc.StartCompleteDate.DateTimeToString("d")).Append(" 00:00:00'");//开始时间没取到
}
if (wc.EndCompleteDate != null)
{
sb.Append(" AND c.CompleteDate <='").Append(wc.EndCompleteDate.DateTimeToString("d")).Append(" 23:59:59'");//结束时间没取到
}
#endregion
string sqlWhere = sb.ToString();
DbParam[] dbParams = new DbParam[] {
new DbParam("@Where", DbType.String, sqlWhere, ParameterDirection.Input),
};
//条件结算预览
dt = this.ExecuteDataTable("Proc_WMS_SettlementPreview", dbParams);
response.SettlementCollection = dt.ConvertToEntityCollection <Settlement>();
return(response);
}
catch (Exception ex)
{
return(null);
}
}
public string SaveSettlementBySettlementNumber(SettlementSearchCondition wc)
{
string Mess = string.Empty;
GetSettlementByConditionResponse response = new GetSettlementByConditionResponse();
try
{
//string[] qtysum = wc.ActulQtyargs;
//if (qtysum.Length > 0)
//{
string str = string.Empty;
str = " and SettlementNumber='" + wc.SettlementNumber.Trim() + "' ";
DbParam[] dbParams = new DbParam[] {
new DbParam("@Where", DbType.String, str, ParameterDirection.Input)
};
DataTable dt = this.ExecuteDataSet("Proc_WMS_GetSettlementBySettlementNumber", dbParams).Tables[1];
//if (dt.Rows.Count == qtysum.Length)
//{
for (int i = 0; i < dt.Rows.Count; i++)
{
DbParam[] dbUpdate = new DbParam[] {
//new DbParam("@ActualQty", DbType.String,qtysum[i].ToString(),ParameterDirection.Input),
new DbParam("@ID", DbType.Int32, int.Parse(dt.Rows[i]["ID"].ToString()), ParameterDirection.Input)
};
this.ExecuteScalar("Proc_WMS_SaveSettlementBySettlementNumber", dbUpdate);
}
//}
//}
return("更新成功");
}
catch (Exception)
{
return("更新失败");
}
}
//导出
public DataSet ExportSettlementBySettlementNumber_b(SettlementSearchCondition wc)
{
string Mess = string.Empty;
GetSettlementByConditionResponse response = new GetSettlementByConditionResponse();
try
{
//保存结算主子表
DataTable dt = new DataTable();
#region 查询条件
string sb = string.Empty;
if (!string.IsNullOrEmpty(wc.CustomerID.ToString()))
{
sb += " and c.CustomerID='" + wc.CustomerID.ToString().Trim() + "' ";
}
if (!string.IsNullOrEmpty(wc.WarehouseID.ToString()))
{
sb += " and c.WarehouseID='" + wc.WarehouseID.ToString().Trim() + "' ";
}
if (!string.IsNullOrEmpty(wc.SettlementNumber))
{
sb += " and c.SettlementNumber='" + wc.SettlementNumber.Trim() + "' ";
}
#endregion
DbParam[] dbParams = new DbParam[] {
new DbParam("@Where", DbType.String, sb, ParameterDirection.Input)
};
DataSet ds = this.ExecuteDataSet("Proc_WMS_GetSettlementBySettlementNumber_Export", dbParams);
return(ds);
}
catch (Exception Ex)
{
return(null);
}
}
public GetSettlementByConditionResponse GetSettlementListPay(SettlementSearchCondition wc, out int rowCount)
{
string Mess = string.Empty;
rowCount = 0;
GetSettlementByConditionResponse response = new GetSettlementByConditionResponse();
try
{
//保存结算主子表
DataTable dt = new DataTable();
#region 查询条件
//string sb = string.Empty;
StringBuilder sb = new StringBuilder();
if (!string.IsNullOrEmpty(wc.CustomerID.ToString()))
{
sb.Append(" and c.CustomerID =" + wc.CustomerID.ToString().Trim() + " ");
}
if (!string.IsNullOrEmpty(wc.WarehouseID.ToString()))
{
sb.Append(" and c.WarehouseID =" + wc.WarehouseID.ToString().Trim() + " ");
}
if (wc.StartSettlementdate != null)
{
sb.Append(" AND c.CreateTime >='").Append(wc.StartSettlementdate.DateTimeToString("d")).Append(" 00:00:00'");
}
if (wc.EndSettlementdate != null)
{
sb.Append(" AND c.CreateTime <='").Append(wc.EndSettlementdate.DateTimeToString("d")).Append(" 23:59:59'");
}
if (!string.IsNullOrEmpty(wc.SettlementNumber))
{
sb.Append(" and c.SettlementNumber like '%" + wc.SettlementNumber.Trim() + "%' ");
}
if (!string.IsNullOrEmpty(wc.ExternNumber))
{
sb.Append(" and c.ExternNumber like '%" + wc.ExternNumber.Trim() + "%' ");
}
if (!string.IsNullOrEmpty(wc.Month))
{
sb.Append(" and c.SettlementMonth = '" + wc.Month.Trim() + "' ");
}
//if (!string.IsNullOrEmpty(wc.WarehouseName) && wc.WarehouseName != "==请选择==")
//{
//sb += " and c.warehouse= " + wc.Warehouse;
//switch (wc.Warehouse)
//{
// default:
// break;
// case "20":
// wc.Warehouse = "NIKE-OSR广州仓";
// break;
// case "15":
// wc.Warehouse = "NIKE-OSR北京仓";
// break;
// case "22":
// wc.Warehouse = "NIKE-NFS广州仓";
// break;
// case "21":
// wc.Warehouse = "NIKE-NFS北京仓";
// break;
//}
// sb.Append(" and c.WarehouseID= '").Append(wc.WarehouseID.ToString().Trim()).Append("' ");
//}
//if (!string.IsNullOrEmpty(wc.Area) && wc.Area != "==请选择==")
//{
// //sb += " and ae.id='" + wc.Area + "'";
// sb.Append(" and ae.ID=" + wc.Area.Trim() + " ");
//}
//if (wc.Type.ToString() != "0")
//{
// //sb += " and c.Type='" + wc.Type.ToString() + "'";
// sb.Append(" and c.Type='" + wc.Type.ToString().Trim() + "' ");
//}
#endregion
int tempRowCount = 0;
DbParam[] dbParams = new DbParam[] {
new DbParam("@Where", DbType.String, sb.ToString(), ParameterDirection.Input),
new DbParam("@PageIndex", DbType.Int32, wc.PageIndex, ParameterDirection.Input),
new DbParam("@PageSize", DbType.Int32, wc.PageSize, ParameterDirection.Input),
new DbParam("@RowCount", DbType.Int32, tempRowCount, ParameterDirection.Output)
};
DataSet ds = ExecuteDataSet("Proc_WMS_GetSettlementByConditionPay", dbParams);
rowCount = (int)dbParams[3].Value;
response.SettlementCollection = ds.Tables[0].ConvertToEntityCollection <Settlement>();
return(response);
}
catch (Exception ex)
{
return(null);
}
}
