protected void btnBatch_Click(object sender, EventArgs e)
{
if (txtAddMo.Text == "")
{
return;
}
user_id = getUserName();
string mo_id = txtAddMo.Text.ToUpper();
string strSql = "";
string result = "";
//strSql = "Delect From mo_BatchPrint Where within_code='" + within_code + "' AND user_id='" + user_id + "'";
strSql = "Select mo_id From mo_BatchPrint Where within_code='" + within_code + "' AND user_id='" + user_id + "' AND mo_id='" + mo_id + "'";
DataTable tbMoFind = sh.ExecuteSqlReturnDataTable(strSql);
if (tbMoFind.Rows.Count == 0)
{
strSql = string.Format(@"INSERT INTO mo_BatchPrint(
within_code,mo_id,user_id,crusr,crtim)
VALUES ('{0}','{1}','{2}','{3}',GETDATE()) "
, within_code, mo_id, user_id, user_id);
result = sh.ExecuteSqlUpdate(strSql);
if (result != "")
{
StrHlp.WebMessageBox(this.Page, result);
}
else
{
txtAddMo.Text = "";
txtAddMo.Focus();
}
}
LoadBatchMo();
}
protected void InitControler()
{
dateStart.Value = System.DateTime.Now.AddDays(-1).ToString("yyyy/MM/dd");
dateEnd.Value = dateStart.Value;
string strSql = "";
strSql = "select id from " + remote_db + "cd_season order by id ";
DataTable tbSeason = sh.ExecuteSqlReturnDataTable(strSql);
tbSeason.Rows.Add();
dlSeason.DataSource = tbSeason.DefaultView;
dlSeason.DataTextField = "id";
dlSeason.DataValueField = "id";
dlSeason.Text = "";
dlSeason.DataBind();
strSql = "select mo_group from bs_group order by mo_group ";
DataTable tbMo_group = sh.ExecuteSqlReturnDataTable(strSql);
tbMo_group.Rows.Add();
dlMo_group.DataSource = tbMo_group.DefaultView;
dlMo_group.DataTextField = "mo_group";
dlMo_group.DataValueField = "mo_group";
dlMo_group.Text = "";
dlMo_group.DataBind();
}
//查找記錄
protected void LoadData()
{
int select_flag = 0;
int id = 0;
string approve_flag = "";
select_flag = dlApproveColor.SelectedIndex;
if (select_flag == 1)
{
approve_flag = "0";
}
else
if (select_flag == 2)
{
approve_flag = "1";
}
string strSql = "Select id,prd_mo,prd_rmk,crusr,Convert(Varchar(20),crtim,120) As crtim,approve_flag,approve_usr,Convert(Varchar(20),approve_tim,120) As approve_tim" +
" From mo_approvecolor Where id>='" + id + "'";
if (approve_flag != "")
{
strSql = strSql + " AND approve_flag='" + approve_flag + "'";
}
if (txtMo.Text != "")
{
strSql = strSql + " AND prd_mo='" + txtMo.Text + "'";
}
DataTable tbMoFind = sh.ExecuteSqlReturnDataTable(strSql);
//if (tbMoFind.Rows.Count == 0)
// tbMoFind.Rows.Add();
gvMo.DataSource = tbMoFind.DefaultView;
gvMo.DataBind();
}
protected void initControls()
{
txtDoc_date1.Text = System.DateTime.Now.ToString("yyyy/MM/dd");
txtDoc_date2.Text = txtDoc_date1.Text;
txtDep.Text = "501";
string strSql = "select id AS vend_id from " + remote_db +
"it_vendor Where within_code='" + within_code + "' AND id>='CL' AND id<='CLZZZZZZ'";
DataTable tbVend_id = sh.ExecuteSqlReturnDataTable(strSql);
tbVend_id.Rows.Add();
tbVend_id.DefaultView.Sort = "vend_id";
dlVend_id.DataSource = tbVend_id.DefaultView;
dlVend_id.DataTextField = "vend_id";
dlVend_id.DataValueField = "vend_id";
dlVend_id.DataBind();
DataTable tbUnit = sh.ExecuteSqlReturnDataTable(strSql);
dlSetPrice.Items.Add("全部");
dlSetPrice.Items.Add("已設定單價");
dlSetPrice.Items.Add("未設定單價");
dlSetPrice.SelectedIndex = 0;
}
protected DataTable CehckPrdItem(string Prd_item, string Mat_item, string Dep_id)
{
string strSql = "";
strSql = "Select Prd_item From bs_mat_rate Where Prd_item='" + Prd_item + "' And Mat_item='" + Mat_item + "' And Dep_id='" + Dep_id + "'";
DataTable dt = sh.ExecuteSqlReturnDataTable(strSql);
return(dt);
}
public DataTable getCpFlag()
{
SQLHelp sh = new SQLHelp();
string strSql = "Select flag_id,flag_desc From bs_flag_desc Where doc_type='" + "CP" + "'";
DataTable dt = sh.ExecuteSqlReturnDataTable(strSql);
return(dt);
}
private int findOorderTrace(string within_code, string mo_id)
{
SQLHelp sh = new SQLHelp();
string strSql = "Select mo_id From so_order_trace Where within_code='" + within_code + "' AND mo_id='" + mo_id + "'";
DataTable dt = sh.ExecuteSqlReturnDataTable(strSql);
int result = dt.Rows.Count;
return(result);
}
private int findOorderInvoice(string invoice_id)
{
SQLHelp sh = new SQLHelp();
string strSql = "Select invoice_id From so_ordertest_invoice Where invoice_id='" + invoice_id + "'";
DataTable dt = sh.ExecuteSqlReturnDataTable(strSql);
int result = dt.Rows.Count;
return(result);
}
//檢查是否存在來料申請記錄
protected string chkRequestNoValid(string RequestNo)
{
string result = "";
string strSql = "Select id From st_jx_store_request Where id='" + RequestNo + "'";
DataTable dt = sh.ExecuteSqlReturnDataTable(strSql);
if (dt.Rows.Count == 0)
{
result = "來料申請單號不存在!";
}
return(result);
}
protected void InitControler()
{
string strSql;
strSql = "select group_id,group_desc from bs_mo_group order by group_id ";
DataTable tbMo_group = sh.ExecuteSqlReturnDataTable(strSql);
dlMo_group.DataSource = tbMo_group.DefaultView;
dlMo_group.DataTextField = "group_desc";
dlMo_group.DataValueField = "group_id";
dlMo_group.DataBind();
crDateStart.Value = System.DateTime.Now.ToString("yyyy/MM/dd");
crDateEnd.Value = crDateStart.Value;
dlShowPeriod.SelectedIndex = 1;
lblShowPeriodInfo.Text = "(貨未到包裝部。若香港有存貨，N/W/V單若已入成品倉，都當作已完成；R單已交包裝部(因為不用回港))";
}
public DataTable getDep()
{
SQLHelp sh = new SQLHelp();
string strSql = "Select dep_id,Rtrim(dep_id)+'--'+Rtrim(dep_cdesc) AS dep_cdesc From bs_dep Order By dep_id";
DataTable dt = sh.ExecuteSqlReturnDataTable(strSql);
dt.Rows.Add();
dt.DefaultView.Sort = "dep_id";
return(dt);
}
public DataTable getWork_type()
{
SQLHelp sh = new SQLHelp();
string strSql = "Select work_type_id,Rtrim(work_type_id)+'--'+Rtrim(work_type_desc) AS work_type_desc" +
" From dgcf_pad.dbo.work_type Order By work_type_id";
DataTable dt = sh.ExecuteSqlReturnDataTable(strSql);
//dt.Rows.Add();
//dt.DefaultView.Sort = "work_type_id";
return(dt);
}
public DataTable getJxDep()
{
SQLHelp sh = new SQLHelp();
//string remote_db = "dgerp2.cferp.dbo.";
//string strSql = "Select id,id+'--'+name AS name From "+remote_db+ "cd_department Where id='J03' OR id='J82' OR id='J86' Order By id";
string strSql = "Select dep_id AS id,Rtrim(dep_id)+'--'+Rtrim(dep_cdesc) AS name From bs_dep Where dep_group='JX' Order By dep_id";
DataTable dt = sh.ExecuteSqlReturnDataTable(strSql);
dt.Rows.Add();
dt.DefaultView.Sort = "id";
return(dt);
}
//產生ID編號
protected int GenID(string Loc_no, string Flag_id, string Transfer_date)
{
int Seq;
//產生自動單號
string ID1, ID2;
ID1 = Loc_no + Flag_id;
ID2 = ID1 + "-" + Transfer_date.Substring(2, 2) + Transfer_date.Substring(5, 2) + Transfer_date.Substring(8, 2) + "9999";
string strSql = "Select MAX(ID) AS max_id From st_jx_store_transfer Where ID>='" + ID1 + "' And ID<='" + ID2 + "'";
DataTable dt = sh.ExecuteSqlReturnDataTable(strSql);
if (dt.Rows.Count > 0)
{
Seq = dt.Rows[0]["max_id"].ToString() != "" ? (Convert.ToInt32(dt.Rows[0]["max_id"].ToString().Substring(12, 4)) + 1) : 1;
}
else
{
Seq = 1;
}
return(Seq);
}
//protected void btnExpData_Click(object sender, EventArgs e)
//{
// Response.Redirect("Sa_Mo_PeriodOver5Day.aspx");
//}
protected void LoadBatchMo()
{
string strSql = "Select id,mo_id,goods_id From mo_periodover5day_no";
DataTable tbMoFind = sh.ExecuteSqlReturnDataTable(strSql);
if (tbMoFind.Rows.Count == 0)
{
tbMoFind.Rows.Add();
}
gvBatchMo.DataSource = tbMoFind.DefaultView;
gvBatchMo.DataBind();
}
protected void InitValue()
{
dateStart.Value = System.DateTime.Now.ToString("yyyy/MM/dd HH:mm");
dateEnd.Value = dateStart.Value;
string strSql = "";
strSql = "select convert(varchar(1),flag_id) AS flag_id,flag_desc from bs_flag_desc Where doc_type='AL' AND flag0='Y' order by flag_id ";
DataTable tbA1 = sh.ExecuteSqlReturnDataTable(strSql);
dlWaitSample.DataSource = tbA1.DefaultView;
dlWaitSample.DataTextField = "flag_desc";
dlWaitSample.DataValueField = "flag_id";
dlWaitSample.DataBind();
strSql = "select dep_id,dep_cdesc from sample_trace_dep order by sorting,dep_id ";
DataTable tbDep = sh.ExecuteSqlReturnDataTable(strSql);
dlOwnType.DataSource = tbDep.DefaultView;
dlOwnType.DataTextField = "dep_cdesc";
dlOwnType.DataValueField = "dep_id";
dlOwnType.DataBind();
}
private bool FindMo(string mo_id)
{
string strSql = "";
bool result = true;
strSql = "Select mo_id From so_polo_order_trace Where mo_id='" + mo_id + "'";
DataTable dtOc = sh.ExecuteSqlReturnDataTable(strSql);
if (dtOc.Rows.Count == 0)
{
result = false;
}
return(result);
}
private void InitValues()
{
string strSql = "Select typ_code AS id,typ_desc From bs_type Where typ_group='3'";
DataTable tbCp = sh.ExecuteSqlReturnDataTable(strSql);
dlSales_group.DataSource = tbCp.DefaultView;
dlSales_group.DataTextField = "typ_desc";
dlSales_group.DataValueField = "id";
dlSales_group.DataBind();
dlSales_group.SelectedIndex = 0;
//txtDate1.Text = "____/__/__";
//txtDate2.Text = "____/__/__";
}
protected void InitControler()
{
string strSql;
strSql = "Select formname,show_name from v_dict_group where formname=" + "'" + "Mo_Group" + "'" +
" AND language_id =" + "'" + lang_id + "'" + " Order By tb_col_sort";
DataTable tbMo_group = sh.ExecuteSqlReturnDataTable(strSql);
dlMo_group.DataSource = tbMo_group.DefaultView;
dlMo_group.DataTextField = "show_name";
dlMo_group.DataValueField = "show_name";
dlMo_group.DataBind();
createDateStart.Value = System.DateTime.Now.ToString("yyyy/MM/dd");
createDateEnd.Value = createDateStart.Value;
dlShowPeriod.SelectedIndex = 2;
}
protected void LoadBatchMo()
{
string dat = txtDate.Text;
string strSql = "Select * From mo_movestatus Where within_code='" + within_code + "'";
if (txtDep.Text != "")
{
strSql = strSql + " AND dep='" + txtDep.Text + "'";
}
if (dat != "")
{
strSql = strSql + " AND count_date='" + dat + "'";
}
DataTable tbMoFind = sh.ExecuteSqlReturnDataTable(strSql);
if (tbMoFind.Rows.Count == 0)
{
tbMoFind.Rows.Add();
}
gvBatchMo.DataSource = tbMoFind.DefaultView;
gvBatchMo.DataBind();
}
/// <summary>
/// 数据集操作
/// </summary>
/// <param name="ds"></param>
/// 更新Excel表到临时表pu_temp_excel中
private void DataSetOperator(DataTable dt)
{
string result_str = "";
string strSql = "", strSql_f = "";
string now_date, prd_dep = "102";
int excel_row = 0;
//string[] proSub = Request.Form.GetValues("selDep");
//prd_dep = proSub[selDep.SelectedIndex];
prd_dep = dlDep.SelectedValue.ToString().Trim();
now_date = dateArrange.Value; //System.DateTime.Now.ToString("yyyy/MM/dd");
string prd_mo, prd_item, urgent_status1, urgent_status, arrange_date, arrange_machine, order_date = "", req_time, dep_rep_date;
int arrange_seq, order_qty, req_qty, cpl_qty, wait_cpl_qty, prd_cpl_qty;
user_id = getUserName();
//strSql += string.Format(@"Delete From product_arrange Where prd_dep='{0}' and now_date='{1}'", prd_dep, now_date);
try
{
for (int i = 0; i < dt.Rows.Count; i++)
{
excel_row = excel_row + 1;
DataRow dr = dt.Rows[i];
arrange_seq = (dr[0].ToString() != "" ? Convert.ToInt32(dr[0]) : 0);
prd_mo = dr[1].ToString();
if (prd_mo != "")
{
arrange_date = (dr[2].ToString() != "" ? Convert.ToDateTime(dr[2].ToString()).ToString("yyyy/MM/dd") : "");
urgent_status1 = dr[3].ToString();
urgent_status = "";
if (urgent_status1 == "超特急")
{
urgent_status = "01";
}
else
{
if (urgent_status1 == "特急")
{
urgent_status = "02";
}
else
{
if (urgent_status1 == "急單" || urgent_status1 == "急")
{
urgent_status = "03";
}
}
}
prd_item = dr[4].ToString();
order_date = (dr[7].ToString() != "" ? Convert.ToDateTime(dr[7].ToString()).ToString("yyyy/MM/dd") : "");
req_time = (dr[9].ToString() != "" ? Convert.ToDateTime(dr[9].ToString()).ToString("yyyy/MM/dd") : "");
order_qty = (dr[10].ToString() != "" ? Convert.ToInt32(dr[10]) : 0);
req_qty = (dr[11].ToString() != "" ? Convert.ToInt32(dr[11]) : 0);
cpl_qty = (dr[12].ToString() != "" ? Convert.ToInt32(dr[12]) : 0);
wait_cpl_qty = (dr[13].ToString() != "" ? Convert.ToInt32(dr[13]) : 0);
prd_cpl_qty = (dr[14].ToString() != "" ? Convert.ToInt32(dr[14]) : 0);
dep_rep_date = (dr[16].ToString() != "" ? Convert.ToDateTime(dr[16].ToString()).ToString("yyyy/MM/dd") : "");
arrange_machine = dr[17].ToString();
string dtt = System.DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss");
string id = prd_dep + dtt.Substring(0, 4) + dtt.Substring(5, 2) + dtt.Substring(8, 2) + dtt.Substring(11, 2) + dtt.Substring(14, 2) + dtt.Substring(17, 2) + (i + 1).ToString().PadLeft(4, '0');
strSql_f = "Select prd_mo From product_arrange Where prd_dep='" + prd_dep + "' And now_date='" + now_date + "' And prd_mo='" + prd_mo + "' And prd_item='" + prd_item + "'";
if (sh.ExecuteSqlReturnDataTable(strSql_f).Rows.Count == 0)
{
strSql += string.Format(@"INSERT INTO product_arrange (arrange_id,now_date,prd_dep,prd_mo,prd_item,urgent_status,arrange_machine,arrange_date,arrange_seq,order_qty
,order_date,req_time,req_qty,cpl_qty,wait_cpl_qty,prd_cpl_qty,dep_rep_date,crusr,crtim)
VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}',GETDATE())"
, id, now_date, prd_dep, prd_mo, prd_item, urgent_status, arrange_machine, arrange_date, arrange_seq, order_qty
, order_date, req_time, req_qty, cpl_qty, wait_cpl_qty, prd_cpl_qty, dep_rep_date, user_id);
}
else
{
strSql += string.Format(@"Update product_arrange Set urgent_status='{0}',arrange_machine='{1}',arrange_date='{2}',arrange_seq='{3}'
,order_qty='{4}',order_date='{5}',req_time='{6}',req_qty='{7}',cpl_qty='{8}',wait_cpl_qty='{9}',prd_cpl_qty='{10}'
,dep_rep_date='{11}',amusr='******',amtim=GETDATE() Where prd_dep='{13}' And now_date='{14}' And prd_mo='{15}' And prd_item='{16}'"
, urgent_status, arrange_machine, arrange_date, arrange_seq, order_qty
, order_date, req_time, req_qty, cpl_qty, wait_cpl_qty, prd_cpl_qty, dep_rep_date, user_id, prd_dep, now_date, prd_mo, prd_item);
}
}
}
if (strSql != "")
{
result_str = sh.ExecuteSqlUpdate(strSql);//更新明細記錄
}
else
{
result_str = "";
}
}
catch (Exception ex)
{
result_str = "Excel文件的欄位不正確:(" + excel_row.ToString() + ")" + ex.Message;
}
if (result_str != "")
{
//Response.Write(String.Format("<script text='text/javascript'>alert('{0}')</script>", result_str));
StrHlp.WebMessageBox(this.Page, result_str);
}
}
public void getPlan(HttpContext context)
{
clsPublic cls = new clsPublic();
BasePage bp = new BasePage();
string ReturnValue = string.Empty;
string Prd_dep = "";
string Arrange_date = "";
string Prd_mo_from = "", Prd_mo_to = "";
string Prd_item_from = "";
string Dep_group = "";
string strSql = "";
string para = context.Request["param"];
if (para != null)
{
JArray ja = (JArray)JsonConvert.DeserializeObject(para);
Prd_dep = ja[0]["Prd_dep"].ToString().Trim();
Arrange_date = ja[0]["Arrange_date"].ToString().Trim();
Prd_mo_from = ja[0]["Prd_mo_from"].ToString().Trim();
Prd_mo_to = ja[0]["Prd_mo_to"].ToString().Trim();
Prd_item_from = ja[0]["Prd_item_from"].ToString().Trim();
Dep_group = ja[0]["Dep_group"].ToString().Trim();
}
else
{
Prd_dep = context.Request["Prd_dep"] != null ? context.Request["Prd_dep"] : "";
Arrange_date = context.Request["Arrange_date"] != null ? context.Request["Arrange_date"] : "";
Prd_mo_from = context.Request["Prd_mo_from"] != null ? context.Request["Prd_mo_from"] : "";
Prd_mo_to = context.Request["Prd_mo_to"] != null ? context.Request["Prd_mo_to"] : "";
Prd_item_from = context.Request["Prd_item_from"] != null ? context.Request["Prd_item_from"] : "";
Dep_group = context.Request["Dep_group"] != null ? context.Request["Dep_group"] : "";
}
if (Prd_dep == "" && Arrange_date == "" && Prd_mo_from == "" && Prd_mo_to == "" && Prd_item_from == "" && Dep_group == "")
{
Prd_dep = "ZZZ";
Prd_mo_from = "ZZZZZZZZZ";
Prd_mo_to = "ZZZZZZZZZ";
Prd_item_from = "";
}
strSql = "Select a.arrange_id,a.now_date, a.prd_dep, a.prd_mo, a.prd_item,b.name AS prd_item_cdesc, a.mo_urgent,c.flag_cdesc" +
", a.arrange_machine, a.arrange_date, a.arrange_seq, a.order_qty" +
", a.order_date, a.req_f_date, a.req_qty, a.cpl_qty, a.arrange_qty, a.prd_cpl_qty, a.dep_rep_date,a.cust_o_date,a.dep_group " +
",dgcf_pad.dbo.fn_getArrangeWorker(a.arrange_id) AS worker_gp,a.pre_prd_dep_date,a.pre_prd_dep_qty " +
" From dgcf_pad.dbo.product_arrange a " +
" Left Join geo_it_goods b On a.prd_item COLLATE chinese_taiwan_stroke_CI_AS=b.id " +
" Left Join bs_flag_desc c On a.mo_urgent COLLATE chinese_taiwan_stroke_CI_AS=c.flag_id " +
" Where a.arrange_id>'' And c.doc_type='mo_urgent_status' ";
if (Prd_dep != "")
{
strSql += " And a.prd_dep='" + Prd_dep + "'";
}
if (Arrange_date != "")
{
strSql += " And a.now_date='" + Arrange_date + "'";
}
if (Prd_mo_from != "" && Prd_mo_to != "")
{
strSql += " And a.prd_mo>='" + Prd_mo_from + "' And a.prd_mo<='" + Prd_mo_to + "'";
}
if (Dep_group != "")
{
strSql += " And a.dep_group='" + Dep_group + "'";
}
if (Prd_item_from != "")
{
strSql += " And a.prd_item Like '" + "%" + Prd_item_from + "%'";
}
strSql += " Order By a.prd_dep,a.dep_group,a.arrange_seq,a.arrange_date";
DataTable dt = sh.ExecuteSqlReturnDataTable(strSql);
ReturnValue = cls.DataTableJsonReturnExcel(dt);//提取記錄，返回給表格
context.Response.ContentType = "text/plain";
context.Response.Write(ReturnValue);
context.Response.End();
}
/// <summary>
/// 数据集操作
/// </summary>
/// <param name="ds"></param>
/// 更新Excel表到临时表pu_temp_excel中
private void DataSetOperator(DataTable dt, string fileName)
{
string result_str = "";
string strSql = "", strSql_f = "";
string now_date, prd_dep = "501";
int excel_row = 0;
//string[] proSub = Request.Form.GetValues("selDep");
//prd_dep = proSub[selDep.SelectedIndex];
prd_dep = dlDep.SelectedValue.ToString().Trim();
string vendor_id, prd_mo, prd_item, remark;
user_id = getUserName();
try
{
string vend_h = "", prd_mo_h = "", prd_item_h = "", remark_h = "";
for (int j = 0; j < dt.Columns.Count; j++)
{
string colName = dt.Columns[j].ColumnName;
vend_h = (colName == "供應商" ? colName : vend_h);
prd_mo_h = (colName == "頁數" ? colName : prd_mo_h);
prd_item_h = (colName == "貨品編碼" ? colName : prd_item_h);
remark_h = (colName == "電鍍回覆" ? colName : remark_h);
}
if (vend_h == "" || prd_mo_h == "" || prd_item_h == "" || remark_h == "")
{
StrHlp.WebMessageBox(this.Page, "Excel文件第一行必須為有效的表頭格式：供應商 -- 頁數 -- 貨品編碼--電鍍回覆!");
return;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
excel_row = excel_row + 1;
DataRow dr = dt.Rows[i];
prd_mo = (prd_mo_h == "" ? "" : dr[prd_mo_h].ToString().Trim());
if (prd_mo != "")
{
vendor_id = (prd_item_h == "" ? "" : dr[vend_h].ToString());
prd_item = (prd_item_h == "" ? "" : dr[prd_item_h].ToString());
remark = (remark_h == "" ? "" : dr[remark_h].ToString());
strSql_f = "Select mo_id From mo_outside_rework_mark Where dep_id='" + prd_dep + "' And vendor_id='" + vendor_id + "' And mo_id='" + prd_mo + "' And goods_id='" + prd_item + "'";
DataTable dtArrange = sh.ExecuteSqlReturnDataTable(strSql_f);
if (dtArrange.Rows.Count == 0)
{
//產生自動單號
strSql += string.Format(@"INSERT INTO mo_outside_rework_mark (dep_id,vendor_id,mo_id,goods_id,remark,crusr,crtim)
VALUES ('{0}','{1}','{2}','{3}','{4}','{5}',GETDATE())"
, prd_dep, vendor_id, prd_mo, prd_item, remark, user_id);
}
else
{
strSql += string.Format(@"Update mo_outside_rework_mark Set remark='{0}',crusr='******'
,crtim=GETDATE() Where dep_id='{2}' And vendor_id='{3}' And mo_id='{4}' And goods_id='{5}'"
, remark, user_id, prd_dep, vendor_id, prd_mo, prd_item);
}
}
}
if (strSql != "")
{
result_str = sh.ExecuteSqlUpdate(strSql);//更新明細記錄
StrHlp.WebMessageBox(this.Page, "匯入電鍍回覆成功!");
this.DataSetBand();
}
else
{
result_str = "";
}
}
catch (Exception ex)
{
result_str = "Excel文件的欄位不正確:(" + excel_row.ToString() + ")" + ex.Message;
}
if (result_str != "")
{
//Response.Write(String.Format("<script text='text/javascript'>alert('{0}')</script>", result_str));
StrHlp.WebMessageBox(this.Page, result_str);
}
}
protected void DataSetBand()
{
bool is_val = false;
string prd_dep = dlDep.SelectedValue;
string prd_mo = txtPrd_mo.Value;
this.OrderList.DataSource = null;
System.Web.UI.WebControls.PagedDataSource ps = new PagedDataSource();
//CollectionPager1.DataSource = null;
SQLHelp sh = new SQLHelp();
string strSql = "";
strSql = "Select dep_id,vendor_id,mo_id,goods_id,remark,crusr,Convert(Varchar(20),crtim,120) As crtim " +
" From mo_outside_rework_mark Where dep_id>'' ";
if (prd_dep != "" && prd_dep != null)
{
strSql += " And dep_id='" + prd_dep + "'";
}
if (txtVendor_id.Value != "")
{
strSql += " And vendor_id='" + txtVendor_id.Value + "'";
is_val = true;
}
if (prd_mo != "" && prd_mo != null)
{
strSql += " And mo_id='" + prd_mo + "'";
is_val = true;
}
if (is_val == false)
{
string dat1 = System.DateTime.Now.ToString("yyyy/MM/dd");
string dat2 = System.DateTime.Now.AddDays(1).ToString("yyyy/MM/dd");
strSql += " And crtim>='" + dat1 + "' And crtim<'" + dat2 + "'";
}
strSql += " Order By dep_id,vendor_id,crtim Desc";
DataTable dt = sh.ExecuteSqlReturnDataTable(strSql);
ps.DataSource = dt.DefaultView;
ps.AllowPaging = true;
ps.PageSize = 30; //每页显示几条记录
ps.CurrentPageIndex = curPage - 1; //设置当前页的索引(当前页码减1就是)
//this.OrderList.DataSource = ps;
////this.hourseDataList.DataKeyField = "hourseId";
//this.OrderList.DataBind();
////如果有UpdatePanel就用如下代码调用前台js
//////this.Page.ClientScript.RegisterStartupScript(this.Page.GetType(), "", "<script>fixgrid();</script>", true);
//////ScriptManager.RegisterStartupScript(UpdatePanel, this.Page.GetType(), "", "fixgrid();", true);
this.firstBtn.Enabled = true;
this.nextBtn.Enabled = true;
this.prevBtn.Enabled = true;
this.lastBtn.Enabled = true;
totalPage = ps.PageCount;
setCurrentPage(totalPage);//設置下拉框頁數
this.lTotalPage.Text = totalPage.ToString();
if (curPage == 1)//当是第一页是.上一页和首页的按钮不可用
{
this.prevBtn.Enabled = false;
this.firstBtn.Enabled = false;
}
if (curPage == ps.PageCount)//当是最后一页时下一页和最后一页的按钮不可用
{
this.nextBtn.Enabled = false;
this.lastBtn.Enabled = false;
}
this.OrderList.DataSource = ps;
//this.hourseDataList.DataKeyField = "hourseId";
this.OrderList.DataBind();
//如果有UpdatePanel就用如下代码调用前台js
ScriptManager.RegisterStartupScript(UpdatePanel, this.Page.GetType(), "", "fixgrid();", true);
}
private void Data_Binding()
{
//string dateFrom = Request.QueryString["dateFrom"].ToString();
//string dateTo = Request.QueryString["dateTo"].ToString();
//string ID = Request.QueryString["ID"];
//this.ReportViewer1.Reset();
//this.ReportViewer1.LocalReport.Dispose();
//this.ReportViewer1.LocalReport.DataSources.Clear();
//Microsoft.Reporting.WebForms.ReportDataSource reportDataSource = new Microsoft.Reporting.WebForms.ReportDataSource();
//reportDataSource.Name = "DsSo";
////Dgsql2DBContext db = new Dgsql2DBContext();
////var model = new so_online_order_details();
////int id = 1023;
////List<so_online_order_details> lsModel = new List<so_online_order_details>();
////lsModel = db.so_online_order_details.ToList();
//var lsModel = SoDAL.GetPrintSo(ID);
//double TotalAmountUSD = 0;
//for (int i = 0; i < lsModel.Count; i++)
//{
// TotalAmountUSD += lsModel[i].AmountUSD;
//}
//reportDataSource.Value = lsModel;// Db.BaseUser.Find(id);
//this.ReportViewer1.LocalReport.ReportPath = Server.MapPath("../Reports/St_GoodsTransferJxDetails.rdlc");
//this.ReportViewer1.LocalReport.DataSources.Add(reportDataSource);
//ReportParameter rp = new ReportParameter("TotalAmount", TotalAmountUSD.ToString());
//this.ReportViewer1.LocalReport.SetParameters(new ReportParameter[] { rp });
//this.ReportViewer1.LocalReport.Refresh();
//ReportViewer1.LocalReport.ReportPath = AppDomain.CurrentDomain.BaseDirectory + "/Reports/sample_trace.rdlc";//
//ReportViewer1.LocalReport.EnableExternalImages = true;
//// 获取 MyHandler.jxd 的完整路径
////string barcode_url = Request.Url.Scheme + "://" + Request.Url.Authority + Request.ApplicationPath + "file/image/" + "/MyHandler.jxd?data=";
//string barcode_url = Request.Url.Scheme + "://" + Request.Url.Authority + Request.ApplicationPath + "file/image/";
////+"file/image"
////-> "http://localhost:6344/HttpHandlerDemo/MyHandler.jxd?data="
string prdDep = Request.QueryString["prdDep"].ToString();
string moGroup = Request.QueryString["moGroup"].ToString();
string prdMoFrom = Request.QueryString["prdMoFrom"].ToString();
string prdMoTo = Request.QueryString["prdMoTo"].ToString();
string prdItem = Request.QueryString["prdItem"].ToString();
string dateFrom = Request.QueryString["dateFrom"].ToString();
string dateTo = Request.QueryString["dateTo"].ToString();
string transferFlag = Request.QueryString["transferFlag"].ToString();
string strSql = "Select a.prd_dep,d.dep_cdesc AS prd_dep_cdesc,a.prd_mo,a.prd_item,b.name AS prd_item_cdesc" +
",a.transfer_qty,a.transfer_weg,a.wip_id" +
",a.transfer_date,c.flag_desc,a.to_dep,e.dep_cdesc AS to_dep_cdesc " +
" FROM dgcf_pad.dbo.product_transfer_jx_details a" +
" LEFT JOIN geo_it_goods b ON a.prd_item=b.id COLLATE chinese_taiwan_stroke_CI_AS" +
" LEFT JOIN bs_flag_desc c ON a.transfer_flag=c.flag_id COLLATE chinese_taiwan_stroke_CI_AS" +
" LEFT JOIN bs_dep d ON a.wip_id=d.dep_id COLLATE chinese_taiwan_stroke_CI_AS" +
" LEFT JOIN bs_dep e ON a.to_dep=e.dep_id COLLATE chinese_taiwan_stroke_CI_AS" +
" WHERE c.doc_type='goods_transfer_jx'";
if (prdDep != "")
{
strSql += " AND a.prd_dep='" + prdDep + "'";
}
if (moGroup != "")
{
strSql += " AND Substring(a.prd_mo,3,1)='" + moGroup + "'";
}
if (prdMoFrom != "" && prdMoTo != "")
{
strSql += " AND a.prd_mo>='" + prdMoFrom + "' AND a.prd_mo<='" + prdMoTo + "'";
}
if (prdItem != "")
{
strSql += " AND a.prd_item Like '" + "%" + prdItem + "%" + "'";
}
if (dateFrom != "" && dateTo != "")
{
strSql += " AND a.Transfer_date>='" + dateFrom + "' AND a.Transfer_date<='" + dateTo + "'";
}
if (transferFlag.Trim() == "0" || transferFlag.Trim() == "1")
{
strSql += " AND a.Transfer_flag='" + transferFlag + "'";
}
strSql += " ORDER BY a.prd_dep,a.Transfer_flag,a.transfer_date,a.prd_item,a.prd_mo";
DataTable dtPrint = sh.ExecuteSqlReturnDataTable(strSql);
this.ReportViewer1.Reset();
//this.ReportViewer1.LocalReport.Dispose();
ReportViewer1.LocalReport.DataSources.Clear();
this.ReportViewer1.LocalReport.ReportPath = Server.MapPath("../Reports/St_GoodsTransferJxDetails.rdlc");
Microsoft.Reporting.WebForms.ReportDataSource rds = new Microsoft.Reporting.WebForms.ReportDataSource("DSDgsql2Dgcf_pad", dtPrint);
//this.ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("DSDgcf_pad", dtPrint));
//ReportViewer1.LocalReport.DataSources.Add(
//new Microsoft.Reporting.WebForms.ReportDataSource("DSDgcf_pad", dtPrint));
//向報表傳遞多個參數
List <ReportParameter> para = new List <ReportParameter>(); //这里是添加两个字段
para.Add(new ReportParameter("userId", getUserName()));
//para.Add(new ReportParameter("userName", "aaa"));
ReportViewer1.LocalReport.SetParameters(para);
ReportViewer1.LocalReport.DataSources.Add(rds);
this.ReportViewer1.ZoomMode = Microsoft.Reporting.WebForms.ZoomMode.Percent;
this.ReportViewer1.ZoomPercent = 100;
ReportViewer1.LocalReport.Refresh();
//string doc_type_to = "相關制單";
//string color = "N001";
//ReportViewer1.LocalReport.ReportPath = "Reports\\sample_trace.rdlc";//Report1.rdlc;
//ReportViewer1.LocalReport.EnableExternalImages = true;
////ReportViewer1.LocalReport.SetParameters(new ReportParameter(doc_type_to));//, Guid.NewGuid().ToString()
////向報表傳遞單個參數
////ReportParameter rp = new ReportParameter("doc_type_to", doc_type_to);
////ReportViewer1.LocalReport.SetParameters(new ReportParameter[] { rp });
////向報表傳遞多個參數
//ReportParameter[] para = new ReportParameter[3];
//para[0] = new ReportParameter("Remark", txtRemark_head.Text);
//para[1] = new ReportParameter("RouteDep", txtRouteDep.Text);
//para[2] = new ReportParameter("barcode_url", barcode_url);
//ReportViewer1.LocalReport.SetParameters(para);
//ReportDataSource rds = new ReportDataSource("DataSet_Trace", dtPrint);
//ReportViewer1.LocalReport.DataSources.Clear();
//ReportViewer1.LocalReport.DataSources.Add(rds);
//ReportViewer1.LocalReport.Refresh();
//ReportViewer1.LocalReport.Refresh();
}
public void GetPlan(HttpContext context)
{
clsPublic cls = new clsPublic();
BasePage bp = new BasePage();
string ReturnValue = string.Empty;
string Prd_dep = "";
string Now_date = "";
string Prd_mo_from = "", Prd_mo_to = "";
string Prd_item_from = "";
string Mat_item_from = "";
string para = context.Request["param"];
if (para != null)
{
JArray ja = (JArray)JsonConvert.DeserializeObject(para);
Prd_dep = ja[0]["Prd_dep"].ToString().Trim();
Now_date = ja[0]["Now_date"].ToString().Trim();
Prd_mo_from = ja[0]["Prd_mo_from"].ToString().Trim();
Prd_mo_to = ja[0]["Prd_mo_to"].ToString().Trim();
Prd_item_from = ja[0]["Prd_item_from"].ToString().Trim();
Mat_item_from = ja[0]["Mat_item_from"].ToString().Trim();
}
else
{
Prd_dep = context.Request["Prd_dep"] != null ? context.Request["Prd_dep"] : "";
Now_date = context.Request["Now_date"] != null ? context.Request["Now_date"] : "";
Prd_mo_from = context.Request["Prd_mo_from"] != null ? context.Request["Prd_mo_from"] : "";
Prd_mo_to = context.Request["Prd_mo_to"] != null ? context.Request["Prd_mo_to"] : "";
Prd_item_from = context.Request["Prd_item_from"] != null ? context.Request["Prd_item_from"] : "";
Mat_item_from = context.Request["Mat_item_from"] != null ? context.Request["Mat_item_from"] : "";
}
if (Prd_dep == "" && Now_date == "" && Prd_mo_from == "" && Prd_mo_to == "" && Prd_item_from == "" && Mat_item_from == "")
{
Prd_dep = "ZZZ";
Prd_mo_from = "ZZZZZZZZZ";
Prd_mo_to = "ZZZZZZZZZ";
Prd_item_from = "";
}
string strSql = "Select a.prd_seq,a.receive_date,a.prd_mo,a.prd_item,a.arrange_machine" +
",a.prd_worker,a.delivery_date,a.arrange_qty,a.cpl_qty,a.not_cpl_qty,a.mat_item,a.arrange_id,a.prd_dep" +
",a.arrange_date,a.now_date,a.wp_id" +
",b.name As prd_item_cdesc" +
",d.mat_item AS mat_item1,c.name As mat_item_cdesc,d.kg_qty_rate,Convert(float,d.kg_qty_rate) AS not_cpl_weg " +
" From product_arrange_jx a" +
" Left Join geo_it_goods b On a.prd_item=b.id" +
" Left Join bs_mat_rate d On a.prd_item=d.prd_item And a.wp_id=d.dep_id" +
" Left Join geo_it_goods c On d.mat_item=c.id" +
" Where a.arrange_id>=''";
if (Prd_dep != "")
{
strSql += " And a.prd_dep='" + Prd_dep + "'";
}
if (Now_date != "")
{
strSql += " And a.now_date='" + Now_date + "'";
}
if (Prd_mo_from != "" && Prd_mo_to != "")
{
strSql += " And a.prd_mo>='" + Prd_mo_from + "' And a.prd_mo<='" + Prd_mo_to + "'";
}
if (Prd_item_from != "")
{
strSql += " And a.prd_item Like " + "'%" + Prd_item_from + "%'";
}
if (Mat_item_from != "")
{
strSql += " And d.mat_item Like " + "'%" + Mat_item_from + "%'";
}
strSql += " Order By a.prd_dep,a.arrange_seq";
DataTable dt = sh.ExecuteSqlReturnDataTable(strSql);
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
double kg_qty_rate = dr["kg_qty_rate"].ToString() != "" ? Convert.ToInt32(dr["kg_qty_rate"]) : 0;
dr["not_cpl_weg"] = kg_qty_rate != 0 ? Math.Round(Convert.ToDouble(dr["not_cpl_qty"]) / Convert.ToDouble(dr["kg_qty_rate"]), 2) : 0;
}
ReturnValue = cls.DataTableJsonReturnExcel(dt);//提取記錄，返回給表格
context.Response.ContentType = "text/plain";
context.Response.Write(ReturnValue);
context.Response.End();
}
private void Data_Binding()
{
string productMoFrom = Request.QueryString["productMoFrom"].ToString();
string productMoTo = Request.QueryString["productMoTo"].ToString();
string invFrom = Request.QueryString["invFrom"].ToString();
string invTo = Request.QueryString["invTo"].ToString();
string dateFrom = Request.QueryString["dateFrom"].ToString();
string dateTo = Request.QueryString["dateTo"].ToString();
string remote_db = DBUtility.remote_db;
string within_code = DBUtility.within_code;
string strSql = "Select b.mo_id,a.id,a.invoice_no,Convert(Varchar(20),a.invoice_date,111) AS invoice_date,c.name AS goods_cname " +
",b.issues_unit,Convert(INT,b.issues_qty) AS issues_qty,d.id AS order_id,d.unit_price,d.p_unit,f.rate" +
",Round((b.issues_qty/f.rate)*Convert(float,d.unit_price),2) AS order_amt" +
",a.linkman,d.table_head,e.m_id" +
" From " + remote_db + "so_issues_mostly a" +
" Inner Join " + remote_db + "so_issues_details b On a.within_code=b.within_code And a.id=b.id" +
" Inner Join " + remote_db + "it_goods c On b.within_code=c.within_code And b.goods_id=c.id" +
" Inner Join " + remote_db + "so_order_details d On b.within_code=d.within_code And b.mo_id=d.mo_id" +
" Inner Join " + remote_db + "so_order_manage e On d.within_code=e.within_code And d.id=e.id And d.ver=e.ver" +
" Inner Join " + remote_db + "it_coding f On d.within_code=f.within_code And d.p_unit=f.unit_code And b.issues_unit=f.basic_unit" +
" Where a.within_code='" + within_code + "' And f.id='*'";
if (dateFrom == "" && invFrom == "" && productMoFrom == "")
{
invFrom = "INV999999999";
invTo = "INV999999999";
}
if (dateFrom != "" && dateTo != "")
{
dateTo = Convert.ToDateTime(dateTo).AddDays(1).ToString("yyyy/MM/dd");
strSql += " And a.invoice_date>='" + dateFrom + "' And a.invoice_date<'" + dateTo + "'";
}
if (productMoFrom != "" && productMoTo != "")
{
strSql += " And b.mo_id>='" + productMoFrom + "' And b.mo_id<='" + productMoTo + "'";
}
if (invFrom != "" && invTo != "")
{
strSql += " And a.invoice_no>='" + invFrom + "' And a.invoice_no<='" + invTo + "'";
}
strSql += " Order By a.invoice_no,a.invoice_date,b.mo_id";
DataTable dtPrint = sh.ExecuteSqlReturnDataTable(strSql);
decimal totalAmt = 0;
this.ReportViewer1.Reset();
//this.ReportViewer1.LocalReport.Dispose();
ReportViewer1.LocalReport.DataSources.Clear();
this.ReportViewer1.LocalReport.ReportPath = Server.MapPath("../Reports/Sa_Iv_VatDeliveryDetails.rdlc");
Microsoft.Reporting.WebForms.ReportDataSource rds = new Microsoft.Reporting.WebForms.ReportDataSource("dsSa_Iv_VatDeliveryDetails", dtPrint);
//this.ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("DSDgcf_pad", dtPrint));
//ReportViewer1.LocalReport.DataSources.Add(
//new Microsoft.Reporting.WebForms.ReportDataSource("DSDgcf_pad", dtPrint));
//向報表傳遞多個參數
List <ReportParameter> para = new List <ReportParameter>(); //这里是添加两个字段
para.Add(new ReportParameter("totalAmt", totalAmt.ToString()));
//para.Add(new ReportParameter("userName", "aaa"));
ReportViewer1.LocalReport.SetParameters(para);
ReportViewer1.LocalReport.DataSources.Add(rds);
this.ReportViewer1.ZoomMode = Microsoft.Reporting.WebForms.ZoomMode.Percent;
this.ReportViewer1.ZoomPercent = 100;
ReportViewer1.LocalReport.Refresh();
//string doc_type_to = "相關制單";
//string color = "N001";
//ReportViewer1.LocalReport.ReportPath = "Reports\\sample_trace.rdlc";//Report1.rdlc;
//ReportViewer1.LocalReport.EnableExternalImages = true;
////ReportViewer1.LocalReport.SetParameters(new ReportParameter(doc_type_to));//, Guid.NewGuid().ToString()
////向報表傳遞單個參數
////ReportParameter rp = new ReportParameter("doc_type_to", doc_type_to);
////ReportViewer1.LocalReport.SetParameters(new ReportParameter[] { rp });
////向報表傳遞多個參數
//ReportParameter[] para = new ReportParameter[3];
//para[0] = new ReportParameter("Remark", txtRemark_head.Text);
//para[1] = new ReportParameter("RouteDep", txtRouteDep.Text);
//para[2] = new ReportParameter("barcode_url", barcode_url);
//ReportViewer1.LocalReport.SetParameters(para);
//ReportDataSource rds = new ReportDataSource("DataSet_Trace", dtPrint);
//ReportViewer1.LocalReport.DataSources.Clear();
//ReportViewer1.LocalReport.DataSources.Add(rds);
//ReportViewer1.LocalReport.Refresh();
//ReportViewer1.LocalReport.Refresh();
}
/// <summary>
/// 数据集操作
/// </summary>
/// <param name="ds"></param>
/// 更新Excel表到临时表pu_temp_excel中
private void DtOperator(DataTable dt, string fileName, string sheetName)
{
bool upd_flag = true;
string result_str = "";
string strSql = "", strSql_f = "";
imgProcess.Visible = true;
string now_date, prd_dep = "102";
int excel_row = 1;
//string[] proSub = Request.Form.GetValues("selDep");
//prd_dep = proSub[selDep.SelectedIndex];
prd_dep = dlDep.SelectedValue.ToString().Trim();
now_date = txtArrangeDate.Value; //System.DateTime.Now.ToString("yyyy/MM/dd");
string prd_mo, prd_item, urgent_status1, urgent_status, arrange_date, arrange_machine, cust_o_date = "", req_f_date, dep_rep_date, req_hk_date, dep_group;
string pre_prd_dep_date = "";
int pre_prd_dep_qty = 0;
int arrange_seq, order_qty, req_qty, cpl_qty, arrange_qty, prd_cpl_qty;
string prd_status = "00";//
string rec_status = "0";
user_id = getUserName();
string strDep = fileName.Substring(0, 3);
dep_group = "";
if (prd_dep == "102")
{
if (fileName.Substring(0, 3) == "萬能機")
{
dep_group = "A";
}
else
{
if (fileName.Substring(0, 2) == "雞眼")
{
dep_group = "B";
}
}
}
//strSql += string.Format(@"Delete From product_arrange Where prd_dep='{0}' and now_date='{1}'", prd_dep, now_date);
try
{
string arrange_seq_h = "", prd_mo_h = "", urgent_status_h = "", prd_item_h = "", cust_o_date_h = "", req_f_date_h = "", order_qty_h = "", cpl_qty_h = ""
, arrange_date_h = "", arrange_qty_h = "", req_qty_h = "", prd_cpl_qty_h = "", dep_rep_date_h = "", arrange_machine_h = ""
, req_hk_date_h = "", dep_group_h1 = "", dep_group_h2 = "", dep_group_h3 = ""
, pre_prd_dep_date_h = "", pre_prd_dep_qty_h = "";
for (int j = 0; j < dt.Columns.Count; j++)
{
string colName = dt.Columns[j].ColumnName;
arrange_seq_h = (colName == "序號" ? colName : arrange_seq_h);
prd_mo_h = (colName == "制單編號" || colName == "頁數" ? colName : prd_mo_h);
urgent_status_h = (colName == "急單" || colName == "状态" || colName == "狀態" ? colName : urgent_status_h);
arrange_date_h = (colName == "排期日期" || colName == "排期日期AA" || colName == "排期" ? colName : arrange_date_h);
prd_item_h = (colName == "產品編號" || colName == "物料編號" ? colName : prd_item_h);
cust_o_date_h = (colName == "客落單日期" ? colName : cust_o_date_h);
req_f_date_h = (colName == "要求完成時間" || colName == "pmc要求完成日期" ? colName : req_f_date_h);
order_qty_h = (colName == "訂單數量" ? colName : order_qty_h);
req_qty_h = (colName == "要求數量" || colName == "應生產數量" ? colName : req_qty_h);
cpl_qty_h = (colName == "完成數量" || colName == "已完成數量" ? colName : cpl_qty_h);
arrange_qty_h = (colName == "待完成數量" ? colName : arrange_qty_h);
prd_cpl_qty_h = (colName == "生產數量" ? colName : prd_cpl_qty_h);
dep_rep_date_h = (colName == "部門回覆" || colName == "部門覆期" || colName == "部門復期" ? colName : dep_rep_date_h);
arrange_machine_h = (colName == "生產設備" ? colName : arrange_machine_h);
req_hk_date_h = (colName == "計劃回港期" || colName == "計劃回港日期" || colName == "回港期" ? colName : req_hk_date_h);
dep_group_h1 = (colName == "自動" || colName == "组別" || colName == "組別" ? colName : dep_group_h1);
dep_group_h2 = (colName == "打扣" ? colName : dep_group_h2);
dep_group_h3 = (colName == "車碑" ? colName : dep_group_h3);
pre_prd_dep_date_h = (colName == "上部門來貨期" ? colName : pre_prd_dep_date_h);
pre_prd_dep_qty_h = (colName == "上部門來貨數量" ? colName : pre_prd_dep_qty_h);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
strSql = "";
excel_row = excel_row + 1;
DataRow dr = dt.Rows[i];
//if (i == 577)
//{
// int aa = 1;
//}
arrange_seq = (arrange_seq_h == "" ? 0 : (dr[arrange_seq_h].ToString() != "" ? Convert.ToInt32(dr[arrange_seq_h]) : 0));
prd_mo = (prd_mo_h == "" ? "" : dr[prd_mo_h].ToString().Trim());
if (prd_mo != "")
{
arrange_date = (arrange_date_h == "" ? "" : (dr[arrange_date_h].ToString() != "" ? Convert.ToDateTime(dr[arrange_date_h].ToString()).ToString("yyyy/MM/dd") : ""));
urgent_status1 = (urgent_status_h == "" ? "" : dr[urgent_status_h].ToString().Trim());
urgent_status = "";
if (urgent_status1 == "超特急")
{
urgent_status = "04";
}
else
{
if (urgent_status1 == "特急")
{
urgent_status = "03";
}
else
{
if (urgent_status1 == "急單" || urgent_status1 == "急")
{
urgent_status = "02";
}
else
{
urgent_status = "00";
}
}
}
prd_item = (prd_item_h == "" ? "" : dr[prd_item_h].ToString());
cust_o_date = (cust_o_date_h == "" ? "" : (dr[cust_o_date_h].ToString() != "" ? Convert.ToDateTime(dr[cust_o_date_h].ToString()).ToString("yyyy/MM/dd") : ""));
req_f_date = (req_f_date_h == "" ? "" : (dr[req_f_date_h].ToString() != "" ? Convert.ToDateTime(dr[req_f_date_h].ToString()).ToString("yyyy/MM/dd") : ""));
order_qty = (order_qty_h == "" ? 0 : (dr[order_qty_h].ToString() != "" ? Convert.ToInt32(dr[order_qty_h]) : 0));
req_qty = (req_qty_h == "" ? 0 : (dr[req_qty_h].ToString() != "" ? Convert.ToInt32(dr[req_qty_h]) : 0));
cpl_qty = (cpl_qty_h == "" ? 0 : (dr[cpl_qty_h].ToString() != "" ? Convert.ToInt32(dr[cpl_qty_h]) : 0));
arrange_qty = (arrange_qty_h == "" ? 0 : (dr[arrange_qty_h].ToString() != "" ? Convert.ToInt32(dr[arrange_qty_h]) : 0));
prd_cpl_qty = (prd_cpl_qty_h == "" ? 0 : (dr[prd_cpl_qty_h].ToString() != "" ? Convert.ToInt32(dr[prd_cpl_qty_h]) : 0));
//dep_rep_date = (dep_rep_date_h == "" ? "" : (dr[dep_rep_date_h].ToString() != "" ? Convert.ToDateTime(dr[dep_rep_date_h].ToString()).ToString("yyyy/MM/dd") : ""));
dep_rep_date = (dep_rep_date_h == "" ? "" : (dr[dep_rep_date_h].ToString() != "" ? dr[dep_rep_date_h].ToString() : ""));
arrange_machine = (arrange_machine_h == "" ? "" : dr[arrange_machine_h].ToString());
req_hk_date = (req_hk_date_h == "" ? "" : (dr[req_hk_date_h].ToString() != "" ? Convert.ToDateTime(dr[req_hk_date_h].ToString()).ToString("yyyy/MM/dd") : ""));
pre_prd_dep_date = (pre_prd_dep_date_h == "" ? "" : dr[pre_prd_dep_date_h].ToString());
pre_prd_dep_qty = (pre_prd_dep_qty_h == "" ? 0 : (dr[pre_prd_dep_qty_h].ToString() != "" ? Convert.ToInt32(dr[pre_prd_dep_qty_h]) : 0));
if (prd_dep == "202")
{
dep_group = (dep_group_h1 == "" ? "" : dr[dep_group_h1].ToString());//自動
if (dep_group != "")
{
dep_group = "A";
}
else
{
if (dep_group == "")
{
dep_group = (dep_group_h2 == "" ? "" : dr[dep_group_h2].ToString());//打扣
if (dep_group != "")
{
dep_group = "B";
}
else
{
if (dep_group == "")
{
dep_group = (dep_group_h3 == "" ? "" : dr[dep_group_h3].ToString());//車碑
if (dep_group != "")
{
dep_group = "C";
}
}
}
}
}
}
else
{
dep_group = (dep_group_h1 == "" ? "" : dr[dep_group_h1].ToString());
}
string dtt = System.DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss");
strSql_f = "Select arrange_id From dgcf_pad.dbo.product_arrange Where prd_dep='" + prd_dep + "' And now_date='" + now_date + "' And prd_mo='" + prd_mo + "' And prd_item='" + prd_item + "'";
DataTable dtArrange = sh.ExecuteSqlReturnDataTable(strSql_f);
if (dtArrange.Rows.Count == 0)
{
//產生自動單號
string id = prd_dep + dtt.Substring(0, 4) + dtt.Substring(5, 2) + dtt.Substring(8, 2) + dtt.Substring(11, 2) + dtt.Substring(14, 2) + dtt.Substring(17, 2) + (i + 1).ToString().PadLeft(4, '0');
//string prd_seq = "01";
strSql += string.Format(@"INSERT INTO dgcf_pad.dbo.product_arrange (arrange_id,now_date,prd_dep,prd_mo,prd_item,mo_urgent,arrange_machine,arrange_date,arrange_seq,order_qty
,cust_o_date,req_f_date,req_qty,cpl_qty,arrange_qty,prd_cpl_qty,dep_rep_date,rec_status,prd_status,req_hk_date,dep_group,pre_prd_dep_date,pre_prd_dep_qty,crusr,crtim)
VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}',GETDATE())"
, id, now_date, prd_dep, prd_mo, prd_item, urgent_status, arrange_machine, arrange_date, arrange_seq, order_qty
, cust_o_date, req_f_date, req_qty, cpl_qty, arrange_qty, prd_cpl_qty, dep_rep_date, rec_status, prd_status, req_hk_date, dep_group, pre_prd_dep_date, pre_prd_dep_qty, user_id);
}
else
{
string arrange_id = dtArrange.Rows[0]["arrange_id"].ToString();
strSql += string.Format(@"Update dgcf_pad.dbo.product_arrange Set mo_urgent='{0}',arrange_machine='{1}',arrange_date='{2}',arrange_seq='{3}'
,order_qty='{4}',cust_o_date='{5}',req_f_date='{6}',req_qty='{7}',cpl_qty='{8}',arrange_qty='{9}',prd_cpl_qty='{10}'
,dep_rep_date='{11}',rec_status='{12}',prd_status='{13}',req_hk_date='{14}',dep_group='{15}',now_date='{16}',pre_prd_dep_date='{17}',pre_prd_dep_qty='{18}',amusr='******'
,amtim=GETDATE() Where arrange_id='{20}'"
, urgent_status, arrange_machine, arrange_date, arrange_seq, order_qty, cust_o_date, req_f_date, req_qty
, cpl_qty, arrange_qty, prd_cpl_qty, dep_rep_date, rec_status, prd_status, req_hk_date, dep_group, now_date, pre_prd_dep_date, pre_prd_dep_qty, user_id, arrange_id);
}
result_str = sh.ExecuteSqlUpdate(strSql);//更新明細記錄
if (result_str != "")
{
upd_flag = false;
break;
}
}
}
imgProcess.Visible = false;
if (upd_flag)
{
StrHlp.WebMessageBox(this.Page, "匯入排期表成功!");
}
else
{
StrHlp.WebMessageBox(this.Page, "匯入排期表失敗,記錄： " + excel_row.ToString() + " " + result_str);
}
//if (strSql != "")
//{
// result_str = sh.ExecuteSqlUpdate(strSql);//更新明細記錄
// if (result_str == "")
// StrHlp.WebMessageBox(this.Page, "匯入排期表成功!");
// else
// StrHlp.WebMessageBox(this.Page, "匯入排期表失敗："+ result_str);
//}
//else
//{
// result_str = "";
//}
}
catch (Exception ex)
{
result_str = "Excel文件的欄位不正確,行:" + excel_row.ToString() + " " + ex.Message;
}
imgProcess.Visible = false;
if (result_str != "")
{
//Response.Write(String.Format("<script text='text/javascript'>alert('{0}')</script>", result_str));
StrHlp.WebMessageBox(this.Page, result_str);
}
}
/// <summary>
/// 数据集操作
/// </summary>
/// <param name="ds"></param>
/// 更新Excel表到临时表pu_temp_excel中
private void DataSetOperator(DataTable dt, string fileName)
{
string result_str = "";
string strSql = "", strSql_f = "";
int excel_row = 0;
string prd_mo, urgent_status, urgent_status1 = "";
user_id = getUserName();
//strSql += string.Format(@"Delete From product_arrange Where prd_dep='{0}' and now_date='{1}'", prd_dep, now_date);
try
{
string prd_mo_h = "", urgent_status_h = "";
for (int j = 0; j < dt.Columns.Count; j++)
{
string colName = dt.Columns[j].ColumnName;
prd_mo_h = (colName == "制單編號" || colName == "頁數" || colName == "未完成頁數" ? colName : prd_mo_h);
urgent_status_h = (colName == "急單" || colName == "状态" || colName == "狀態" || colName == "急/特急狀態" ? colName : urgent_status_h);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
excel_row = excel_row + 1;
prd_mo = (prd_mo_h == "" ? "" : dr[prd_mo_h].ToString().Trim());
if (prd_mo != "")
{
urgent_status1 = (urgent_status_h == "" ? "" : dr[urgent_status_h].ToString().Trim());
urgent_status = "";
if (urgent_status1 == "超特急")
{
urgent_status = "04";
}
else
{
if (urgent_status1 == "特急")
{
urgent_status = "03";
}
else
{
if (urgent_status1 == "急單" || urgent_status1 == "急")
{
urgent_status = "02";
}
else
{
urgent_status = "00";
}
}
}
strSql_f = "Select mo_id From mo_status Where mo_id='" + prd_mo + "'";
DataTable dtArrange = sh.ExecuteSqlReturnDataTable(strSql_f);
if (dtArrange.Rows.Count == 0)
{
strSql += string.Format(@"INSERT INTO mo_status (mo_id,mo_status,cr_usr,cr_tim)
VALUES ('{0}','{1}','{2}',GETDATE())", prd_mo, urgent_status, user_id);
}
else
{
strSql += string.Format(@"Update mo_status Set mo_status='{0}',am_usr='******'
,am_tim=GETDATE() Where mo_id='{2}'", urgent_status, user_id, prd_mo);
}
}
}
if (strSql != "")
{
result_str = sh.ExecuteSqlUpdate(strSql);//更新明細記錄
StrHlp.WebMessageBox(this.Page, "更新制單狀態成功!");
}
else
{
result_str = "";
}
}
catch (Exception ex)
{
result_str = "Excel文件的欄位不正確:(" + excel_row.ToString() + ")" + ex.Message;
}
if (result_str != "")
{
//Response.Write(String.Format("<script text='text/javascript'>alert('{0}')</script>", result_str));
StrHlp.WebMessageBox(this.Page, result_str);
}
}
private int chkWorker(string arrange_id, string worker_id, string work_type_id)
{
string strSql = " Select worker_id From dgcf_pad.dbo.product_arrange_worker Where arrange_id='" + arrange_id + "' And worker_id='" + worker_id + "' And work_type_id='" + work_type_id + "'";
return(sh.ExecuteSqlReturnDataTable(strSql).Rows.Count);
}