/// <summary>
/// 生成盤點工作
/// </summary>
/// <returns></returns>
public HttpResponseBase GetCountBook2()
{
// DataSet.Tables["XX"].Columns["xx"].ColumnName = "NewColumnName";
string json = string.Empty;
IinvdQuery m = new IinvdQuery();
CbjobMaster cm = new CbjobMaster();
CbjobDetail cd = new CbjobDetail();
IinvdMgr iinvdMgr = new IinvdMgr(mySqlConnectionString);
_IiupcMgr = new IupcMgr(mySqlConnectionString);
string cbjob_id = "PC";// +DateTime.Now.ToString("yyyyMMddHHmmss");
try
{
#region 條件
if (!string.IsNullOrEmpty(Request.Params["startIloc"].Trim()))
{//料位開始
m.startIloc = Request.Params["startIloc"].Trim().ToUpper();
}
int length = m.startIloc.Length;
switch (length)
{
case 0:
cbjob_id = cbjob_id + "AA";
break;
case 1:
cbjob_id = cbjob_id+ m.startIloc+"A";
break;
default:
cbjob_id = cbjob_id + m.startIloc.Substring(0, 2);
break;
}
if (!string.IsNullOrEmpty(Request.Params["endIloc"].Trim()))
{
m.endIloc = Request.Params["endIloc"].Trim() + "Z";
m.endIloc = m.endIloc.ToUpper();
}
length = m.endIloc.Length;
switch (length)
{
case 0:
cbjob_id = cbjob_id + "ZZ";
break;
case 1:
cbjob_id = cbjob_id +m.endIloc +"Z";
break;
default:
cbjob_id = cbjob_id + m.endIloc.Substring(0, 2);
break;
}
if (!string.IsNullOrEmpty(Request.Params["level"]))//層數
{//層數選擇
m.lot_no = Request.Params["level"].ToString().ToUpper();
}
if (!string.IsNullOrEmpty(Request.Params["sort"]))//排序
{//排序方式
m.Sort = Request.Params["sort"];
if (m.Sort == "0" && !string.IsNullOrEmpty(Request.Params["firstsd"]))
{
m.Firstsd = Request.Params["firstsd"];
}
}
if (!string.IsNullOrEmpty(m.Firstsd))
{
if (m.Firstsd == "0")
{
cbjob_id += "S";//單
}
else
{
cbjob_id += "M";//雙
}
}
else
{
cbjob_id += "Z";//不分
}
cbjob_id += DateTime.Now.ToString("yyyyMMddHHmmss");
if (!string.IsNullOrEmpty(Request.Params["vender"]))
{//vender
m.vender = Request.Params["vender"].ToString().ToUpper();
}
if (!string.IsNullOrEmpty(Request.Params["prepaid"]))
{
m.prepaid = int.Parse(Request.Params["prepaid"]);
}
#endregion
DataTable dt = iinvdMgr.getVentory(m);
if (dt.Rows.Count > 0)
{
#region 往cbjob_master裡面插入一條數據
StringBuilder sb = new StringBuilder();
_cbjobMgr = new CbjobDetailMgr(mySqlConnectionString);
_cbMasterMgr = new CbjobMasterMgr(mySqlConnectionString);
cm.cbjob_id = cbjob_id;
cm.create_datetime = DateTime.Now;
cm.sta_id = "CNT";
cm.create_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id;
sb.Append(_cbMasterMgr.Insertsql(cm));
#endregion
#region 修改iinvd數據,往cbjob_detail循環插入數據
int a = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (!string.IsNullOrEmpty(dt.Rows[i]["row_id"].ToString()))
{
cd.cb_jobid = cm.cbjob_id;
cd.cb_newid = a + 1;
cd.chang_user = cm.create_user;
cd.change_datetime = DateTime.Now;
cd.create_datetime = DateTime.Now;
cd.create_user = cm.create_user;
cd.status = 1;
cd.iinvd_id = int.Parse(dt.Rows[i]["row_id"].ToString());
sb.AppendFormat("set sql_safe_updates = 0; UPDATE iinvd set st_qty=prod_qty where row_id='{0}'; set sql_safe_updates = 1;", cd.iinvd_id);
sb.Append(_cbjobMgr.insertsql(cd));
a++;
}
}
#endregion
_cbjobMgr.InsertSql(sb.ToString());
json = "{success:true,msg:" + 1 + "}";
}
else
{
json = "{success:true,msg:" + 2 + "}";
#region 應用
//Response.Clear();
//this.Response.Write("沒有數據<br/>");
#endregion
}
}
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);
this.Response.End();
return this.Response;
}
public HttpResponseBase GetCountBook()
{
// DataSet.Tables["XX"].Columns["xx"].ColumnName = "NewColumnName";
string json = string.Empty;
IinvdQuery m = new IinvdQuery();
CbjobMaster cm = new CbjobMaster();
CbjobDetail cd = new CbjobDetail();
IinvdMgr iinvdMgr = new IinvdMgr(mySqlConnectionString);
_IiupcMgr = new IupcMgr(mySqlConnectionString);
string cbjob_id = "PC" + DateTime.Now.ToString("yyyyMMddHHmmss");
try
{
#region 條件
if (!string.IsNullOrEmpty(Request.Params["startIloc"]))
{//料位開始
m.startIloc = Request.Params["startIloc"].ToUpper();
}
if (!string.IsNullOrEmpty(Request.Params["endIloc"]))
{
m.endIloc = Request.Params["endIloc"] + "Z";
m.endIloc = m.endIloc.ToUpper();
}
if (!string.IsNullOrEmpty(Request.Params["level"]))//層數
{//層數選擇
m.lot_no = Request.Params["level"].ToString().ToUpper();
}
if (!string.IsNullOrEmpty(Request.Params["sort"]))//排序
{//排序方式
m.Sort = Request.Params["sort"];
if (m.Sort == "0" && !string.IsNullOrEmpty(Request.Params["firstsd"]))
{
m.Firstsd = Request.Params["firstsd"];
}
}
if (!string.IsNullOrEmpty(Request.Params["vender"]))
{//vender
m.vender = Request.Params["vender"].ToString().ToUpper();
}
if (!string.IsNullOrEmpty(Request.Params["prepaid"]))
{
m.prepaid = int.Parse(Request.Params["prepaid"]);
}
#endregion
DataTable dt = iinvdMgr.getproduct(m);
#region 列名
DataTable dtCountBook = new DataTable();
dtCountBook.Columns.Add("編號", typeof(String));
dtCountBook.Columns.Add("料位", typeof(String));
dtCountBook.Columns.Add("狀態", typeof(String));
dtCountBook.Columns.Add("商品細項編號", typeof(String));
dtCountBook.Columns.Add("庫存", typeof(String));
dtCountBook.Columns.Add("成本", typeof(String));
dtCountBook.Columns.Add("商品名稱", typeof(String));
dtCountBook.Columns.Add("商品規格", typeof(String));
dtCountBook.Columns.Add("是否買斷", typeof(String));
dtCountBook.Columns.Add("製造日期", typeof(String));
dtCountBook.Columns.Add("保存期限", typeof(String));
dtCountBook.Columns.Add("有效日期", typeof(String));
dtCountBook.Columns.Add("是否有效期控制", typeof(String));
dtCountBook.Columns.Add("是否即期", typeof(String));
dtCountBook.Columns.Add("是否過期", typeof(String));
dtCountBook.Columns.Add("允出天數", typeof(String));
dtCountBook.Columns.Add("允收天數", typeof(String));
dtCountBook.Columns.Add("國際條碼", typeof(String));
dtCountBook.Columns.Add("最新店內碼", typeof(String));
#endregion
if (dt.Rows.Count > 0)
{
#region 往cbjob_master裡面插入一條數據
StringBuilder sb = new StringBuilder();
_cbjobMgr = new CbjobDetailMgr(mySqlConnectionString);
_cbMasterMgr = new CbjobMasterMgr(mySqlConnectionString);
cm.cbjob_id = cbjob_id;
cm.create_datetime = DateTime.Now;
cm.sta_id = "CNT";
cm.create_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id;
sb.Append(_cbMasterMgr.Insertsql(cm));
#endregion
#region 修改iinvd數據,往cbjob_detail循環插入數據
int a = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (!string.IsNullOrEmpty(dt.Rows[i]["row_id"].ToString()))
{
cd.cb_jobid = cm.cbjob_id;
cd.cb_newid = a + 1;
cd.chang_user = cm.create_user;
cd.change_datetime = DateTime.Now;
cd.create_datetime = DateTime.Now;
cd.create_user = cm.create_user;
cd.status = 1;
cd.iinvd_id = int.Parse(dt.Rows[i]["row_id"].ToString());
sb.AppendFormat("set sql_safe_updates = 0; UPDATE iinvd set st_qty=prod_qty where row_id='{0}'; set sql_safe_updates = 1;", cd.iinvd_id);
sb.Append(_cbjobMgr.insertsql(cd));
a++;
}
}
_cbjobMgr.InsertSql(sb.ToString());
#endregion
m.cb_jobid = cm.cbjob_id;//賦值給m
//dt = iinvdMgr.CountBook(m);
int bh = 1;
string loc = "";
foreach (DataRow item2 in dt.Rows)
{
if (item2["loc_id"].ToString() != loc)
{
if (!string.IsNullOrEmpty(item2["row_id"].ToString()))
{
#region 讀取iinvd裡面的數據
m.loc_id = item2["loc_id"].ToString();
DataTable Invdt = iinvdMgr.GetIinvdCountBook(m);
foreach (DataRow item in Invdt.Rows)
{
DataRow dr = dtCountBook.NewRow();
dr[0] = bh;
dr[1] = item["loc_id"];
dr[2] = item["lsta_id"];
if (!string.IsNullOrEmpty(item["item_id"].ToString()))
{
dr[3] = item["item_id"];
}
if (!string.IsNullOrEmpty(item["prod_qty"].ToString()))
{
dr[4] = item["prod_qty"];
}
else
{
dr[4] = "0";
}
if (!string.IsNullOrEmpty(item["product_id"].ToString()))
{
dr[5] = iinvdMgr.Getcost(item["product_id"].ToString());
}
dr[6] = item["product_name"];
dr[7] = GetProductSpec(item["item_id"].ToString());
dr[8] = item["prepaid"].ToString() == "0" ? "否" : "是";
DateTime md;
if (DateTime.TryParse(item["made_date"].ToString(), out md))
{
dr[9] = DateTime.Parse(item["made_date"].ToString()).ToString("yyyy/MM/dd");
}
dr[10] = item["cde_dt_incr"];
DateTime cdate;
if (DateTime.TryParse(item["cde_dt"].ToString(), out cdate))
{
dr[11] = cdate.ToString("yyyy/MM/dd");
}
int shp = 0;
if (item["pwy_dte_ctl"].ToString() == "Y" && Int32.TryParse(item["cde_dt_shp"].ToString(), out shp))//表示是有效期控管的商品
{
dr[12] = "Y";
if (cdate.AddDays(-shp) <= DateTime.Now && cdate >= DateTime.Now)
{
dr[13] = "Y";
}
else
{
dr[13] = "N";
}
if (cdate < DateTime.Now)
{
dr[14] = "Y";
}
else
{
dr[14] = "N";
}
dr[15] = item["cde_dt_shp"];//允出天數
dr[16] = item["cde_dt_var"];
}
else if (item["pwy_dte_ctl"].ToString() == "N")
{
dr[12] = "N";
dr[13] = "";
dr[14] = "";
dr[15] = 0;//允出天數
dr[16] = 0;
}
else
{
dr[12] = "";
dr[13] = "";
dr[14] = "";
dr[15] = 0;//允出天數
dr[16] = 0;
}
dr[17] = " " + _IiupcMgr.Getupc(item["item_id"].ToString(), "1");
dr[18] = " " + _IiupcMgr.Getupc(item["item_id"].ToString(), "2");
dtCountBook.Rows.Add(dr);
bh++;
}
#endregion
}
else
{
#region iinvd沒有的料位信息從iplas表查出
DataRow dr = dtCountBook.NewRow();
m.loc_id = item2["loc_id"].ToString();
DataTable dt1 = iinvdMgr.GetIplasCountBook(m);
foreach (DataRow item1 in dt1.Rows)
{
dr[0] = bh;
dr[1] = item2["loc_id"];
dr[2] = item1["lsta_id"];
if (!string.IsNullOrEmpty(item1["item_id"].ToString()))
{
dr[3] = item1["item_id"];
}
if (!string.IsNullOrEmpty(item1["prod_qty"].ToString()))
{
dr[4] = item1["prod_qty"];
}
else
{
dr[4] = "0";
}
if (!string.IsNullOrEmpty(item1["product_id"].ToString()))
{
dr[5] = iinvdMgr.Getcost(item1["product_id"].ToString());
}
else
{
dr[5] = "0";
}
dr[6] = item1["product_name"];
dr[7] = GetProductSpec(item1["item_id"].ToString());
dr[8] = item1["prepaid"].ToString() == "0" ? "否" : "是";
dr[9] = "";
dr[10] = item1["cde_dt_incr"];
dr[11] = "";
if (item1["pwy_dte_ctl"].ToString() == "Y")//表示是有效期控管的商品
{
dr[12] = "Y";
dr[13] = "N";
dr[14] = "N";
dr[15] = item1["cde_dt_shp"];//允出天數
dr[16] = item1["cde_dt_var"];
}
else if (item1["pwy_dte_ctl"].ToString() == "N")
{
dr[12] = "N";
dr[13] = "";
dr[14] = "";
dr[15] = 0;//允出天數
dr[16] = 0;
}
else
{
dr[12] = "";
dr[13] = "";
dr[14] = "";
dr[15] = 0;//允出天數
dr[16] = 0;
}
dr[17] = " " + _IiupcMgr.Getupc(item1["item_id"].ToString(), "1");
dr[18] = " " + _IiupcMgr.Getupc(item1["item_id"].ToString(), "2");
dtCountBook.Rows.Add(dr);
bh++;
}
#endregion
}
loc = item2["loc_id"].ToString();
}
}
string fileName = "盤點簿" + cm.cbjob_id.Substring(2, 14) + ".xls";
String str = "盤點簿報表-" + cm.cbjob_id;
MemoryStream ms = ExcelHelperXhf.ExportDT(dtCountBook, str);
Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.BinaryWrite(ms.ToArray());
}
else
{
Response.Clear();
this.Response.Write("沒有數據<br/>");
}
}
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);
}
return this.Response;
}
