public List<IplasQuery> GetIplas(IplasQuery m, out int totalCount)
{
try
{
return _IplasDao.GetIplas(m, out totalCount);
}
catch (Exception ex)
{
throw new Exception("IplasMgr-->GetIplas-->" + ex.Message, ex);
}
}
public int DeleteIplasById(IplasQuery plas)
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat(" set sql_safe_updates = 0;DELETE FROM iplas WHERE iplas.loc_id ='{0}';set sql_safe_updates = 1; ", plas.loc_id.ToString().ToUpper());
sql.AppendFormat(" set sql_safe_updates = 0; update iloc set lsta_id='F' where loc_id='{0}';set sql_safe_updates = 1; ", plas.loc_id.ToString().ToUpper());
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
int i = 0;
try
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
mySqlConn.Open();
}
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
mySqlCmd.CommandText = sql.ToString();
i = mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("IplaseDao.DeleteIplasById-->" + ex.Message + sql.ToString(), ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return i;
}
/// <summary>
/// 查詢商品主料位
/// </summary>
/// <param name="m">實體</param>
/// <param name="totalCount">總條數，用來分頁</param>
/// <returns>返回列表頁</returns>
public List<IplasQuery> GetIplas(IplasQuery m, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sb = new StringBuilder();
StringBuilder sqlwhere = new StringBuilder();
totalCount = 0;
try
{
sb.AppendLine("select Count(ip.plas_id) as totalcounts from iplas ip where 1=1 ");
sql.AppendLine(@"select mu.user_username as create_users, CONCAT(vb.brand_name,'-',p.product_name) as product_name,plas_id,dc_id,whse_id,loc_id,lcus_id,ip.item_id,prdd_id,ip.create_user,ip.create_dtim,loc_stor_cse_cap from iplas ip ");
sql.AppendLine(@"left JOIN product_item pi ON ip.item_id=pi.item_id ");
sql.AppendLine(@"left JOIN manage_user mu on mu.user_id=ip.create_user ");
sql.AppendLine(@"left JOIN product p ON p.product_id=pi.product_id ");
sql.AppendLine(@"left join vendor_brand vb on p.brand_id=vb.brand_id where 1=1 ");
if (m.serch_type != 0)
{
if (!string.IsNullOrEmpty(m.searchcontent))
{
switch (m.serch_type)
{
case 1:
sqlwhere.AppendFormat(" and ip.loc_id in (select loc_id from iloc where loc_id='{0}' or hash_loc_id='{0}' ) ", m.searchcontent);
break;
case 2:
sqlwhere.AppendFormat(" and ip.item_id=(select item_id from iupc iu where iu.upc_id ='{0}') ", m.searchcontent);
break;
case 3:
sqlwhere.AppendFormat(" and ip.item_id in ({0}) ", m.searchcontent);
break;
default:
break;
}
}
}
DateTime dt = DateTime.Parse("1970-01-02 08:00:00");
if (!string.IsNullOrEmpty(m.starttime.ToString()) && dt < m.starttime)
{
sqlwhere.AppendFormat(" and ip.create_dtim>='{0}' ", m.starttime.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(m.endtime.ToString()) && dt < m.endtime)
{
sqlwhere.AppendFormat(" and ip.create_dtim<='{0}' ", m.endtime.ToString("yyyy-MM-dd HH:mm:ss"));
}
//DateTime dt = DateTime.MinValue;
//if (!string.IsNullOrEmpty(m.starttime.ToString()) && m.starttime>dt)
//{
// sqlwhere.AppendFormat(" and ip.create_dtim > '{0}' ", m.starttime);
//}
//if (!string.IsNullOrEmpty(m.endtime.ToString()) && m.endtime > dt)
//{
// sqlwhere.AppendFormat(" and ip.create_dtim < '{0}' ", m.endtime);
//}
totalCount = 0;
if (m.IsPage)
{
DataTable _dt = _dbAccess.getDataTable(sb.ToString() + sqlwhere.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalcounts"]);
}
sqlwhere.AppendFormat(" limit {0},{1}", m.Start, m.Limit);
}
return _dbAccess.getDataTableForObj<IplasQuery>(sql.ToString() + sqlwhere.ToString());
}
catch (Exception ex)
{
throw new Exception("IplasDao-->GetIplas-->" + ex.Message + sql.ToString() + sqlwhere.ToString(), ex);
}
}
public int GetIplasid(IplasQuery plas)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat(@"SELECT plas_id FROM iplas WHERE item_id='{0}' ", plas.item_id);
return _dbAccess.getDataTable(sql.ToString()).Rows.Count;
}
catch (Exception ex)
{
throw new Exception("IplasDao-->GetIplasid-->" + ex.Message + sql.ToString(), ex);
}
}
public void ExportInoiupc()
{
string json = string.Empty;
IplasQuery m = new IplasQuery();
if (!string.IsNullOrEmpty(Request.Params["startIloc"]))
{//料位開始
m.startloc = Request.Params["startIloc"].ToUpper();
}
if (!string.IsNullOrEmpty(Request.Params["endIloc"]))
{
m.endloc = Request.Params["endIloc"] + "Z";
m.endloc = m.endloc.ToUpper();
}
if (!System.IO.Directory.Exists(Server.MapPath(excelPath)))
{
System.IO.Directory.CreateDirectory(Server.MapPath(excelPath));
}
DataTable dtHZ = new DataTable();
string newExcelName = string.Empty;
dtHZ.Columns.Add("料位", typeof(String));
dtHZ.Columns.Add("屬性", typeof(String));
dtHZ.Columns.Add("品號", typeof(String));
dtHZ.Columns.Add("數量", typeof(String));
dtHZ.Columns.Add("品名", typeof(String));
dtHZ.Columns.Add("規格", typeof(String));
dtHZ.Columns.Add("條碼", typeof(String));
dtHZ.Columns.Add("有效日期", typeof(String));
try
{
//List<IplasQuery> store = new List<IplasQuery>();
DataTable _dt = new DataTable();
_IiplasMgr = new IplasMgr(mySqlConnectionString);
//store = _IiplasMgr.Export(m);
_dt = _IiplasMgr.ExportMessage(m);
foreach (DataRow Drow in _dt.Rows)
{
DataRow dr = dtHZ.NewRow();
dr[0] = Drow["loc_id"];
dr[1] = Drow["lcat_id"];
dr[2] = Drow["item_id"];
dr[3] = Drow["prod_qtys"];
dr[4] = Drow["product_name"];
dr[5] = Drow["prod_sz"];
dr[6] = " " + Drow["upc_id"];
if (Drow["cde_dt"].ToString() != null && Drow["cde_dt"].ToString().Trim() != "")
{
dr[7] = DateTime.Parse(Drow["cde_dt"].ToString()).ToString("yyyy-MM-dd");
}
else
{
dr[7] = "";
}
dtHZ.Rows.Add(dr);
}
string fileName = DateTime.Now.ToString("無條碼商品報表_yyyyMMddHHmm") + ".xls";
MemoryStream ms = ExcelHelperXhf.ExportDT(dtHZ, "無條碼商品報表_" + DateTime.Now.ToString("yyyyMMddHHmm"));
Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.BinaryWrite(ms.ToArray());
}
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,data:[]}";
}
}
public void IplasExcelList()
{
string json = string.Empty;
IplasQuery ilpsQuery = new IplasQuery();
DataTable dtExcel = new DataTable();
try
{
string content = string.Empty;
if (!string.IsNullOrEmpty(Request.Params["search_type"]))
{
ilpsQuery.serch_type = int.Parse(Request.Params["search_type"]);
if (!string.IsNullOrEmpty(Request.Params["searchcontent"]) && Request.Params["searchcontent"].Trim().Length > 0)//有查詢內容就不管時間
{
switch (ilpsQuery.serch_type)
{
case 1:
case 2:
ilpsQuery.searchcontent = Request.Params["searchcontent"].Trim();
break;
case 3:
#region 之後的更改
content = Request.Params["searchcontent"].Replace('，', ',').Replace('|', ',').Replace(' ', ',');//.Replace(' ',',')
string[] list = content.Split(',');
string test = "^[0-9]*$";
int count = 0;//實現最後一個不加,
for (int i = 0; i < list.Length; i++)
{
if (!string.IsNullOrEmpty(list[i]))
{
if (Regex.IsMatch(list[i], test))
{
count = count + 1;
if (count == 1)
{
ilpsQuery.searchcontent = list[i];
}
else
{
ilpsQuery.searchcontent = ilpsQuery.searchcontent + "," + list[i];
}
}
else
{
ilpsQuery.searchcontent = ilpsQuery.searchcontent + list[i] + ",";
}
}
}
#endregion
break;
default:
break;
}
}
}
DateTime time;
if (DateTime.TryParse(Request.Params["starttime"].ToString(), out time))
{
ilpsQuery.starttime = DateTime.Parse(time.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (DateTime.TryParse(Request.Params["endtime"].ToString(), out time))
{
ilpsQuery.endtime = DateTime.Parse(time.ToString("yyyy-MM-dd HH:mm:ss"));
}
List<IplasQuery> store = new List<IplasQuery>();
_IiplasMgr = new IplasMgr(mySqlConnectionString);
store = _IiplasMgr.GetIplasExportList(ilpsQuery);
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));
for (int i = 0; i < store.Count; i++)
{
DataRow newRow = dtExcel.NewRow();
newRow[0] = store[i].item_id.ToString();
newRow[1] = store[i].product_name.ToString();
newRow[2] = store[i].loc_id.ToString();
newRow[3] = store[i].loc_stor_cse_cap.ToString();
newRow[4] = store[i].prepaid.ToString() == "0" ? "否" : "是";
newRow[5] = store[i].create_users.ToString();
newRow[6] = store[i].create_dtim.ToString();
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 DeleteIplasById()
{
string jsonStr = String.Empty;
_IlocMgr = new IlocMgr(mySqlConnectionString);
_IiplasMgr = new IplasMgr(mySqlConnectionString);
Iloc loc = new Iloc();
IplasQuery plasQuery = new IplasQuery();
IinvdQuery nvdQery = new IinvdQuery();
try
{
string str = Request.Params["loc_id"].ToString().ToUpper();//獲取類型
str = str.Substring(0, str.LastIndexOf(","));
int sum = 0;
string[] strs = str.Split(',');
for (int i = 0; i < strs.Length; i++)
{
nvdQery.loc_id = strs[i];
sum = sum + _IiplasMgr.GetIinvdItemId(nvdQery);
}
if (sum > 0)
{
jsonStr = "{success:true,sum:" + sum + "}";//大於0表示算選包含庫存量
}
else
{
int counts = 0;
for (int i = 0; i < strs.Length; i++)
{
plasQuery.loc_id = strs[i];
counts = counts + _IiplasMgr.DeleteIplasById(plasQuery);
}
if (counts > 0)
{
jsonStr = "{success:true,count:" + counts + "}";
}
else
{
jsonStr = "{success:false}";
}
}
}
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);
jsonStr = "{success:false}";
}
this.Response.Clear();
this.Response.Write(jsonStr);
this.Response.End();
return this.Response;
}
public int DeleteIplasById(IplasQuery plas)
{
try
{
return _IplasDao.DeleteIplasById(plas);
}
catch (Exception ex)
{
throw new Exception("IplasMgr-->DeleteIplasById-->" + ex.Message, ex);
}
}
public HttpResponseBase GetIPlasList()
{
string json = string.Empty;
IplasQuery iplas = new IplasQuery();
iplas.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
iplas.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量
//iplas.searchcontent = Request.Params["searchcontent"];
try
{
#region 修改之前
//if (!string.IsNullOrEmpty(Request.Params["searchcontent"]))
//{
// iplas.searchcontent = Request.Params["searchcontent"].ToString().ToUpper();//輸入的內容
// if (iplas.searchcontent.Length > 10)
// {
// _iinvd = new IinvdMgr(mySqlConnectionString);
// DataTable dt = new DataTable();
// dt = _iinvd.Getprodubybar(Request.Params["searchcontent"].ToString());
// if (dt.Rows.Count > 0)
// {
// iplas.searchcontent = dt.Rows[0]["item_id"].ToString();
// }
// }
//}
#endregion
#region 修改之後
string content = string.Empty;
if (!string.IsNullOrEmpty(Request.Params["search_type"]))
{
iplas.serch_type = int.Parse(Request.Params["search_type"]);
if (!string.IsNullOrEmpty(Request.Params["searchcontent"]) && Request.Params["searchcontent"].Trim().Length > 0)//有查詢內容就不管時間
{
switch (iplas.serch_type)
{
case 1:
case 2:
iplas.searchcontent = Request.Params["searchcontent"].Trim();
break;
case 3:
#region 之後的更改
content = Request.Params["searchcontent"].Replace('，', ',').Replace('|', ',').Replace(' ', ',');//.Replace(' ',',')
string[] list = content.Split(',');
string test = "^[0-9]*$";
int count = 0;//實現最後一個不加,
for (int i = 0; i < list.Length; i++)
{
if (!string.IsNullOrEmpty(list[i]))
{
if (Regex.IsMatch(list[i], test))
{
count = count + 1;
if (count == 1)
{
iplas.searchcontent = list[i];
}
else
{
iplas.searchcontent = iplas.searchcontent + "," + list[i];
}
}
else
{
iplas.searchcontent = iplas.searchcontent + list[i] + ",";
}
}
}
#endregion
break;
default:
break;
}
}
}
DateTime time;
if (DateTime.TryParse(Request.Params["starttime"].ToString(), out time))
{
iplas.starttime = DateTime.Parse(time.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (DateTime.TryParse(Request.Params["endtime"].ToString(), out time))
{
iplas.endtime = DateTime.Parse(time.ToString("yyyy-MM-dd HH:mm:ss"));
}
#endregion
//DateTime time;
//if (DateTime.TryParse(Request.Params["starttime"].ToString(), out time))
//{
// iplas.starttime = time;
//}
//if (DateTime.TryParse(Request.Params["endtime"].ToString(), out time))
//{
// iplas.endtime = time;
//}
List<IplasQuery> store = new List<IplasQuery>();
_IiplasMgr = new IplasMgr(mySqlConnectionString);
// _IiplasMgr.UpIplas(iplas);
int totalCount = 0;
store = _IiplasMgr.GetIplas(iplas, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
foreach (var item in store)
{
item.product_name += GetProductSpec(item.item_id.ToString());
}
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;
}
public HttpResponseBase SaveIinvd()
{
string json = "{success:false,message:'系統異常'}";
try
{
int temp = 0;
if (int.TryParse(Request.Params["prod_qty"], out temp))
{
if (temp > 0)
{
IinvdQuery iinvd = new IinvdQuery();
if (!string.IsNullOrEmpty(Request.Params["loc_id"]))
{
iinvd.plas_loc_id = Request.Params["loc_id"];
}
if (!string.IsNullOrEmpty(Request.Params["item_id"]))
{
if (int.TryParse(Request.Params["item_id"], out temp))
{
iinvd.item_id = uint.Parse(Request.Params["item_id"]);
}
}
#region 判斷是否指定主料位
IlocQuery ilocquery = new IlocQuery();
_IiplasMgr = new IplasMgr(mySqlConnectionString);
IplasQuery iplasquery = new IplasQuery();
iplasquery.item_id = iinvd.item_id;
IIlocImplMgr ilocMgr = new IlocMgr(mySqlConnectionString);
IplasDao iplasdao = new IplasDao(mySqlConnectionString);
int total = 0;
ilocquery.loc_id = iinvd.plas_loc_id;
ilocquery.lcat_id = "0";
ilocquery.lsta_id = "";
ilocquery.IsPage = false;
List<IlocQuery> listiloc = ilocMgr.GetIocList(ilocquery, out total);
if (listiloc.Count > 0)
{
string lcat_id = listiloc.Count == 0 ? "" : listiloc[0].lcat_id;
if (lcat_id == "S")
{
string item_id = iplasdao.Getlocid(ilocquery.loc_id);
if (item_id == "")
{
Iplas iplas = new Iplas();
if (int.TryParse(Request.Params["item_id"], out temp))
{
iplas.item_id = uint.Parse(Request.Params["item_id"]);
if (_IiplasMgr.IsTrue(iplas) == "false")
{
json = "{success:false,message:'不存在該商品編號'}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
if (_IiplasMgr.GetIplasid(iplasquery) > 0)
{
json = "{success:false,message:'此商品主料位非該料位'}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
Iloc iloc = new Iloc();
iloc.loc_id = iinvd.plas_loc_id;
if (_IiplasMgr.GetLocCount(iloc) <= 0)
{
json = "{success:false,message:'該料位已鎖定或被指派'}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
iplas.loc_id = iloc.loc_id;
iplas.loc_stor_cse_cap = 100;
iplas.create_user = (Session["caller"] as Caller).user_id;
iplas.create_dtim = DateTime.Now;
iplas.change_user = (Session["caller"] as Caller).user_id;
iplas.change_dtim = DateTime.Now;
_IiplasMgr.InsertIplas(iplas);
}
}
}
}
#endregion
iinvd.create_user = (Session["caller"] as Caller).user_id;
iinvd.create_dtim = DateTime.Now;
iinvd.change_user = iinvd.create_user;
iinvd.change_dtim = iinvd.create_dtim;
iinvd.ista_id = "A";
if (!string.IsNullOrEmpty(Request.Params["loc_id"]))
{
iinvd.plas_loc_id = Request.Params["loc_id"];
}
int change_prod_qty = int.Parse(Request.Params["prod_qty"]);
iinvd.prod_qty = change_prod_qty;
if (!string.IsNullOrEmpty(Request.Params["st_qty"]))
{
if (int.TryParse(Request.Params["st_qty"], out temp))
{
iinvd.st_qty = int.Parse(Request.Params["st_qty"]);
}
}
if (!string.IsNullOrEmpty(Request.Params["item_id"]))
{
if (int.TryParse(Request.Params["item_id"], out temp))
{
iinvd.item_id = uint.Parse(Request.Params["item_id"]);
}
}
DateTime date = DateTime.Now;
if (DateTime.TryParse(Request.Params["datetimepicker1"], out date))
{
iinvd.made_date = date;
}
else
{
iinvd.made_date = DateTime.Now;
}
_iinvd = new IinvdMgr(mySqlConnectionString);
if (Request.Params["pwy_dte_ctl"] == "Y")
{
iinvd.pwy_dte_ctl = "Y";
IProductExtImplMgr productExt = new ProductExtMgr(mySqlConnectionString);
int Cde_dt_incr = productExt.GetCde_dt_incr((int)iinvd.item_id);
iinvd.cde_dt = date.AddDays(Cde_dt_incr);
}
else
{
iinvd.cde_dt = DateTime.Now;
}
iinvd.prod_qtys = _iinvd.GetProd_qty(Convert.ToInt32(iinvd.item_id), iinvd.plas_loc_id, "", iinvd.row_id.ToString());
IialgQuery iialg = new IialgQuery();
iialg.cde_dt = iinvd.cde_dt;
int prod_qty = 0;// _iinvd.GetProd_qty((int)iinvd.item_id, iinvd.plas_loc_id, "", "");
int row = _iinvd.GetIinvdCount(iinvd);
if (row > 1)
{
prod_qty = row - 1;
json = "{success:true}";
}
else
{
if (_iinvd.Insert(iinvd) == 1)
{
json = "{success:true}";
}
}
iialg.qty_o = prod_qty;
iialg.loc_id = iinvd.plas_loc_id;
iialg.item_id = iinvd.item_id;
iialg.iarc_id = "循環盤點";
iialg.adj_qty = change_prod_qty;
iialg.create_dtim = DateTime.Now;
iialg.create_user = iinvd.create_user;
iialg.type = 2;
iialg.doc_no = "C" + DateTime.Now.ToString("yyyyMMddHHmmss");
iialg.made_dt = iinvd.made_date;
iialg.cde_dt = iinvd.cde_dt;
_iialgMgr = new IialgMgr(mySqlConnectionString);
_iialgMgr.insertiialg(iialg);
IstockChangeQuery istock = new IstockChangeQuery();
istock.sc_trans_id = iialg.doc_no;
istock.item_id = iinvd.item_id;
istock.sc_istock_why = 2;
istock.sc_trans_type = 2;
istock.sc_num_old = iinvd.prod_qtys;
istock.sc_num_chg = iialg.adj_qty;
istock.sc_num_new = _iinvd.GetProd_qty((int)iinvd.item_id, iinvd.plas_loc_id,"N","");
istock.sc_time = iinvd.create_dtim;
istock.sc_user = iinvd.create_user;
istock.sc_note = "循環盤點";
IstockChangeMgr istockMgr = new IstockChangeMgr(mySqlConnectionString);
istockMgr.insert(istock);
}
else
{
json = "{success:false,message:'庫存不能小於1'}";
}
}
else
{
json = "{success:false,message:'庫存請輸入數字'}";
}
}
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 List<IplasQuery> GetIplasExportList(IplasQuery iplas)
{
try
{
return _IplasDao.GetIplasExportList(iplas);
}
catch (Exception ex)
{
throw new Exception("IplasMgr-->GetIplasExportList-->" + ex.Message, ex);
}
}
public DataTable ExportMessage(IplasQuery m)
{
try
{
return _IplasDao.ExportMessage(m);
}
catch (Exception ex)
{
throw new Exception("IplasMgr-->ExportMessage-->" + ex.Message, ex);
}
}
public int GetIplasid(IplasQuery plas)
{
try
{
return _IplasDao.GetIplasid(plas);
}
catch (Exception ex)
{
throw new Exception("IplasMgr-->GetIplasid-->" + ex.Message, ex);
}
}
public DataTable ExportMessage(IplasQuery m)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendLine(@"SELECT ip.loc_id,il.lcat_id,ip.item_id,sum(ii.prod_qty) as 'prod_qtys',CONCAT(v.brand_name,'-',p.product_name) AS 'product_name',concat(IFNULL(ps1.spec_name,''),IFNULL(ps2.spec_name,'')) as prod_sz,iu.upc_id,ii.cde_dt ");
sql.AppendLine(@"from iplas ip LEFT JOIN (SELECT upc_id,item_id from iupc GROUP BY item_id) iu ON ip.item_id=iu.item_id ");
sql.AppendLine(@"LEFT JOIN product_item pi on pi.item_id=ip.item_id ");
sql.AppendLine(@"LEFT JOIN product_spec ps1 ON pi.spec_id_1 = ps1.spec_id ");
sql.AppendLine(@"LEFT JOIN product_spec ps2 ON pi.spec_id_2 = ps2.spec_id ");
sql.AppendLine(@"LEFT JOIN iinvd ii ON ip.item_id = ii.item_id ");
sql.AppendLine(@"LEFT JOIN iloc il ON ip.loc_id= il.loc_id ");
sql.AppendLine(@"LEFT JOIN product p ON pi.product_id =p.product_id ");
sql.AppendLine(@"LEFT JOIN vendor_brand v ON p.brand_id=v.brand_id ");
sql.AppendLine(@"WHERE upc_id is NULL ");
if (m.startloc != "")
{
sql.AppendFormat(" and ip.loc_id>'{0}' ", m.startloc);
}
if (m.endloc != "")
{
sql.AppendFormat(" and ip.loc_id<'{0}' ", m.endloc);
}
sql.Append(" GROUP BY ip.item_id ORDER BY ip.loc_id; ");
return _dbAccess.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("IplasDao-->ExportMessage-->" + ex.Message + sql.ToString(), ex);
}
}
public HttpResponseBase GetIPlasEdit()
{
string json = string.Empty;
IplasQuery iplas = new IplasQuery();
Iloc iloc = new Iloc();
Iupc upc = new Iupc();
_IiplasMgr = new IplasMgr(mySqlConnectionString);
_IlocMgr = new IlocMgr(mySqlConnectionString);
_ipalet = new PalletMoveMgr(mySqlConnectionString);
try
{
if (string.IsNullOrEmpty(Request.Params["plas_id"]))//首先考慮添加情況
{
string itemid = Request.Params["item_id"];
DataTable dt = _ipalet.GetProdInfo(itemid);
if (dt.Rows.Count > 0)
{
iplas.item_id = uint.Parse(dt.Rows[0]["item_id"].ToString());
}
else
{
iplas.item_id = 0;
}
if (_IiplasMgr.IsTrue(iplas) == "false")
{
json = "{success:false,msg:\"" + "商品編號不存在" + "\"}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
if (_IiplasMgr.GetIplasid(iplas) > 0)
{
json = "{success:false,msg:\"" + "此商品已存在主料位" + "\"}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
iloc.loc_id = Request.Params["loc_id"].ToString().ToUpper();
if (_IiplasMgr.GetLocCount(iloc) <= 0)
{
json = "{success:false,msg:\"" + "非主料位或主料位已鎖定" + "\"}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
iplas.loc_id = Request.Params["loc_id"].ToString().ToUpper();
if (_IiplasMgr.GetIplasCount(iplas).Count > 0)//主料位重複
{
json = "{success:false,msg:\"" + "主料位重複" + "\"}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
iplas.loc_stor_cse_cap = int.Parse(Request.Params["loc_stor_cse_cap"]);
iplas.create_user = (Session["caller"] as Caller).user_id;
iplas.create_dtim = DateTime.Now;
iplas.change_user = (Session["caller"] as Caller).user_id;
iplas.change_dtim = DateTime.Now;
_IiplasMgr.InsertIplas(iplas);//判斷主料位和商品編號沒有問題,插入Iplas表的同時.操作iloc表,設置其為已指派料位
json = "{success:true}";
}
else//編輯
{
iplas.plas_id = int.Parse(Request.Params["plas_id"]);
upc.upc_id = Request.Params["upcid"];
iplas.item_id = uint.Parse(Request.Params["item_id"]);
iplas.loc_id = Request.Params["loc_id"].ToString().ToUpper();
iloc.loc_id = iplas.loc_id;
if (_IiplasMgr.GetLocCount(iloc) <= 0)
{
json = "{success:false,msg:\"" + "非主料位或主料位已鎖定" + "\"}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
if (_IiplasMgr.GetIplasCount(iplas).Count > 0)//主料位重複
{
json = "{success:false,msg:\"" + "該主料位不可用!" + "\"}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
iplas.loc_stor_cse_cap = int.Parse(Request.Params["loc_stor_cse_cap"]);
iplas.change_user = (Session["caller"] as Caller).user_id;
iplas.change_dtim = DateTime.Now;
_IiplasMgr.UpIplas(iplas);
json = "{success:true}";
}
}
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 List<IplasQuery> GetIplasExportList(IplasQuery iplas)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendLine(@"select mu.user_username as create_users, CONCAT(vb.brand_name,'-',p.product_name) as product_name,plas_id,dc_id,whse_id,loc_id,change_dtim,change_user,lcus_id,ip.item_id,p.prepaid,");
sql.AppendLine(@"prdd_id,ip.create_user,ip.create_dtim,loc_stor_cse_cap from iplas ip ");
sql.AppendLine(@"left JOIN product_item pi ON ip.item_id=pi.item_id ");
sql.AppendLine(@"left JOIN manage_user mu on mu.user_id=ip.create_user ");
sql.AppendLine(@"left JOIN product p ON p.product_id=pi.product_id ");
sql.AppendLine(@"left join vendor_brand vb on p.brand_id=vb.brand_id where 1=1 ");
if (iplas.serch_type != 0)
{
if (!string.IsNullOrEmpty(iplas.searchcontent))
{
switch (iplas.serch_type)
{
case 1:
sql.AppendFormat(" and ip.loc_id in (select loc_id from iloc where loc_id='{0}' or hash_loc_id='{0}' ) ", iplas.searchcontent);
break;
case 2:
sql.AppendFormat(" and ip.item_id=(select item_id from iupc iu where iu.upc_id ='{0}') ", iplas.searchcontent);
break;
case 3:
sql.AppendFormat(" and ip.item_id in ({0}) ", iplas.searchcontent);
break;
default:
break;
}
}
}
DateTime dt = DateTime.Parse("1970-01-02 08:00:00");
if (!string.IsNullOrEmpty(iplas.starttime.ToString()) && dt < iplas.starttime)
{
sql.AppendFormat(" and ip.create_dtim>='{0}' ",iplas.starttime.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(iplas.endtime.ToString()) && dt < iplas.endtime)
{
sql.AppendFormat(" and ip.create_dtim<='{0}' ", iplas.endtime.ToString("yyyy-MM-dd HH:mm:ss"));
}
//if (!string.IsNullOrEmpty(iplas.searchcontent))
//{
// sql.AppendFormat(" and (ip.item_id like '%{0}%' or ip.loc_id like '%{0}%')", iplas.searchcontent);
//}
//DateTime dt = DateTime.MinValue;
//if (!string.IsNullOrEmpty(iplas.starttime.ToString()) && iplas.starttime>dt)
//{
// sql.AppendFormat(" and ip.create_dtim > '{0}' ", iplas.starttime);
//}
//if (!string.IsNullOrEmpty(iplas.endtime.ToString()) && iplas.endtime > dt)
//{
// sql.AppendFormat(" and ip.create_dtim < '{0}' ", iplas.endtime);
//}
return _dbAccess.getDataTableForObj<IplasQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("IplasDao-->GetIplasExportList-->" + ex.Message + sql.ToString(), ex);
}
}
public List<IplasQuery> Export(IplasQuery m)
{
try
{
return _IplasDao.Export(m);
}
catch (Exception ex)
{
throw new Exception("IplasMgr-->Export-->" + ex.Message, ex);
}
}
