public int UpdateIcdStatus(IlocChangeDetailQuery ilocDetailQuery)
{
try
{
return _ilocDao.UpdateIcdStatus(ilocDetailQuery);
}
catch (Exception ex)
{
throw new Exception("ChannelContactMgr-->UpdateIcdStatus-->" + ex.Message, ex);
}
}
public DataTable GetIlocChangeDetailExcelList(IlocChangeDetailQuery ilocDetailQuery)
{
try
{
return _ilocDao.GetIlocChangeDetailExcelList(ilocDetailQuery);
}
catch (Exception ex)
{
throw new Exception("ChannelContactMgr-->GetIlocChangeDetailExcelList-->" + ex.Message, ex);
}
}
public List<IlocChangeDetailQuery> GetIlocChangeDetailList(IlocChangeDetailQuery query, out int totalcount)
{
try
{
return _ilocDao.GetIlocChangeDetailList(query, out totalcount);
}
catch (Exception ex)
{
throw new Exception("ChannelContactMgr-->GetIlocChangeDetailList-->" + ex.Message, ex);
}
}
public DataTable GetIlocChangeDetailExcelList(IlocChangeDetailQuery ilocDetailQuery)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlwhere = new StringBuilder();
try
{
sql.AppendLine(@"SELECT icd.icd_item_id,pt.product_name,'' as 'product_sz',pt.prepaid ,icd.icd_old_loc_id,pe.cde_dt_incr,icd.icd_create_time,icd.icd_new_loc_id,pe.pwy_dte_ctl,'' as 'isjq','' as 'isgq',cde_dt_var,cde_dt_shp,icd.icd_create_time,mu.user_username,icd.icd_status
FROM iloc_change_detail icd
LEFT JOIN product_ext pe on icd.icd_item_id = pe.item_id
LEFT JOIN product_item pii on icd.icd_item_id=pii.item_id
LEFT JOIN product pt on pii.product_id=pt.product_id
LEFT JOIN manage_user mu on icd.icd_create_user=mu.user_id where 1=1 ");
if (ilocDetailQuery.icd_id != 0)
{
sqlwhere.AppendFormat(" and icd.icd_id ='{0}'", ilocDetailQuery.icd_id);
}
if (!string.IsNullOrEmpty(ilocDetailQuery.productids))
{
sqlwhere.AppendFormat(" and icd.icd_item_id in ({0})", ilocDetailQuery.productids);
}
if (!string.IsNullOrEmpty(ilocDetailQuery.icd_old_loc_id.Trim()))
{
sqlwhere.AppendFormat(" and icd.icd_old_loc_id ='{0}'", ilocDetailQuery.icd_old_loc_id);
}
if (!string.IsNullOrEmpty(ilocDetailQuery.icd_new_loc_id.Trim()))
{
sqlwhere.AppendFormat(" and icd.icd_new_loc_id ='{0}'", ilocDetailQuery.icd_new_loc_id);
}
DateTime dt = DateTime.Parse("1970-01-02 08:00:00");
if (!string.IsNullOrEmpty(ilocDetailQuery.start_time.ToString()) && dt < ilocDetailQuery.start_time)
{
sqlwhere.AppendFormat(" and icd.icd_create_time >='{0}' ", CommonFunction.DateTimeToString(ilocDetailQuery.start_time));
}
if (!string.IsNullOrEmpty(ilocDetailQuery.end_time.ToString()) && dt < ilocDetailQuery.end_time)
{
sqlwhere.AppendFormat(" and icd.icd_create_time <='{0}' ", CommonFunction.DateTimeToString(ilocDetailQuery.end_time));
}
if (!string.IsNullOrEmpty(ilocDetailQuery.startloc.Trim()))
{
sqlwhere.AppendFormat(" and icd.icd_new_loc_id >='{0}'", ilocDetailQuery.startloc);
}
if (!string.IsNullOrEmpty(ilocDetailQuery.endloc.Trim()))
{
sqlwhere.AppendFormat(" and icd.icd_new_loc_id <='{0}'", ilocDetailQuery.endloc);
}
if (!string.IsNullOrEmpty(ilocDetailQuery.icd_status))
{
sqlwhere.AppendFormat(" and icd.icd_status='{0}' ", ilocDetailQuery.icd_status);
}
return _access.getDataTable(sql.ToString() + sqlwhere.ToString());
}
catch (Exception ex)
{
throw new Exception("IlocChangeDetail-->GetIlocChangeDetailList-->" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// 列表頁
/// </summary>
/// <param name="ilocDetailQuery"></param>
/// <param name="totalcount"></param>
/// <returns></returns>
public List<IlocChangeDetailQuery> GetIlocChangeDetailList(IlocChangeDetailQuery q, out int totalcount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlwhere = new StringBuilder();
totalcount = 0;
try
{
sql.AppendLine(@"SELECT icd.icd_id,icd.icd_item_id,pt.product_name,'' as 'product_sz',icd.icd_status,pt.prepaid ,icd.icd_old_loc_id,icd.icd_create_time,icd.icd_new_loc_id,pe.cde_dt_incr,pe.pwy_dte_ctl,'' as 'isjq','' as 'isgq',cde_dt_var,cde_dt_shp,icd.icd_create_time,mu.user_username
FROM iloc_change_detail icd
LEFT JOIN product_ext pe on icd.icd_item_id = pe.item_id
LEFT JOIN product_item pii on icd.icd_item_id=pii.item_id
LEFT JOIN product pt on pii.product_id=pt.product_id
LEFT JOIN manage_user mu on icd.icd_create_user=mu.user_id where 1=1 ");
if (!string.IsNullOrEmpty(q.productids))
{
sqlwhere.AppendFormat(" and icd.icd_item_id in ({0})", q.productids);
}
if (!string.IsNullOrEmpty(q.icd_old_loc_id.Trim()))
{
sqlwhere.AppendFormat(" and icd.icd_old_loc_id ='{0}'", q.icd_old_loc_id);
}
if (!string.IsNullOrEmpty(q.icd_new_loc_id.Trim()))
{
sqlwhere.AppendFormat(" and icd.icd_new_loc_id ='{0}'", q.icd_new_loc_id);
}
if (!string.IsNullOrEmpty(q.start_time.ToString()) && q.start_time > DateTime.MinValue)
{
sqlwhere.AppendFormat(" and icd.icd_create_time >='{0}'", CommonFunction.DateTimeToString(q.start_time));
}
if (!string.IsNullOrEmpty(q.end_time.ToString()) && q.end_time > DateTime.MinValue)
{
sqlwhere.AppendFormat(" and icd.icd_create_time <='{0}'", CommonFunction.DateTimeToString(q.end_time));
}
if (!string.IsNullOrEmpty(q.startloc.Trim()))
{
sqlwhere.AppendFormat(" and icd.icd_new_loc_id >='{0}'", q.startloc);
}
if (!string.IsNullOrEmpty(q.endloc.Trim()))
{
sqlwhere.AppendFormat(" and icd.icd_new_loc_id <='{0}'", q.endloc);
}
if (!string.IsNullOrEmpty(q.icd_status))
{
sqlwhere.AppendFormat(" and icd.icd_status='{0}' ", q.icd_status);
}
if (q.IsPage)
{
System.Data.DataTable _dt = _access.getDataTable(sql.ToString() + sqlwhere.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalcount = _dt.Rows.Count;
}
sqlwhere.AppendFormat(" limit {0},{1}", q.Start, q.Limit);
}
return _access.getDataTableForObj<IlocChangeDetailQuery>(sql.ToString() + sqlwhere.ToString());
}
catch (Exception ex)
{
throw new Exception("IlocChangeDetail-->GetIlocChangeDetailList-->" + ex.Message + sql.ToString() + sqlwhere.ToString(), ex);
}
}
public int UpdateIcdStatus(IlocChangeDetailQuery ilocDetailQuery)
{
StringBuilder sql = new StringBuilder();
try
{
if (!string.IsNullOrEmpty(ilocDetailQuery.icd_id_In))
{
sql.AppendFormat(" set sql_safe_updates=0; update iloc_change_detail set icd_status='COM',icd_modify_time='{0}',icd_modify_user='******' where icd_id in ({2});set sql_safe_updates=1;", Common.CommonFunction.DateTimeToString(ilocDetailQuery.icd_modify_time),ilocDetailQuery.icd_modify_user,ilocDetailQuery.icd_id_In);
return _access.execCommand(sql.ToString());
}
else
{
return 0;
}
}
catch (Exception ex)
{
throw new Exception("IlocChangeDetail-->UpdateIcdStatus-->" + ex.Message + sql.ToString(), ex);
}
}
//料位移動之搬移
public HttpResponseBase GetIlocChangeDetailEdit()
{
string json = string.Empty;
IlocChangeDetailQuery ilocDetailQuery = new IlocChangeDetailQuery();
_ilocDetailMger = new IlocChangeDetailMgr(mySqlConnectionString);
try
{
if (!string.IsNullOrEmpty(Request.Params["icd_id_In"]))//
{
ilocDetailQuery.icd_id_In = Request.Params["icd_id_In"];
ilocDetailQuery.icd_id_In = ilocDetailQuery.icd_id_In.TrimEnd(',');
}
ilocDetailQuery.icd_modify_time = DateTime.Now;
ilocDetailQuery.icd_modify_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id;
if (_ilocDetailMger.UpdateIcdStatus(ilocDetailQuery) > 0)
{
json = "{success:true}";//返回json數據
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public void IlocChangeDetailExcelList()
{
string json = string.Empty;
IlocChangeDetailQuery ilocDetailQuery = new IlocChangeDetailQuery();
DataTable dtExcel = new DataTable();
try
{
if (!string.IsNullOrEmpty(Request.Params["productids"]))
{
ilocDetailQuery.productids = Request.Params["productids"].Replace('，', ',').Replace('|', ',').Replace(' ', ',');
}
if (!string.IsNullOrEmpty(Request.Params["oldilocid"]))//model中默認為F
{
ilocDetailQuery.icd_old_loc_id = Request.Params["oldilocid"];
}
if (!string.IsNullOrEmpty(Request.Params["newilocid"]))
{
ilocDetailQuery.icd_new_loc_id = Request.Params["newilocid"].ToString();
}
DateTime time;
if (DateTime.TryParse(Request.Params["start_time"].ToString(), out time))
{
ilocDetailQuery.start_time = DateTime.Parse(time.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (DateTime.TryParse(Request.Params["end_time"].ToString(), out time))
{
ilocDetailQuery.end_time = DateTime.Parse(time.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(Request.Params["startloc"]))
{
ilocDetailQuery.startloc = Request.Params["startloc"].ToUpper();
}
if (!string.IsNullOrEmpty(Request.Params["endloc"]))
{
ilocDetailQuery.endloc = Request.Params["endloc"].ToUpper() + "Z";
}
if (!string.IsNullOrEmpty(Request.Params["icd_status"]) && Request.Params["icd_status"] != "全部")
{
ilocDetailQuery.icd_status = Request.Params["icd_status"];
}
DataTable store = new DataTable();
_ilocDetailMger = new IlocChangeDetailMgr(mySqlConnectionString);
store = _ilocDetailMger.GetIlocChangeDetailExcelList(ilocDetailQuery);
dtExcel.Columns.Add("商品編號", typeof(String));
dtExcel.Columns.Add("商品名稱", typeof(String));
dtExcel.Columns.Add("規格", typeof(String));
dtExcel.Columns.Add("是否買斷", typeof(String));
dtExcel.Columns.Add("原料位編號", typeof(String));
dtExcel.Columns.Add("新料位編號", typeof(String));
// dtExcel.Columns.Add("製造日期", typeof(String));
//dtExcel.Columns.Add("保存期限", typeof(String));
dtExcel.Columns.Add("搬移日期", typeof(String));
dtExcel.Columns.Add("庫存", typeof(String));
dtExcel.Columns.Add("是否有效期控管", typeof(String));
//dtExcel.Columns.Add("是否即期", typeof(String));
//dtExcel.Columns.Add("是否過期", typeof(String));
dtExcel.Columns.Add("允收天數", typeof(String));
dtExcel.Columns.Add("允出天數", typeof(String));
dtExcel.Columns.Add("搬移狀態", typeof(String));
IinvdMgr iinvdMgr = new IinvdMgr(mySqlConnectionString);
List<Parametersrc> stores = new List<Parametersrc>();
_ptersrc = new ParameterMgr(mySqlConnectionString);
stores = _ptersrc.GetElementType("icd_status");
for (int i = 0; i < store.Rows.Count; i++)
{
Iinvd invd = new Iinvd();
invd.item_id = uint.Parse(store.Rows[i]["icd_item_id"].ToString());
DataRow newRow = dtExcel.NewRow();
newRow[0] = store.Rows[i]["icd_item_id"].ToString();
newRow[1] = store.Rows[i]["product_name"].ToString();
newRow[2] = GetProductSpec(store.Rows[i]["icd_item_id"].ToString());
newRow[3] = store.Rows[i]["prepaid"].ToString() == "1" ? "是" : "否";
newRow[4] = store.Rows[i]["icd_old_loc_id"].ToString();
newRow[5] = store.Rows[i]["icd_new_loc_id"].ToString();
// newRow[6] = store.Rows[i]["made_date"].ToString();
newRow[6] = store.Rows[i]["icd_create_time"].ToString();
// newRow[8] = store.Rows[i]["cde_dt"].ToString();
newRow[7] = iinvdMgr.SumProd_qty(invd);//store.Rows[i]["prod_qty"].ToString();
newRow[8] = store.Rows[i]["pwy_dte_ctl"].ToString();
//if (store.Rows[i]["pwy_dte_ctl"].ToString() == "Y")
//{
// DateTime made = DateTime.Parse(store.Rows[i]["made_date"].ToString());
// int incr = int.Parse(store.Rows[i]["cde_dt_incr"].ToString());
// int shp = int.Parse(store.Rows[i]["cde_dt_shp"].ToString());
// if (made.AddDays(incr) < DateTime.Now)
// {
// newRow[9] = "是";
// }
// else if (made.AddDays(incr - shp) < DateTime.Now)
// {
// newRow[10] = "是";
// }
// else
// {
// newRow[10] = "否";
// newRow[9] = "否";
// }
//}
//else
//{
// newRow[9] = "";
// newRow[10] = "";
//}
newRow[9] = store.Rows[i]["cde_dt_var"].ToString();
newRow[10] = store.Rows[i]["cde_dt_shp"].ToString();
for (int a = 0; a < stores.Count; a++)
{
if (stores[a].ParameterCode == store.Rows[i]["icd_status"].ToString())
{
newRow[11] = stores[a].parameterName;
}
}
dtExcel.Rows.Add(newRow);
}
if (dtExcel.Rows.Count > 0)
{
string fileName = "料位移動查詢_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
MemoryStream ms = ExcelHelperXhf.ExportDT(dtExcel, "料位移動查詢_" + DateTime.Now.ToString("yyyyMMddHHmmss"));
Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.BinaryWrite(ms.ToArray());
}
else
{
Response.Write("匯出數據不存在");
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
}
}
public HttpResponseBase MaterialHandling()
{//判斷寄倉或者調度
string json = String.Empty;
string id = Request.Params["icd"];
IlocChangeDetailQuery ilocDetailQuery = new IlocChangeDetailQuery();
ilocDetailQuery.icd_id = int.Parse(id);
DataTable _dt = new DataTable();
_ilocDetailMger = new IlocChangeDetailMgr(mySqlConnectionString);
_dt = _ilocDetailMger.GetIlocChangeDetailExcelList(ilocDetailQuery);
foreach (DataRow item in _dt.Rows)
{
item["product_sz"] = GetProductSpec(item["icd_item_id"].ToString());
// item.product_sz = GetProductSpec(item.icd_item_id.ToString());
}
try
{
if (_dt.Rows.Count > 0)
{//獲取寄倉信息
json = "{success:true,data:" + JsonConvert.SerializeObject(_dt, Formatting.Indented) + "}";//返回json數據
}
json = "{success:true,data:" + JsonConvert.SerializeObject(_dt, Formatting.Indented) + "}";//返回json數據
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,msg:0}";
}
this.Response.Clear();
this.Response.Write(json.ToString());
this.Response.End();
return this.Response;
}
public HttpResponseBase GetIlocChangeDetailList()
{
string json = string.Empty;
IlocChangeDetailQuery ilocDetailQuery = new IlocChangeDetailQuery();
ilocDetailQuery.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
ilocDetailQuery.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量
if (!string.IsNullOrEmpty(Request.Params["productids"]))
{
ilocDetailQuery.productids = Request.Params["productids"].Replace('，', ',').Replace('|', ',').Replace(' ', ',');
}
if (!string.IsNullOrEmpty(Request.Params["oldilocid"]))//model中默認為F
{
ilocDetailQuery.icd_old_loc_id = Request.Params["oldilocid"];
}
if (!string.IsNullOrEmpty(Request.Params["newilocid"]))
{
ilocDetailQuery.icd_new_loc_id = Request.Params["newilocid"].ToString();
}
DateTime time;
if (DateTime.TryParse(Request.Params["start_time"].ToString(), out time))
{
ilocDetailQuery.start_time = DateTime.Parse(time.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (DateTime.TryParse(Request.Params["end_time"].ToString(), out time))
{
ilocDetailQuery.end_time = DateTime.Parse(time.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(Request.Params["startloc"]))
{
ilocDetailQuery.startloc = Request.Params["startloc"].ToUpper();
}
if (!string.IsNullOrEmpty(Request.Params["endloc"]))
{
ilocDetailQuery.endloc = Request.Params["endloc"].ToUpper() + "Z";
}
if (!string.IsNullOrEmpty(Request.Params["icd_status"]) && Request.Params["icd_status"] != "全部")
{
ilocDetailQuery.icd_status = Request.Params["icd_status"];
}
try
{
List<IlocChangeDetailQuery> store = new List<IlocChangeDetailQuery>();
_ilocDetailMger = new IlocChangeDetailMgr(mySqlConnectionString);
int totalCount = 0;
store = _ilocDetailMger.GetIlocChangeDetailList(ilocDetailQuery, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
IinvdMgr iinvdMgr = new IinvdMgr(mySqlConnectionString);
foreach (var item in store)
{
Iinvd invd = new Iinvd();
invd.item_id = item.icd_item_id;
item.prod_qty = iinvdMgr.SumProd_qty(invd);
if (item.prepaid == 0)
{
item.prepa_name = "否";
}
else
{
item.prepa_name = "是";
}
if (item.icd_item_id > 0)
{
item.product_sz = GetProductSpec(item.icd_item_id.ToString());
}
//if (item.pwy_dte_ctl == "Y")
//{
// if (item.made_date.AddDays(item.cde_dt_incr) < DateTime.Now)
// {
// item.isgq = "是";
// }
// else if (item.made_date.AddDays(item.cde_dt_incr - item.cde_dt_shp) < DateTime.Now)
// {
// item.isjq = "是";
// }
// else
// {
// item.isgq = "否";
// item.isjq = "否";
// }
//}
//else
//{
// item.isgq = "-";
// item.isjq = "-";
//}
}
json = "{success:true,'msg':'user',totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";//返回json數據
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
