public List<IpoNvdLogQuery> GetIpoNvdLogList(IpoNvdLogQuery query, out int totalCount)
{
try
{
return _IpoNvdLogDao.GetIpoNvdLogList(query, out totalCount);
}
catch (Exception ex)
{
throw new Exception("IpoNvdLogMgr-->GetIpoNvdLogList-->" + ex.Message, ex);
}
}
/// <summary>
/// IpoNvdLog查詢
/// </summary>
/// <returns></returns>
public HttpResponseBase GetIpoNvdLogList()
{
string json = string.Empty;
int totalcount = 0;
IpoNvdLogQuery query = new IpoNvdLogQuery();
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
ipoNvdLogMgr = new IpoNvdLogMgr(mySqlConnectionString);
if (!string.IsNullOrEmpty(Request.Params["work_id"].Trim()))
{
query.work_id = Request.Params["work_id"].Trim();
}
if (!string.IsNullOrEmpty(Request.Params["ipo_id"].Trim()))
{
query.ipo_id = Request.Params["ipo_id"].Trim();
}
if (!string.IsNullOrEmpty(Request.Params["itemId_or_upcId"].Trim()))
{
uint itemId = Convert.ToUInt32(Request.Params["itemId_or_upcId"].Trim());
bool result = ipoNvdLogMgr.GetInfoByItemId(itemId);
if (result == false)
{
query.upc_id = Request.Params["itemId_or_upcId"].Trim();//條碼
}
else
{
query.item_id = Convert.ToUInt32(Request.Params["itemId_or_upcId"].Trim());
}
}
if (!string.IsNullOrEmpty(Request.Params["loc_id"].Trim()))
{
query.loc_id = Request.Params["loc_id"].Trim();
}
if (!string.IsNullOrEmpty(Request.Params["time_start"]))//開始時間
{
query.start_time = Convert.ToDateTime(Convert.ToDateTime(Request.Params["time_start"]).ToString("yyyy-MM-dd 00:00:00"));
}
if (!string.IsNullOrEmpty(Request.Params["time_end"]))//結束時間
{
query.end_time = Convert.ToDateTime(Convert.ToDateTime(Request.Params["time_end"]).ToString("yyyy-MM-dd 23:59:59"));
}
try
{
List<IpoNvdLogQuery> list = ipoNvdLogMgr.GetIpoNvdLogList(query, out totalcount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalcount + ",data:" + JsonConvert.SerializeObject(list, Formatting.Indented, timeConverter) + "}";
}
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:true,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return Response;
}
public bool SaveReceiptShelves(IpoNvdQuery invd_query,int pick_num)
{
try
{
//更新IpoNvd 表
IpoNvdQuery query = _IpoNvdDao.GetIpoNvd(invd_query);
query.modify_user = invd_query.modify_user;
query.made_date = invd_query.made_date;
query.cde_dt = invd_query.cde_dt;
query.out_qty = query.out_qty - pick_num;
query.com_qty = query.com_qty + pick_num;
if (query.out_qty > 0)
{
query.work_status = "SKP";
}
else if (query.out_qty == 0)
{
query.work_status = "COM";
}
string UpdateIpoNvdSql = _IpoNvdDao.UpdateIpoNvdSql(query);
//更新IpoNvdLog表
IpoNvdLogQuery invdLog = new IpoNvdLogQuery();
invdLog.work_id = query.work_id;
invdLog.ipo_id = query.ipo_id;
invdLog.item_id = (uint)query.item_id;
invdLog.add_qty = pick_num;
invdLog.cde_date = query.cde_dt;
invdLog.made_date = query.made_date;
invdLog.create_user = query.modify_user;
string InsertIpoNvdLogSql = _IpoNvdDao.InsertIpoNvdLogSql(invdLog);
//更新iinvd表
IinvdQuery iinvd_query = new IinvdQuery();
iinvd_query.made_date = query.made_date;
iinvd_query.cde_dt = query.cde_dt;
iinvd_query.prod_qty = pick_num;
iinvd_query.ista_id = "A";
iinvd_query.create_user = invd_query.modify_user;
iinvd_query.change_user = invd_query.modify_user;
iinvd_query.plas_loc_id = invd_query.loc_id;
iinvd_query.item_id = query.item_id;
string ista_id = string.Empty;
string UpdateIinvdSql = _iinvdDao.UpdateIinvdSql(iinvd_query, out ista_id);
//更新istockchange表
IstockChangeQuery stock_query = new IstockChangeQuery();
stock_query.sc_trans_id = "";
stock_query.sc_cd_id = "";
stock_query.item_id = query.item_id;
stock_query.sc_trans_type = 1;
stock_query.sc_num_chg = pick_num;
stock_query.sc_time = DateTime.Now;
stock_query.sc_user = invd_query.modify_user;
stock_query.sc_note = "收貨上架";
stock_query.sc_istock_why = 4;
string insertIstockChangeSql = string.Empty;
if (ista_id != "H")
{
insertIstockChangeSql = istockchangeDao.insertIstockChangeSql(stock_query);
}
//執行SQL
ArrayList arrList = new ArrayList();
arrList.Add(UpdateIpoNvdSql);
arrList.Add(InsertIpoNvdLogSql);
arrList.Add(UpdateIinvdSql);
if (ista_id != "H")
{
arrList.Add(insertIstockChangeSql);
}
bool result = myDao.ExcuteSqls(arrList);
return result;
}
catch (Exception ex)
{
throw new Exception("IpoNvdMgr-->SaveReceiptShelves-->" + ex.Message, ex);
}
}
/// <summary>
/// 採購單,列表頁
/// </summary>
/// <param name="query"></param>
/// <param name="totalcount"></param>
/// <returns></returns>
public List<IpoNvdLogQuery> GetIpoNvdLogList(IpoNvdLogQuery query, out int totalCount)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
StringBuilder conndSql = new StringBuilder();
try
{
sql.Append(@"select inl.row_id,inl.`work_id`, inl.`ipo_id`, inl.item_id, i.upc_id,ip.loc_id, `add_qty`, `made_date`, `cde_date`,pe.pwy_dte_ctl,mu.user_username as create_user_string, inl.`create_datetime`
FROM ipo_nvd_log inl LEFT JOIN iupc i on inl.item_id=i.item_id
INNER JOIN manage_user mu on mu.user_id=inl.create_user
left join iplas ip on ip.item_id=inl.item_id
left join product_ext pe on pe.item_id=inl.item_id
where 1=1 ");
if (!string.IsNullOrEmpty(query.work_id))
{
conndSql.AppendFormat(" and inl.work_id ='{0}' ", query.work_id);
}
if (!string.IsNullOrEmpty(query.ipo_id))
{
conndSql.AppendFormat(" and inl.ipo_id ='{0}' ", query.ipo_id);
}
if (!string.IsNullOrEmpty(query.upc_id))
{
conndSql.AppendFormat(" and i.upc_id ='{0}' ", query.upc_id);
}
if (query.item_id !=0)
{
conndSql.AppendFormat(" and inl.item_id ='{0}' ", query.item_id);
}
if (!string.IsNullOrEmpty(query.loc_id))
{
conndSql.AppendFormat(" and ip.loc_id ='{0}' ", query.loc_id);
}
if (query.start_time!=DateTime.MinValue)
{
conndSql.AppendFormat(" and inl.create_datetime >= '{0}'", Common.CommonFunction.DateTimeToString(query.start_time));
}
if (query.end_time != DateTime.MinValue)
{
conndSql.AppendFormat(" and inl.create_datetime <= '{0}'", Common.CommonFunction.DateTimeToString(query.end_time));
}
conndSql.Append("group by inl.row_id order by inl.create_datetime desc ");
sql = sql.Append(conndSql.ToString());
totalCount = 0;
if (query.IsPage)
{
DataTable _dt = new DataTable();
_dt = _access.getDataTable(@"select count(tab.row_id) as totalCount from (select inl.row_id FROM ipo_nvd_log inl LEFT JOIN iupc i on inl.item_id = i.item_id
INNER JOIN manage_user mu on mu.user_id=inl.create_user
left join iplas ip on ip.item_id=inl.item_id
where 1=1 " + conndSql.ToString() + ") tab");
if (_dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
sql.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
}
return _access.getDataTableForObj<IpoNvdLogQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("IpoNvdLogDao-->GetIpoNvdLogList-->" + ex.Message + sql.ToString(), ex);
}
}
public string InsertIpoNvdLogSql(IpoNvdLogQuery query)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat(@" INSERT INTO `ipo_nvd_log` (`work_id`, `ipo_id`, `item_id`, `add_qty`, `made_date`, `cde_date`, `create_user`, `create_datetime`) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}');", query.work_id, query.ipo_id, query.item_id, query.add_qty, query.made_date.ToString("yyyy-MM-dd"), query.cde_date.ToString("yyyy-MM-dd"), query.create_user, CommonFunction.DateTimeToString(DateTime.Now));
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("IpoNvdDao.InsertIpoNvdLogSql-->" + ex.Message + sql.ToString(), ex);
}
}
