/// <summary>
/// 顯示區域工作明細
/// </summary>
private void view1()
{
gridControl2.Visible = false;
gridControl1.Visible = true;
String strSQL = string.Format("select isnull(d.groupname,'其他') as 編制組別,c.GROUPNAME as 工作組別,wEmpID as 工號,wName as 姓名,case when c.GROUPNAME<>isnull(d.groupname,'') then '支援' else '' end as 備註,substring(convert(varchar,wSTime,120),12,5) as 開始時間 ", v1);
strSQL = strSQL + string.Format(" from [192.168.100.208].[ERP].[dbo].[DC_EmpGroup] a right join Darren_RFWorkRecord_Agg b ", v1);
strSQL = strSQL + string.Format(" on a.EmpID=b.wEmpID and convert(char(8),empdate,112)=convert(char(8),GETDATE(),112) join workitem_jack c on replace(b.wZone,' ','')=replace(c.DCTYPE+c.GROUPNAME+c.ITEMNAME,' ','') ", v1);
strSQL = strSQL + string.Format(" left join work_referce d on d.groupid=empgroupvalue where convert(char(8),wSysTime,112)=convert(char(8),GETDATE(),112) and wETime is null and wkclass='{0}' ", v1);
dt1 = sca1.GetDataTable(v2, strSQL);
gridControl1.DataSource = dt1;
int[] width = new int[] {
0, 0, 0, 0, 0,
0, 0, 50, 0, 0,
50, 50
};
#region 計算支援人數
int kk = 0;
for (int i = 0; i < dt1.Rows.Count; i++)
{
if (dt1.Rows[i]["備註"].ToString() == "支援")
{
kk = kk + 1;
}
}
#endregion
lbl_Content_Form2.Text = "支援" + v3 + "工作" + kk.ToString() + "人 " + " 共 " + dt1.Rows.Count.ToString() + "人";
lbl_WorkName_Form2.Text = v3;
}
private void button_Search_Click(object sender, EventArgs e)
{
#region 檢驗有沒有調整單存在
cmdstring = "Select top 1 I_corh_flag from cort_head where L_corh_id=@corhid and I_corh_type=3";
object[] objParam = { "@corhid", SqlDbType.NVarChar.ToString(), textBox_corhid.Text };
DataTable dt1 = sca.GetDataTable(conn_Sel, cmdstring, objParam, 0);
if (dt1.Rows.Count == 0)
{
MessageBox.Show("無此單號/_\\");
return;
}
if (dt1.Rows[0][0].ToString() == "60")
{
MessageBox.Show("單據已經完成");
return;
}
dt1.Clear();
#endregion
#region 帶出調整單內容
cmdstring = @"select b.S_cori_merdid, b.D_cori_newexpdate, b.S_cori_newsbuid, d.D_merl_expdate,d.S_merl_sbuid,d.L_merl_merdsysno
from cort_head a inner join cort_item b on a.L_corh_id=b.L_cori_corhid
left join recirecord_item c on a.L_corh_jobid=c.L_reri_recrid and b.L_cori_merdsysno=c.L_reri_merdsysno
left join mer_list d on c.L_reri_merlsysno=d.L_merl_sysno
where L_corh_id=@corhid and I_corh_type=3 ";
DataTable dt2 = sca.GetDataTable(conn_Sel, cmdstring, objParam, 0);
if (dt2.Rows.Count > 0)
{
dataGridView1.DataSource = dt2;
}
else
{
MessageBox.Show("無調整資料/_\\");
return;
}
#endregion
#region 檢查mer_list有沒有空白事業單位的資料
foreach (DataRow dr in dt2.Rows)
{
if (dr["S_merl_sbuid"].ToString() == "")
{
MessageBox.Show("事業單位不可為空白，請洽資訊部人員");
return;
}
}
checkBox1.Checked = true;
button_StartAdj.Enabled = true;
#endregion
}
private void Form1_Load(object sender, EventArgs e)
{
SetMasterBindingNavigator(null);
tabPage1.BackColor = this.BackColor;
tabPage2.BackColor = this.BackColor;
tabPage3.BackColor = this.BackColor;
//取得此表單的LoaderFormInfo
fFormInfo = XSC.ClientLoader.FormInfo(this);
//透過LoginUserId取得sqlClientAccess
sca = XSC.ClientAccess.UserAccess.sqlUserAccess(fFormInfo.LoginUserId);
//fFormInfo.UserId;
comboBox1.SelectedIndex = 0;
object[] objParam = { "@userid", SqlDbType.NVarChar.ToString(), fFormInfo.LoginUserId };
cmdstring = "Select EMPID From XSC_Menu_Userlist where XSC_UserID=@userid";
DataTable dt = sca.GetDataTable("EEPDC", cmdstring, objParam, 0);
if (dt.Rows.Count > 0)
{
Global.Globalparameter.UserName = dt.Rows[0]["EMPID"].ToString();
}
else
{
MessageBox.Show("找不到工號，無法使用外掛");
Global.Globalparameter.UserName = "";
button_Search.Enabled = false;
}
toolStripStatusLabel1.Text = "XSC使用者：" + fFormInfo.UserId;
toolStripStatusLabel2.Text = " WMS使用者工號：" + Global.Globalparameter.UserName;
}
private void button3_Click(object sender, EventArgs e)
{
object[] obj_CheckAuthority = { "@a01", "NVarChar", "" };
string strSelect = @"SELECT
HAS_PERMS_BY_NAME(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name), 'OBJECT','SELECT') AS select1,
HAS_PERMS_BY_NAME(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name), 'OBJECT','INSERT') AS insert1,
HAS_PERMS_BY_NAME(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name), 'OBJECT','DELETE') AS delete1,
HAS_PERMS_BY_NAME(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name), 'OBJECT','UPDATE') AS update1,
* FROM sys.tables
order by name";
DataTable dt_CheckTable = sca.GetDataTable(comboBox1.SelectedItem.ToString(), strSelect, obj_CheckAuthority, 0);
dataGridView2.DataSource = dt_CheckTable;
strSelect =
@"select
name
, has_perms_by_name(name, 'OBJECT', 'EXECUTE') as has_execute
, has_perms_by_name(name, 'OBJECT', 'VIEW DEFINITION') as has_view_definition
from sys.procedures";
DataTable dt_CheckSp = sca.GetDataTable(comboBox1.SelectedItem.ToString(), strSelect, obj_CheckAuthority, 0);
dataGridView3.DataSource = dt_CheckSp;
strSelect =
@"select
name
, has_perms_by_name(name, 'OBJECT', 'EXECUTE') as has_execute
, has_perms_by_name(name, 'OBJECT', 'VIEW DEFINITION') as has_view_definition
from sys.views";
DataTable dt_CheckView = sca.GetDataTable(comboBox1.SelectedItem.ToString(), strSelect, obj_CheckAuthority, 0);
dataGridView4.DataSource = dt_CheckView;
strSelect = richTextBox1.Text;
if (strSelect != "")
{
DataTable dt_CheckAuthority = sca.GetDataTable(comboBox1.SelectedItem.ToString(), strSelect, obj_CheckAuthority, 0);
dataGridView1.DataSource = dt_CheckAuthority;
}
}
private void Form2_Load(object sender, EventArgs e)
{
fFormInfo = XSC.ClientLoader.FormInfo(this);
sca1 = XSC.ClientAccess.UserAccess.sqlUserAccess(fFormInfo.LoginUserId);
string sql = string.Format("select GROUPNAME from workitem_jack where wkclass='{0}'", v1);
dt1 = sca1.GetDataTable(v2, sql);
if (dt1.Rows.Count > 0)
{
v4 = dt1.Rows[0][0].ToString();
}
view1();
}
static public string CreatPdf_Prod(XSC.ClientAccess.sqlClientAccess sca, string PO, string siteno, string Host_string, string IP_string, string Mainfilepath)
{
string returnstring = "";
Document doc = new Document();
string filepath = Mainfilepath;
string path; //檔案路徑
string FileKey; //PO單號
DataTable dt = new DataTable();
//產生字型檔,圖檔,然後刪除
if (File.Exists(Environment.CurrentDirectory + "\\WMSADJ\\logo.jpg") == false)
{
return("無logo.jpg，請嘗試重開程式");
}
if (File.Exists(Environment.CurrentDirectory + "\\WMSADJ\\IDAutomationHC39M_FREE.otf") == false)
{
return("無otf，請嘗試重開程式");
}
if (File.Exists(Environment.CurrentDirectory + "\\WMSADJ\\kaiu.ttf") == false)
{
return("無ttf，請嘗試重開程式");
}
#region 初使參數設定
// 定義字型
BaseFont bfChinese =
BaseFont.CreateFont(Environment.CurrentDirectory + "\\WMSADJ\\kaiu.ttf", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);//中文字型標楷體
BaseFont bfCode =
//BaseFont.CreateFont(@"C:\WINDOWS\Fonts\3of9.ttf", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
BaseFont.CreateFont(Environment.CurrentDirectory + "\\WMSADJ\\IDAutomationHC39M_FREE.otf", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
//標題
Paragraph h = new Paragraph();
h.Alignment = Element.ALIGN_CENTER;
h.Font = new Font(bfChinese, 32, Font.BOLD + Font.NORMAL, BaseColor.BLACK);
h.SetLeading(0.0f, 0.9f);
// 內容
Paragraph h_title = new Paragraph();
h_title.Alignment = Element.ALIGN_CENTER;
h_title.Font = new Font(bfChinese, 20, Font.BOLD + Font.NORMAL, BaseColor.BLACK);
h_title.SetLeading(2.0f, 0.9f);
// 條碼
Paragraph h_Code = new Paragraph();
h_Code.Alignment = Element.ALIGN_CENTER;
h_Code.Font = new Font(bfCode, 32);//, Font.NORMAL,, BaseColor.BLACK);
h_Code.SetLeading(0.0f, 0.9f);
// 空白行
Paragraph h_newline = new Paragraph();
h_newline.Alignment = Element.ALIGN_CENTER;
h_newline.Font = new Font(bfChinese, 10, Font.BOLD + Font.NORMAL, BaseColor.BLUE);
h_newline.SetLeading(0.0f, 0.9f);
h_newline.Add(" ");
// 空白行(大)
Paragraph h_newline_L = new Paragraph();
h_newline_L.Alignment = Element.ALIGN_CENTER;
h_newline_L.Font = new Font(bfChinese, 72, Font.BOLD + Font.NORMAL, BaseColor.BLUE);
h_newline_L.SetLeading(0.0f, 0.9f);
h_newline_L.Add(" ");
#endregion
try
{
#region 找PO單
string sqlcommand =
@"
select
SITE_NO_Descr=SITE_NO_Descr+'['+DOCK_MODE_Descr+']',
PO_NO,
VENDOR_NO=VENDOR_NO+VENDOR_Name,
ARRIVE_DATE,
ARRIVE_START_TIME=ARRIVE_START_TIME+'-'+ARRIVE_END_TIME,
RECEIVE_PLACE_ADDR,
remark,
remark2,
SITE_NO,
ITEM_SEQ_NO,
ITEM_NO=ITEM_NO+CASE WHEN WARM_LAYER!='' THEN '('+WARM_LAYER+')' ELSE '' END,
NAME,
QTY,
IP_QTY,
WARM_LAYER,
EA_Barcode,
INNER_QTY,
MANUFACTURING_DAYS=CASE WHEN MANUFACTURING_DAYS=MATURITIES_DAYS THEN '無保存期限' ELSE Replace(MATURITIES_DAYS,'/','')+'後生產' END,
MATURITIES_DAYS=CASE WHEN MANUFACTURING_DAYS=MATURITIES_DAYS THEN '' ELSE Replace(MATURITIES_DAYS,'/','')+'後到期' END,
IP_Barcode,
CS_Barcode,
Packing_memo,
Carton_QTY=convert(varchar,Carton_QTY)+CS_UNIT,
VENDOR_ITEM_NO,
TI=convert(varchar,TI)+'底/'+convert(varchar,HI)+'高',
TotalCase
from v_T_PO
where PO_NO=@po_no
and site_no=@site_no";
object[] scaParam = { "@po_no", "NVarChar", PO,
"@site_no", "NVarChar", siteno };
dt = sca.GetDataTable("EDI_DC", sqlcommand, scaParam, 0);
dt.TableName = "v_T_PO";
if (dt.Rows.Count == 0)
{
returnstring += "找不到資料，請確認PO單號存在";
return(returnstring);
}
#endregion
#region 記錄log
sqlcommand = "insert into RunLog (JobID,JobType,JobDate,JobCmd,Crt_date) "
+ " values(''," //JobID
+ "'手動產生PDF" //JobType
+ "',CONVERT(CHAR(10),GETDATE(),112)" //JobDate
+ ",'PDF_PO_Kota.exe " + PO
+ "," + System.Security.Principal.WindowsIdentity.GetCurrent().Name
+ "," + Host_string
+ "," + IP_string //JobCmd
+ "',GETDATE()) "; //Crt_date
sca.Update("LGDC", sqlcommand, scaParam, 0);
#endregion
DataRow dr = dt.Rows[0];
FileKey = dr["PO_NO"].ToString();
string FileKey_ = "";
PdfPCell pCell_tmp;
PdfPTable p;
int Font_Size = 10;
#region Logo
// 公司Logo
string Img_url = Environment.CurrentDirectory + "\\WMSADJ\\logo.jpg";
iTextSharp.text.Image jpg = iTextSharp.text.Image.GetInstance(new Uri(Img_url));
jpg.ScalePercent(60);
PdfPCell jpgcell = new PdfPCell(jpg);
jpgcell.Border = 0;
jpgcell.Colspan = 2;
jpgcell.Column.Alignment = Element.ALIGN_CENTER;
jpgcell.VerticalAlignment = Element.ALIGN_CENTER;
#endregion
int INNER_QTY = 0;
int QTY = 0;
int QTY_div_INNER_QTY = 0;
// 分頁
int row_page = 13; //每頁幾筆
int row_Count_PDFNow = 0; //pdf第幾頁
int row_Count_Now = 0; //第幾筆
int Page = 0; //總頁碼
int pageEnd = 0; //結束頁
p = new PdfPTable(3);
string strTotalCase = "";
foreach (DataRow drt in dt.Rows)
{
FileKey = drt["PO_NO"].ToString();
#region 允收日期 2014/01/07
string RecDateS = "", RecDateE = "";
if (drt["MANUFACTURING_DAYS"].ToString() == drt["MATURITIES_DAYS"].ToString())
{
RecDateS = "無保存期限";
RecDateE = "";
}
else
{
RecDateS = string.Format("{0:yyyy/MM/dd}", drt["MANUFACTURING_DAYS"]).Replace("/", "") + "後生產";
RecDateE = string.Format("{0:yyyy/MM/dd}", drt["MATURITIES_DAYS"]).Replace("/", "") + "後到期";
}
#endregion
#region 溫層 2014/01/07
string WarmLayer = "";
if (drt["WARM_LAYER"].ToString() != "")
{
WarmLayer = "(" + drt["WARM_LAYER"].ToString() + ")";
}
#endregion
#region 單據不同則產生單據
if (FileKey_ != FileKey)
{
if (FileKey_ != "")
{
//尾頁
p = PageEnd(bfChinese, doc, h_newline, p, Font_Size);
//簽收
PageEndSign(bfChinese, row_Count_Now, row_page, pageEnd, Font_Size, p, Page, doc);
}
//頁碼重新計算
row_Count_PDFNow = 0;
row_Count_Now = 0;
Page = 0;
pageEnd = 0;
DataRow[] PageRows;
string expression;
expression = "PO_NO = '" + FileKey + "'";
PageRows = dt.Select(expression);
int row_Count = PageRows.Length; //單號-總筆數
if (row_Count % row_page > 0)
{
Page = row_Count / row_page + 1;
}
else
{
Page = row_Count / row_page;
}
FileKey_ = FileKey;
doc = new Document(PageSize.A4, 20, 15, 15, 15);
path = filepath + "\\" + FileKey.Trim() + "_" + drt["SITE_NO"].ToString().Trim() + ".pdf";
if (path != "")
{
PdfWriter writer = PdfWriter.GetInstance(doc, new FileStream(path, FileMode.Create));
writer.PageEvent = new PDFFooter();
returnstring += "PDF產生完成 " + path;
}
else
{
returnstring += FileKey.Trim() + " 路徑有誤，請重新產生";
return(returnstring);
}
doc.Open();
row_Count_Now = 0;
}
#endregion
row_Count_Now++;
row_Count_PDFNow++;
#region 換頁時重印單頭
if ((row_Count_Now % row_page) == 1)
{
if (row_Count_Now > row_page)
{
doc.Add(p);
doc.Add(h_newline);
p = new PdfPTable(1);
p.WidthPercentage = 100;
pCell_tmp = new PdfPCell();
pCell_tmp = setTable_Cell(bfChinese, string.Format(@"{0} / {1} ", row_Count_PDFNow / row_page, Page)//row_Count / row_page
, 0, Font_Size, Element.ALIGN_RIGHT, 3, 1);
p.AddCell(pCell_tmp);
doc.Add(p);
doc.NewPage();
}
#region 表頭
p = new PdfPTable(5);
p.WidthPercentage = 110;
//送貨通知單
pCell_tmp = setTable_Cell(bfChinese, "送貨通知單", 0, Font_Size + 4, Element.ALIGN_CENTER, 1, 1);
pCell_tmp.Colspan = 5;
pCell_tmp.PaddingTop = 7f;
p.AddCell(pCell_tmp);
//Logo
p.AddCell(jpgcell);
//ex:觀音物流中心[越庫馬頭]
p.AddCell(setTable_Cell(bfChinese, string.Format("{0}", drt["SITE_NO_Descr"]), 0, Font_Size, Element.ALIGN_CENTER, 1, 1, 7f));//SITE_NO,SITE_NO_Descr,DOCK_MODE_Descr
// 條碼
h_Code.Clear();
h_Code.Alignment = Element.ALIGN_RIGHT;
h_Code.Font = new Font(bfCode, 12);//, Font.NORMAL,, BaseColor.BLACK);
h_Code.Add(string.Format("{0}", "*" + drt["PO_NO"] + "*"));
pCell_tmp = new PdfPCell(h_Code);
pCell_tmp.Border = 0;
pCell_tmp.Colspan = 2;
pCell_tmp.Column.Alignment = Element.ALIGN_CENTER;
pCell_tmp.VerticalAlignment = Element.ALIGN_CENTER;
p.AddCell(pCell_tmp);
// p.AddCell(setTable_Cell(string.Format("單號:{0}", drt["PO_NO"]), 0, Font_Size, Element.ALIGN_CENTER, 2, 1));
p.AddCell(setTable_Cell(bfChinese, string.Format(" 廠商:{0}", drt["VENDOR_NO"]), 0, Font_Size, Element.ALIGN_LEFT, 2, 1, 7f));
p.AddCell(setTable_Cell(bfChinese, string.Format(" 到貨日期:{0}", drt["ARRIVE_DATE"].ToString().Substring(0, 10)), 0, Font_Size, Element.ALIGN_LEFT, 1, 1, 7f));
p.AddCell(setTable_Cell(bfChinese, string.Format(" 單號:{0}", drt["PO_NO"]), 0, Font_Size, Element.ALIGN_LEFT, 2, 1, 7f));
p.AddCell(setTable_Cell(bfChinese, string.Format(" 列印時間:{0}", DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")), 0, Font_Size, Element.ALIGN_LEFT, 2, 1, 7f));
p.AddCell(setTable_Cell(bfChinese, string.Format("預計進廠起迄:{0}", drt["ARRIVE_START_TIME"]), 0, Font_Size, Element.ALIGN_LEFT, 1, 1, 7f));
p.AddCell(setTable_Cell(bfChinese, " 客服電話:(886)0800335888", 0, Font_Size, Element.ALIGN_LEFT, 2, 1, 7f));
p.AddCell(setTable_Cell(bfChinese, string.Format(" 到貨地址:{0}", drt["RECEIVE_PLACE_ADDR"].ToString().Trim()), 0, Font_Size, Element.ALIGN_LEFT, 2, 1, 7f));
p.AddCell(setTable_Cell(bfChinese, string.Format("{0}", drt["remark"]), 0, Font_Size, Element.ALIGN_LEFT, 3, 1, 7f));
p.AddCell(setTable_Cell(bfChinese, string.Format("{0}", drt["remark2"]), 0, Font_Size, Element.ALIGN_CENTER, 5, 1, 7f));
//// 空行
p.AddCell(pCell_tmp);
doc.Add(p);
#endregion
#region 表身的頭
float[] widths = { 0.5f, 1.5f, 2f, 1.4f, 0.6f, 1.5f, 1.2f };
p = new PdfPTable(widths);//7
p.WidthPercentage = 100;
p.AddCell(setTable_Cell_Bottom(bfChinese, " ", 0, Font_Size - 1, Element.ALIGN_CENTER, 1, 3));
p.AddCell(setTable_Cell_Bottom(bfChinese, "貨號/條碼/外箱條碼", 0, Font_Size - 1, Element.ALIGN_CENTER, 1, 3));
p.AddCell(setTable_Cell_Bottom(bfChinese, "貨品名稱/規格/包裝組別", 0, Font_Size - 1, Element.ALIGN_CENTER, 1, 3));
p.AddCell(setTable_Cell_Bottom(bfChinese, "訂購量/入數/箱數", 0, Font_Size - 1, Element.ALIGN_CENTER, 1, 3));
p.AddCell(setTable_Cell_Bottom(bfChinese, "實收量", 0, Font_Size - 1, Element.ALIGN_CENTER, 1, 3));
p.AddCell(setTable_Cell_Bottom(bfChinese, "允收日期/廠商貨號", 0, Font_Size - 1, Element.ALIGN_CENTER, 1, 3));
p.AddCell(setTable_Cell_Bottom(bfChinese, "小包裝/棧板堆疊", 0, Font_Size - 1, Element.ALIGN_CENTER, 1, 3));
#endregion
}
#endregion
INNER_QTY = Convert.ToInt32(drt["INNER_QTY"].ToString());
QTY = Convert.ToInt32(drt["QTY"].ToString());
#region 表身明細
//序號 Table
//
p.AddCell(setTable_Cell_Bottom(bfChinese, string.Format("{0}", drt["ITEM_SEQ_NO"]), 0, Font_Size, Element.ALIGN_CENTER, 1, 3));//ITEM_SEQ_NO
//貨號
p.AddCell(setTable_Cell(bfChinese, string.Format("{0}", drt["ITEM_NO"]) + WarmLayer, 0, Font_Size, Element.ALIGN_LEFT, 1, 1));
//貨品名稱
p.AddCell(setTable_Cell(bfChinese, string.Format("{0}", drt["NAME"]), 0, Font_Size - 1, Element.ALIGN_MIDDLE, 1, 2));
//訂購量
p.AddCell(setTable_Cell(bfChinese, string.Format("{0}", QTY), 0, Font_Size, Element.ALIGN_RIGHT, 1, 1));
//實收量
p.AddCell(setTable_Cell_Bottom(bfChinese, string.Format("{0}", ""), 0, Font_Size, Element.ALIGN_CENTER, 1, 3));
//允收日期1
p.AddCell(setTable_Cell(bfChinese, RecDateS, 0, Font_Size - 1, Element.ALIGN_CENTER, 1, 1));
//小包裝
//0 和 null 都要顯示 ""
Object IP_qty;
switch (drt["IP_QTY"].ToString())
{
case "0": IP_qty = ""; break;
default: IP_qty = drt["IP_QTY"]; break;
}
p.AddCell(setTable_Cell(bfChinese, string.Format("{0}", IP_qty), 0, Font_Size, Element.ALIGN_RIGHT, 1, 1));
//資料列 第2排----------------------------------------------------------------------------------------------------------
//條碼
p.AddCell(setTable_Cell(bfChinese, string.Format("{0}", drt["EA_BARCODE"]), 0, Font_Size, Element.ALIGN_LEFT, 1, 1));
//入數
p.AddCell(setTable_Cell(bfChinese, string.Format("{0}", INNER_QTY), 0, Font_Size, Element.ALIGN_RIGHT, 1, 1));
//允收日期2
p.AddCell(setTable_Cell(bfChinese, RecDateE, 0, Font_Size - 1, Element.ALIGN_CENTER, 1, 1));
//棧板堆疊-底
p.AddCell(setTable_Cell(bfChinese, string.Format("{0}", drt["IP_Barcode"]), 0, Font_Size, Element.ALIGN_RIGHT, 1, 1));
//資料列 第3排-------------------------------------------------------------------------------------------------------------------------------------
//外箱條碼
p.AddCell(setTable_Cell_Bottom(bfChinese, string.Format("{0}", drt["CS_BARCODE"]), 0, Font_Size, Element.ALIGN_LEFT, 1, 1));
//贈品
p.AddCell(setTable_Cell_Bottom(bfChinese, string.Format("{0}", drt["Packing_memo"]), 0, Font_Size, Element.ALIGN_CENTER, 1, 1));
if (INNER_QTY != 0)
{
QTY_div_INNER_QTY = QTY / INNER_QTY;
}
else
{
QTY_div_INNER_QTY = 0;
}
//箱數
p.AddCell(setTable_Cell_Bottom(bfChinese, string.Format("{0}", drt["Carton_QTY"]), 0, Font_Size, Element.ALIGN_RIGHT, 1, 1));
//廠商貨號
p.AddCell(setTable_Cell_Bottom(bfChinese, string.Format("{0}", drt["VENDOR_ITEM_NO"]), 0, Font_Size, Element.ALIGN_CENTER, 1, 1));
//棧板堆疊-高
p.AddCell(setTable_Cell_Bottom(bfChinese, string.Format("{0}", drt["TI"]), 0, Font_Size, Element.ALIGN_RIGHT, 1, 1));
strTotalCase = drt["TotalCase"].ToString();
#endregion
}
doc.Add(p);
doc.Add(h_newline);
//總箱量
setTable_Boxes(bfChinese, p, Font_Size, strTotalCase, doc);
//尾頁
p = PageEnd(bfChinese, doc, h_newline, p, Font_Size);
//簽收
PageEndSign(bfChinese, row_Count_Now, row_page, pageEnd, Font_Size, p, Page, doc);
}
catch (Exception err)
{
returnstring += "發生錯誤\n" + err.Message + "\n";
//throw new Exception(err.Message);
}
finally
{
doc.Close();
}
return(returnstring);
}
static public string CreatPdf_Prod(XSC.ClientAccess.sqlClientAccess sca, string RJ, string siteno, string Host_string, string IP_string, string Mainfilepath)
{
string returnstring = "";
Document doc = new Document();
string filepath = Mainfilepath;
string path; //檔案路徑
string FileKey; //PO單號
DataTable dt = new DataTable();
//產生字型檔,圖檔,然後刪除
if (File.Exists(Environment.CurrentDirectory + "\\WMSADJ\\logo.jpg") == false)
{
return("無logo.jpg，請嘗試重開程式");
}
if (File.Exists(Environment.CurrentDirectory + "\\WMSADJ\\IDAutomationHC39M_FREE.otf") == false)
{
return("無otf，請嘗試重開程式");
}
if (File.Exists(Environment.CurrentDirectory + "\\WMSADJ\\kaiu.ttf") == false)
{
return("無ttf，請嘗試重開程式");
}
#region 初使參數設定
// 定義字型
BaseFont bfChinese =
BaseFont.CreateFont(Environment.CurrentDirectory + "\\WMSADJ\\kaiu.ttf", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);//中文字型標楷體
BaseFont bfCode =
//BaseFont.CreateFont(@"C:\WINDOWS\Fonts\3of9.ttf", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
BaseFont.CreateFont(Environment.CurrentDirectory + "\\WMSADJ\\IDAutomationHC39M_FREE.otf", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
//標題
Paragraph h = new Paragraph();
h.Alignment = Element.ALIGN_CENTER;
h.Font = new Font(bfChinese, 32, Font.BOLD + Font.NORMAL, BaseColor.BLACK);
h.SetLeading(0.0f, 0.9f);
// 內容
Paragraph h_title = new Paragraph();
h_title.Alignment = Element.ALIGN_CENTER;
h_title.Font = new Font(bfChinese, 20, Font.BOLD + Font.NORMAL, BaseColor.BLACK);
h_title.SetLeading(2.0f, 0.9f);
// 條碼
Paragraph h_Code = new Paragraph();
h_Code.Alignment = Element.ALIGN_CENTER;
h_Code.Font = new Font(bfCode, 32);//, Font.NORMAL,, BaseColor.BLACK);
h_Code.SetLeading(0.0f, 0.9f);
// 空白行
Paragraph h_newline = new Paragraph();
h_newline.Alignment = Element.ALIGN_CENTER;
h_newline.Font = new Font(bfChinese, 10, Font.BOLD + Font.NORMAL, BaseColor.BLUE);
h_newline.SetLeading(0.0f, 0.9f);
h_newline.Add(" ");
// 空白行(大)
Paragraph h_newline_L = new Paragraph();
h_newline_L.Alignment = Element.ALIGN_CENTER;
h_newline_L.Font = new Font(bfChinese, 72, Font.BOLD + Font.NORMAL, BaseColor.BLUE);
h_newline_L.SetLeading(0.0f, 0.9f);
h_newline_L.Add(" ");
#endregion
try
{
#region 找RJ單
string sqlcommand = "select * from V_EDI_BACK_R02_T where RJ_NO=@po_no and site_no=@site_no";
object[] scaParam = { "@po_no", "NVarChar", RJ,
"@site_no", "NVarChar", siteno };
dt = sca.GetDataTable("EDI_DC", sqlcommand, scaParam, 0);
if (dt.Rows.Count == 0)
{
returnstring += "找不到資料，請確認RJ單號存在";
return(returnstring);
}
#endregion
#region 記錄log
sqlcommand = "insert into LGDC.dbo.RunLog (JobID,JobType,JobDate,JobCmd,Crt_date) "
+ " values(''," //JobID
+ "'手動產生PDF" //JobType
+ "',CONVERT(CHAR(10),GETDATE(),112)" //JobDate
+ ",'PDF_PO_Kota.exe " + RJ
+ "," + System.Security.Principal.WindowsIdentity.GetCurrent().Name
+ "," + Host_string
+ "," + IP_string //JobCmd
+ "',GETDATE()) "; //Crt_date
sca.Update("LGDC", sqlcommand, scaParam, 0);
#endregion
DataRow dr = dt.Rows[0];
FileKey = dr["RJ_NO"].ToString();
string FileKey_ = "";
PdfPCell pCell_tmp;
PdfPTable p;
int Border_type = Rectangle.BOTTOM_BORDER;
int Border_type_H = Rectangle.NO_BORDER;
int Font_Size = 10;
int Font_Size_8 = 9;
int Font_Size_7 = 7;
#region 預計實作功能 圖片及條碼
// string Img_url = @"C:\Fpxmart\logo.jpg";// 公司Logo
string Img_url = Environment.CurrentDirectory + "\\WMSADJ\\logo.jpg";
iTextSharp.text.Image jpg = iTextSharp.text.Image.GetInstance(new Uri(Img_url));
jpg.ScalePercent(60);
PdfPCell jpgcell = new PdfPCell(jpg);
jpgcell.Border = 0;
jpgcell.Colspan = 1;
jpgcell.Column.Alignment = Element.ALIGN_CENTER;
jpgcell.VerticalAlignment = Element.ALIGN_CENTER;
#endregion
int INNER_QTY = 0;
int QTY = 0;
// 分頁
int row_page = 16; //每頁幾筆
int row_Count_PDFNow = 0; //pdf第幾頁
int row_Count_Now = 0; //第幾筆
int Page = 0; //總頁碼
int pageEnd = 0; //結束頁
p = new PdfPTable(3);
foreach (DataRow drt in dt.Rows)
{
FileKey = drt["RJ_NO"].ToString();
#region 單據不同則產生單據
if (FileKey_ != FileKey)
{
if (FileKey_ != "")
{
doc.Add(p);
doc.Add(h_newline);
//尾頁
p = PageEnd(bfChinese, doc, h_newline, p, Font_Size);
//簽收
PageEndSign(bfChinese, row_Count_Now, row_page, pageEnd, Font_Size, p, Page, doc);
}
//頁碼重新計算
row_Count_PDFNow = 0;
row_Count_Now = 0;
Page = 0;
pageEnd = 0;
DataRow[] PageRows;
string expression;
expression = "RJ_NO = '" + FileKey + "'";
PageRows = dt.Select(expression);
int row_Count = PageRows.Length; //單號-總筆數
if (row_Count % row_page > 0)
{
Page = row_Count / row_page + 1;
}
else
{
Page = row_Count / row_page;
}
FileKey_ = FileKey;
if (FileKey_ != "")
{
doc.Close();
}
doc = new Document(PageSize.A4, 20, 15, 15, 15);
path = filepath + "\\RJ" + FileKey.Trim() + "_" + drt["SITE_NO"].ToString().Trim() + ".pdf";
if (path != "")
{
PdfWriter.GetInstance(doc, new FileStream(path, FileMode.Create));
returnstring += "PDF產生完成 " + path;
}
else
{
returnstring += FileKey.Trim() + " 路徑有誤，請重新產生";
return(returnstring);
}
doc.Open();
row_Count_Now = 0;
}
#endregion
row_Count_Now++;
row_Count_PDFNow++;
if ((row_Count_Now % row_page) == 1)
{
if (row_Count_Now > row_page)
{
doc.Add(p);
doc.Add(h_newline);
p = new PdfPTable(1);
p.WidthPercentage = 100;
pCell_tmp = new PdfPCell();
pCell_tmp = setTable_Cell(bfChinese, string.Format(@"{0} / {1} ", row_Count_PDFNow / row_page, Page)
, 0, Font_Size, Element.ALIGN_RIGHT, 1, 1);
p.AddCell(pCell_tmp);
doc.Add(p);
doc.NewPage();
}
#region 表頭
p = new PdfPTable(3);
p.WidthPercentage = 110;
pCell_tmp = setTable_Cell(bfChinese, "退貨通知單", 0, Font_Size + 4, Element.ALIGN_CENTER, 1, 1);
pCell_tmp.Colspan = 3;
pCell_tmp.PaddingTop = 7f;
p.AddCell(pCell_tmp);
//圖案
p.AddCell(jpgcell);
p.AddCell(setTable_Cell(bfChinese, string.Format("{0}越庫中心", drt["SITE_NO_DESCR"].ToString()), 0, Font_Size, Element.ALIGN_CENTER, 1, 1));
//條碼
h_Code.Clear();
h_Code.Alignment = Element.ALIGN_RIGHT;
h_Code.Font = new Font(bfCode, 11);//, Font.NORMAL,, BaseColor.BLACK);
h_Code.Add(string.Format("{0}", "*" + drt["RJ_NO"].ToString().Trim() + "*"));
pCell_tmp = new PdfPCell(h_Code);
pCell_tmp.Column.Alignment = Element.ALIGN_CENTER;
pCell_tmp.VerticalAlignment = Element.ALIGN_CENTER;
pCell_tmp.Colspan = 1;
pCell_tmp.Border = 0;
p.AddCell(pCell_tmp);
string strSD = DateTime.Parse(drt["ARRIVE_DATE"].ToString().Substring(0, 9)).ToString("yyyy/MM/dd");
string strED = DateTime.Parse(drt["CLOSE_DATE"].ToString().Substring(0, 9)).ToString("yyyy/MM/dd");
p.AddCell(setTable_Cell(bfChinese, string.Format(" 廠商：{0}", drt["VENDOR_NO"].ToString() + drt["VENDOR_NAME"].ToString()), Border_type_H, Font_Size, Element.ALIGN_LEFT, 1, 1, 7f));
p.AddCell(setTable_Cell(bfChinese, string.Format(" 通知日期：{0}", strSD), Border_type_H, Font_Size, Element.ALIGN_LEFT, 1, 1, 7f));
p.AddCell(setTable_Cell(bfChinese, string.Format(" 單號：{0}", drt["RJ_NO"]), Border_type_H, Font_Size, Element.ALIGN_LEFT, 1, 1, 7f));
p.AddCell(setTable_Cell(bfChinese, string.Format(" 列印時間：{0}", DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")), Border_type_H, Font_Size_8, Element.ALIGN_LEFT, 1, 1, 7f));
p.AddCell(setTable_Cell(bfChinese, string.Format("預計退貨日起迄：{0}", strSD + "~" + strED + "(" + drt["ARRIVE_START_TIME"].ToString().Trim() + "-" + drt["ARRIVE_END_TIME"].ToString().Trim() + ")"), Border_type_H, Font_Size_7, Element.ALIGN_LEFT, 1, 1, 7f));
p.AddCell(setTable_Cell(bfChinese, " 客服電話:(886)0800335888", Border_type_H, Font_Size_8, Element.ALIGN_LEFT, 1, 1, 7f));
doc.Add(p);
doc.Add(h_newline);
#endregion
#region 表身的頭
p = new PdfPTable(7);
p.WidthPercentage = 100;
// int[] widths = {5, 20, 25, 10, 10,10,20 };
float[] widths = { 0.5f, 1.5f, 2.5f, 1.5f, 0.8f, 1.2f, 1.2f };
p.SetWidths(widths);
p.AddCell(setTable_Cell_Bottom(bfChinese, " ", Border_type, Font_Size, Element.ALIGN_CENTER, 1, 1));
p.AddCell(setTable_Cell_Bottom(bfChinese, "貨號／條碼", Border_type, Font_Size, Element.ALIGN_CENTER, 1, 1));
p.AddCell(setTable_Cell_Bottom(bfChinese, "貨品名稱／規格", Border_type, Font_Size, Element.ALIGN_CENTER, 1, 1));
p.AddCell(setTable_Cell_Bottom(bfChinese, "退貨量(pcs)", Border_type, Font_Size, Element.ALIGN_RIGHT, 1, 1));
p.AddCell(setTable_Cell_Bottom(bfChinese, "實退量", Border_type, Font_Size, Element.ALIGN_RIGHT, 1, 1));
p.AddCell(setTable_Cell_Bottom(bfChinese, "單位", Border_type, Font_Size, Element.ALIGN_CENTER, 1, 1));
p.AddCell(setTable_Cell_Bottom(bfChinese, "備註", Border_type, Font_Size, Element.ALIGN_CENTER, 1, 1));
#endregion
}
INNER_QTY = Convert.ToInt32(drt["INNER_QTY"].ToString());
QTY = Convert.ToInt32(drt["QTY"].ToString());
#region 表身明細
//序號 Table
p.AddCell(setTable_Cell(bfChinese, "", Border_type_H, Font_Size, Element.ALIGN_CENTER, 1, 1)); //序號
p.AddCell(setTable_Cell(bfChinese, string.Format("{0}", drt["ITEM_NO"]), Border_type_H, Font_Size, Element.ALIGN_LEFT, 1, 1)); //貨號
p.AddCell(setTable_Cell_Bottom(bfChinese, string.Format("{0}", drt["NAME"]), Border_type_H, Font_Size, Element.ALIGN_LEFT, 1, 2)); //貨品名稱/規格
//p.AddCell(setTable_Cell_Bottom(string.Format("{0}", drt["RJT_QTY"]), Border_type_H, Font_Size, Element.ALIGN_RIGHT, 1, 2));//退貨量
p.AddCell(setTable_Cell_Bottom(bfChinese, string.Format("{0}", drt["QTY"]), Border_type_H, Font_Size, Element.ALIGN_RIGHT, 1, 2)); //退貨量
p.AddCell(setTable_Cell_Bottom(bfChinese, string.Format("{0}", ""), Border_type_H, Font_Size, Element.ALIGN_CENTER, 1, 2)); //實退量
p.AddCell(setTable_Cell_Bottom(bfChinese, string.Format("{0}", drt["EA_UNIT"]), Border_type_H, Font_Size, Element.ALIGN_CENTER, 1, 2)); //單位
p.AddCell(setTable_Cell_Bottom(bfChinese, string.Format("{0}", ""), Border_type_H, Font_Size, Element.ALIGN_CENTER, 1, 2)); //備註
p.AddCell(setTable_Cell_Bottom(bfChinese, string.Format("{0}", drt["ITEM_SEQ_NO"]), Border_type_H, Font_Size, Element.ALIGN_CENTER, 1, 1)); //序號
p.AddCell(setTable_Cell_Bottom(bfChinese, string.Format("{0}", drt["EA_BARCODE"]), Border_type_H, Font_Size, Element.ALIGN_LEFT, 1, 1));
#endregion
}
doc.Add(p);
doc.Add(h_newline);
//尾頁
p = PageEnd(bfChinese, doc, h_newline, p, Font_Size);
//簽收
PageEndSign(bfChinese, row_Count_Now, row_page, pageEnd, Font_Size, p, Page, doc);
}
catch (Exception err)
{
returnstring += "發生錯誤\n";
throw new Exception(err.Message);
}
finally
{
doc.Close();
}
return(returnstring);
}
//搜尋按鈕
private void btn_Search_Click(object sender, EventArgs e)
{
SetStatusString(4);
Cursor = Cursors.WaitCursor;
string Search_String = ""; //變動類型的查詢字串
string sqlcommand = "";
try
{
object[] scaParam = { "@supid", "NVarChar", txb_SupID.Text,
"@Bdate", "NVarChar", dtp_BDate.Text,
"@Edate", "NVarChar", dtp_EDate.Text,
"@POID", "NVarChar", txb_POID.Text + '%',
"@site_no", "NVarChar", txb_SiteNo.Text }; //傳遞變數
if (rdb_SelectPO.Checked)
{
#region 變動類型的查詢字串
//碼頭
switch (cmb_DockMode.SelectedIndex)
{
case 1: Search_String += " and Dock_Mode='A' "; break;
case 2: Search_String += " and Dock_Mode='M' "; break;
default: break;
}
//供應商
if (txb_SupID.Text != "")
{
Search_String += " and vendor_no = @supid";
}
//倉別
if (txb_SiteNo.Text.Length > 2)
{
Search_String += " and site_no =@site_no ";
}
#endregion
sqlcommand =
@"select
Arrive_date
,Site_no
,po_no
,vendor_no
,Dock_Mode_Descr
,Check1=0
,[rows]=count(1)
from v_T_PO
where 1=1
and Arrive_Date Between @Bdate and @Edate
and po_no like @POID "
+ Search_String
+ @" group by Arrive_date,site_no,po_no,vendor_no,Dock_MODE_Descr
order by site_no,po_no";
dt_POList = sca.GetDataTable("EDI_DC", sqlcommand, scaParam, 0);
gridControl2.DataSource = dt_POList;
SetStatusString(1);
}
else if (rdb_SelectRJ.Checked)
{
#region 變動類型的查詢字串
//供應商
if (txb_SupID.Text != "")
{
Search_String += " and vendor_no = @supid";
}
//倉別
if (txb_SiteNo.Text.Length > 2)
{
Search_String += " and site_no =@site_no ";
}
#endregion
sqlcommand =
@"select
Arrive_date
,Site_no
,RJ_NO as po_no
,vendor_no
,'' as Dock_Mode_Descr
,0 as Check1
,[rows]=count(1)
from V_EDI_BACK_R02_T
where 1=1
and Arrive_Date Between @Bdate and @Edate
and rj_no like @POID "
+ Search_String
+ @" group by Arrive_date,site_no,rj_no,vendor_no
order by site_no,rj_no";
dt_POList = sca.GetDataTable("EDI_DC", sqlcommand, scaParam, 0);
gridControl2.DataSource = dt_POList;
SetStatusString(1);
}
else if (rdb_SelectLOG.Checked)
{
#region 變動類型的查詢字串
//PO單號
if (txb_POID.Text != "")
{
Search_String += " and (b.ID like @POID or c.RJ_NO like @POID)";
}
//供應商編號
if (txb_SupID.Text != "")
{
Search_String += " and (substring(b.SELLER_STR_ID,1,4)=@supid or substring(c.VENDOR_NO,1,4)=@supid)";
}
//倉別
if (txb_SiteNo.Text.Length > 2)
{
Search_String += " and (b.BUYER_STR_ID=@site_no or c.SITE_NO=@site_no ";
}
#endregion
sqlcommand =
@"Select a.JobCmd, a.JobDate
FROM (
select JobCmd,JobDate
,PONO=CASE SUBSTRING(JobCmd,17,1)
WHEN 'P' THEN SUBSTRING(JobCmd,CHARINDEX('P',JobCmd,10),13)
WHEN '5' THEN SUBSTRING(JobCmd,CHARINDEX('5',JobCmd,10),13)
END
from RunLog
where JobType='手動產生PDF'
and Crt_Date BETWEEN @Bdate and @Edate) a
left join
edi.dbo.EDI_CRP_PO_HEADER b
on
a.PONO=b.ID
left JOIN
edi.dbo.V_EDI_BACK_R02_T c
ON
a.PONO=c.RJ_NO
where 1=1 "
+ Search_String
+ " order by a.JobDate desc";
dt_POList = sca.GetDataTable("LGDC", sqlcommand, scaParam, 0);
//XSC使用者名單
DataTable XSCUserList = sca.GetDataTable("EEPDC", "select XSC_UserID, HECName from xsc_menu_userlist where XSC_UserID!='' ", scaParam, 0);
//加入primaryKey
DataColumn[] keys_XSCUserList = new DataColumn[1];
keys_XSCUserList[0] = XSCUserList.Columns["XSC_UserID"];
XSCUserList.PrimaryKey = keys_XSCUserList;
DataTable dtLogResult = new DataTable();
dtLogResult.Columns.Add("Logcol_PONO", typeof(String));
dtLogResult.Columns.Add("Logcol_CrtDate", typeof(String));
dtLogResult.Columns.Add("Logcol_XSCUserID", typeof(String));
dtLogResult.Columns.Add("Logcol_IP", typeof(String));
dtLogResult.Columns.Add("Logcol_UserName", typeof(String));
foreach (DataRow dr in dt_POList.Rows)
{
string[] strlist = dr["JobCmd"].ToString().Split(',');
DataRow dr1 = dtLogResult.NewRow();
//PONO
dr1[0] = strlist[0].Replace("PDF_PO_Kota.exe ", "");
//CrtDate
dr1[1] = dr["JobDate"].ToString();
//XSCUserID
dr1[2] = strlist[2];
//IP
dr1[3] = strlist[3];
//UserName
string UserName = "";
DataRow[] dr_UserName = XSCUserList.Select("XSC_UserID='" + strlist[2].ToString() + "'");
if (dr_UserName.Length > 0)
{
UserName = dr_UserName[0][1].ToString();
}
dr1[4] = UserName;
dtLogResult.Rows.Add(dr1);
}
gridControl1.DataSource = dtLogResult;
SetStatusString(1);
}
}
catch (Exception e1)
{
MessageBox.Show(e1.Message);
}
finally
{
Cursor = Cursors.Default;
}
}
//新增儲位
private void btn_Act_NewInSlodId_Click(object sender, EventArgs e)
{
string slodid = txb_NewSlodId.Text,
itemno = txb_ItemNo.Text,
ErrMsg = "",
merlsysno = "", //效期序號
cusdidsysno = "", //營業所序號
Boxunit = ""; //箱入中文單位
bool IsNew = false;
#region 檢查資料正確性
string cmdstring =
@"select top 1 0 from slot_data where S_slod_id=@merdid";
object[] objPram1 = { "@merdid", SqlDbType.NVarChar.ToString(), slodid };
DataSet DS_OutT1 = sca.GetDataSet(Login_Server, cmdstring, objPram1, 0);
if (DS_OutT1.Tables[0].Rows.Count <= 0)
{
ErrMsg += "儲位不正確\n";
}
cmdstring =
@"select top 1 0 from mer_data where S_merd_id=@merdid";
object[] objPram2 = { "@merdid", SqlDbType.NVarChar.ToString(), itemno };
DataSet DS_OutT2 = sca.GetDataSet(Login_Server, cmdstring, objPram2, 0);
if (DS_OutT2.Tables[0].Rows.Count <= 0)
{
ErrMsg += "貨號不正確\n";
}
if (ErrMsg != "")
{
MessageBox.Show(ErrMsg);
return;
}
cmdstring =
@"select top 1 0 from slot_mer where S_slom_slodid=@slodid and S_slom_merdid=@merdid";
object[] objPram3 = { "@slodid", SqlDbType.NVarChar.ToString(), slodid,
"@merdid", SqlDbType.NVarChar.ToString(), itemno };
DataSet DS_OutT3 = sca.GetDataSet(Login_Server, cmdstring, objPram3, 0);
if (DS_OutT3.Tables[0].Rows.Count > 0)
{
ErrMsg += "已有儲位\n";
}
else
{
IsNew = true;
}
if (ErrMsg != "")
{
MessageBox.Show(ErrMsg);
return;
}
#endregion
#region 產生新儲位
if (IsNew == true)
{
#region 取得merl_sysno
//先新增
cmdstring =
@"Insert into mer_list
select top 1
L_merl_sysno=(select L_merd_sysno from mer_data where S_merd_id=@merdid),
S_merl_merpgroup='01',
S_merl_lotno='',
D_merl_expdate=getdate()+90,
S_merl_supdid='',
S_merl_sbuid='1',
I_merl_mersid=10,
D_merl_creatdate=GETDATE(),
D_merl_recdate=NULL
from mer_list
where L_merl_merdsysno=(select L_merd_sysno from mer_data where S_merd_id=@merdid)
and Not exists (select top 1 *
from mer_list
where L_merl_merdsysno=(select L_merd_sysno from mer_data where S_merd_id=@merdid)
and S_merl_sbuid='1')" ;
sca.Update(Login_Server, cmdstring, objPram3, 0);
//再取得
cmdstring = @"select top 1 L_merl_sysno
from mer_list
where L_merl_merdsysno=(select L_merd_sysno from mer_data where S_merd_id=@merdid)
and S_merl_sbuid='1'" ;
DataTable Dt_OutT4 = sca.GetDataTable(Login_Server, cmdstring, objPram3, 0);
if (Dt_OutT4.Rows.Count > 0)
{
merlsysno = Dt_OutT4.Rows[0][0].ToString();
}
else
{
merlsysno = "0";
}
#endregion
#region 取得營業所sysno
cmdstring = @"select b.L_cusd_sysno
from customer_chuteslod a
inner join customer_data b on a.S_cusc_cusdid=b.S_cusd_id
where S_cusc_outslodid=@slodid";
DataTable Dt_OutT5 = sca.GetDataTable(Login_Server, cmdstring, objPram3, 0);
if (Dt_OutT5.Rows.Count > 0)
{
cusdidsysno = Dt_OutT5.Rows[0][0].ToString();
}
else
{
cusdidsysno = "0";
}
#endregion
#region 取得 unit
cmdstring = @"select S_merp_unit
from mer_package
where S_merp_merdid=@merdid
and I_merp_boxflag=1" ;
DataTable Dt_OutT6 = sca.GetDataTable(Login_Server, cmdstring, objPram3, 0);
if (Dt_OutT6.Rows.Count > 0)
{
Boxunit = Dt_OutT6.Rows[0][0].ToString();
}
else
{
Boxunit = "箱";
}
#endregion
//新增
cmdstring = @"Insert Into slot_mer
(L_slom_merdsysno,
L_slom_merlsysno,
S_slom_slodid,
S_slom_merdid,
s_slom_owndid,
L_slom_1qty,
L_slom_reserqty,
L_slom_replqty,
L_slom_qty1,
L_slom_qty2,
L_slom_qty3,
S_slom_unit1,
S_slom_unit2,
S_slom_unit3,
L_slom_listid,
T_slom_recidate,
L_slom_recsid,
T_slom_creatdate,
T_slom_updatedate,
F_slom_invbulk,
F_slom_wei,
S_slom_container,
L_slom_jobid,
S_slom_empdid,
S_slom_upempdid)
select
L_slom_merdsysno=L_merd_sysno
,L_slom_merlsysno=@merlsysno
,S_slom_slodid=@slod_id
,S_slom_merdid=S_merd_id
,S_slom_owndid='PXMART'
,L_slom_1qty=0
,L_slom_reserqty=0
,L_slom_replqty=0
,L_slom_qty1=0
,L_slom_qty2=0
,L_slom_qty3=0
,S_slom_unit1=@Boxunit
,S_slom_unit1=''
,S_slom_unit1=''
,L_slom_listid=@cusdidsysno
,T_slom_recidate=getdate()
,L_slom_recsid=NULL
,T_slom_creatdate=getdate()
,T_slom_updatedate=getdate()
,F_slom_invbulk=0
,F_slom_wei=0
,S_slom_container=''
,L_slom_jobid=0
,S_slom_empdid='XSC61adj'
,S_slom_upempdid='XSC61adj'
from mer_data
where S_merd_id=@merd_id
and not EXISTS (select top 1 0 from slot_mer where S_slom_slodid=@slod_id and S_slom_merdid=@merd_id)";
object[] objPram5 = { "@slod_id", SqlDbType.NVarChar.ToString(), slodid,
"@merd_id", SqlDbType.NVarChar.ToString(), itemno,
"@merlsysno", SqlDbType.NVarChar.ToString(), merlsysno,
"@cusdidsysno", SqlDbType.NVarChar.ToString(), cusdidsysno,
"@Boxunit", SqlDbType.NVarChar.ToString(), Boxunit };
sca.Update(Login_Server, cmdstring, objPram5, 0);
}
#endregion
btn_NewInSlodId.PerformClick();
StockOutTemp();
}
protected override void New_WSC_DLL_Form_Load(object sender, EventArgs e)
{
base.New_WSC_DLL_Form_Load(sender, e);
panel_IKnow.Size = new Size(844, 639);
panel_IKnow.Location = new Point(61, 38);
//取得此表單的LoaderFormInfo
fFormInfo = XSC.ClientLoader.FormInfo(this);
//透過LoginUserId取得sqlClientAccess
sca = XSC.ClientAccess.UserAccess.sqlUserAccess(fFormInfo.LoginUserId);
#region 帳號權限判斷
Boolean IsCorrectUser = false, IsPXWMS_N = false, IsPXWMS_S = false, IsPXWMS_C = false;
//取得工號
string cmdstring = "Select EMPID From XSC_Menu_Userlist where XSC_UserID=@userid";
object[] objParam = { "@userid", SqlDbType.NVarChar.ToString(), fFormInfo.LoginUserId };
DataTable dt_87 = sca.GetDataTable("EEPDC", cmdstring, objParam, 0);
if (dt_87.Rows.Count > 0)
{
IsCorrectUser = true;
UserWorkNum = dt_87.Rows[0][0].ToString();
}
if (UserWorkNum != "")
{
//取得兩倉權限
//觀音
cmdstring = "select top 1 0 from employee_data where S_empd_id=@userid";
object[] objParam1 = { "@userid", SqlDbType.NVarChar.ToString(), UserWorkNum };
DataTable dt_PXWMS_N = sca.GetDataTable("PXWMS_N", cmdstring, objParam1, 0);
if (dt_PXWMS_N.Rows.Count > 0)
{
IsPXWMS_N = true;
}
//岡山
DataTable dt_PXWMS_S = sca.GetDataTable("PXWMS_S", cmdstring, objParam1, 0);
if (dt_PXWMS_S.Rows.Count > 0)
{
IsPXWMS_S = true;
}
//梧棲
DataTable dt_PXWMS_C = sca.GetDataTable("PXWMS_C_Plus2", cmdstring, objParam1, 0);
if (dt_PXWMS_C.Rows.Count > 0)
{
IsPXWMS_C = true;
}
}
if (IsPXWMS_N == false && IsPXWMS_S == false && IsPXWMS_C == false)
{
IsCorrectUser = false;
}
#endregion
if (!IsCorrectUser)
{
MessageBox.Show("帳號無法對應三倉權限，請洽資訊部人員", ":<");
SetMasterBindingNavigator(null);
SetButtonEnable("L");
return;
}
txb_ItemNo.Text = txb_Merdid.Text;
SetMasterBindingNavigator(BS_Repl);
SetButtonEnable("FL");
if (IsPXWMS_N)
{
comboBox1.Items.Add(new Item("觀音", "PXWMS_N"));
}
if (IsPXWMS_S)
{
comboBox1.Items.Add(new Item("岡山", "PXWMS_S"));
}
if (IsPXWMS_C)
{
comboBox1.Items.Add(new Item("梧棲", "PXWMS_C_Plus2"));
}
comboBox1.Items.Add(new Item("測試", "PXWMS_200"));
comboBox1.SelectedIndex = 0;
cB_SFCkind.Items.Add(new Item("SFCXX01", "SFC%01"));
cB_SFCkind.Items.Add(new Item("SFCXX02", "SFC%02"));
cB_SFCkind.Items.Add(new Item("出貨暫存", "%%"));
cB_SFCkind.Items.Add(new Item("任意儲位", "%%"));
cB_SFCkind.SelectedIndex = 0;
ResizeForm.ResizeForm.WSC_Resize(this, 1);
gridControl1.DataSource = BS_Repl;
DevExpressGridFunctions.GridviewSetup(gridView1);
DevExpressGridFunctions.GridviewSetup(gridView2);
}
static public string CreatPdf_Prod(XSC.ClientAccess.sqlClientAccess sca, string RJ, string siteno, string Host_string, string IP_string, string Mainfilepath)
{
string returnstring = "";
string filepath = Mainfilepath;
string path; //檔案路徑
DataTable dt = new DataTable();
try
{
#region 找PO單
string sqlcommand =
@"select
SITE_NO_DESCR
,RJ_NO
,VENDOR_NO=VENDOR_NO+VENDOR_NAME
,ARRIVE_DATE=convert(varchar,ARRIVE_DATE,111)
,ARRIVE_START_DATE=convert(varchar,ARRIVE_DATE,111)+'~'+ convert(varchar,CLOSE_DATE,111)
,ARRIVE_START_TIME='('+ARRIVE_START_TIME+'-'+ARRIVE_END_TIME+')'
,ITEM_SEQ_NO
,ITEM_NO
,EA_BARCODE
,NAME
,QTY
,EA_UNIT
from V_EDI_BACK_R02_T
where RJ_NO=@po_no and site_no=@site_no";
object[] scaParam = { "@po_no", "NVarChar", RJ,
"@site_no", "NVarChar", siteno };
dt = sca.GetDataTable("EDI_DC", sqlcommand, scaParam, 0);
dt.TableName = "V_EDI_BACK_R02_T";
if (dt.Rows.Count == 0)
{
returnstring += "找不到資料，請確認RJ單號存在";
return(returnstring);
}
#endregion
XtraReport_RJ xReport1 = new XtraReport_RJ();
xReport1.DataSource = dt;
xReport1.DataMember = dt.TableName;
path = filepath + "\\" + "RJ" + RJ + "_" + siteno + ".pdf";
if (path != "")
{
xReport1.ExportToPdf(path);
#region 記錄log
sqlcommand = "insert into RunLog (JobID,JobType,JobDate,JobCmd,Crt_date) "
+ " values(''," //JobID
+ "'手動產生PDF" //JobType
+ "',CONVERT(CHAR(10),GETDATE(),112)" //JobDate
+ ",'PDF_PO_Kota.exe " + RJ
+ "," + System.Security.Principal.WindowsIdentity.GetCurrent().Name
+ "," + Host_string
+ "," + IP_string //JobCmd
+ "',GETDATE()) "; //Crt_date
sca.Update("LGDC", sqlcommand, scaParam, 0);
#endregion
returnstring += "PDF產生完成 " + path;
}
else
{
returnstring += RJ + " 路徑有誤，請重新產生";
}
}
catch (Exception err)
{
returnstring += "發生錯誤\n" + err.Message + "\n";
//throw new Exception(err.Message);
}
finally
{
}
return(returnstring);
}
private void button2_Click(object sender, EventArgs e)
{
string ErrMsg = "";
string site_no = comboBox1.SelectedItem.ToString().Split(',')[0];
SaveFileDialog OFD = new SaveFileDialog();
OFD.InitialDirectory = "D:\\";
OFD.RestoreDirectory = true;
OFD.Title = "儲存檔案";
OFD.DefaultExt = "xlsx";
OFD.Filter = "Microsoft Office Excel 活頁簿 (*.xlsx)|*.xlsx";
//準備輸出
string FileName = "廠商盤點_試算盤點結果表" + dateTimePicker1.Value.ToString("yyyyMMdd") + "_" + site_no;
#region 檢查檔案是否能寫入
OFD.FileName = FileName;
OFD.ShowDialog();
bool IsFileLocked = false;
if (File.Exists(OFD.FileName))
{
FileInfo filepath = new FileInfo(OFD.FileName);
FileStream steam = null;
try
{
steam = filepath.Open(FileMode.Open, FileAccess.ReadWrite, FileShare.None);
}
catch (IOException e1)
{
ErrMsg = e1.Message;
IsFileLocked = true;
}
finally
{
if (steam != null)
{
steam.Close();
}
}
}
if (IsFileLocked)
{
MessageBox.Show("檔案被鎖定，請檢查是否有其他程式正在開啟" + Environment.NewLine + ErrMsg, "匯出失敗");
return;
}
#endregion
DateTime timeStart = DateTime.Now;
try
{
string qry =
@"select *
from 廠商盤點_試算盤點結果表 with(nolock)
where (關貿庫存量!=0 or WMS總庫存!=0 or 總進貨量!=0)
and [倉別]=@site_no
order by 倉別, 廠商編號, 貨號";
dt_OutPut = sca.GetDataTable("LGDC", qry, new object[] { "@site_no", SqlDbType.VarChar, site_no }, 0);
Microsoft.Office.Interop.Excel.Application objXL = null;
Microsoft.Office.Interop.Excel.Workbook objWB = null;
objXL = new Microsoft.Office.Interop.Excel.Application();
objXL.Visible = false;
objXL.DisplayAlerts = false;
objWB = objXL.Workbooks.Add(1);
object useDefaultValue = Type.Missing;
Microsoft.Office.Interop.Excel.Worksheet objSHT = (Worksheet)objWB.Sheets.Add(useDefaultValue, useDefaultValue, useDefaultValue, useDefaultValue);
//Column
for (int col = 1; col <= dt_OutPut.Columns.Count; col++)
{
Range range = objSHT.get_Range(GetExcelColumnName(col) + "1", GetExcelColumnName(col) + "1");
range.Value2 = dt_OutPut.Columns[col - 1].ColumnName;
}
//row
for (int row = 1; row <= dt_OutPut.Rows.Count; row++)
{
for (int col = 1; col <= dt_OutPut.Columns.Count; col++)
{
Range range = objSHT.get_Range(GetExcelColumnName(col) + (row + 1).ToString(), GetExcelColumnName(col) + (row + 1).ToString());
range.NumberFormat = "@";
range.Value2 = dt_OutPut.Rows[row - 1][col - 1].ToString();
}
}
objWB.Close(true, OFD.FileName, null);
objXL.Quit();
}
catch { }
TimeSpan timespan1 = DateTime.Now - timeStart;
MessageBox.Show("檔案匯出至 " + OFD.FileName + " 完畢"
+ Environment.NewLine
+ "花費 " + timespan1.TotalSeconds.ToString() + " 秒"
, "匯出成功");
}
static public string CreatPdf_Prod(XSC.ClientAccess.sqlClientAccess sca, string PO, string siteno, string Host_string, string IP_string, string Mainfilepath)
{
string returnstring = "";
string filepath = Mainfilepath;
string path; //檔案路徑
DataTable dt = new DataTable();
try
{
#region 找PO單
string sqlcommand =
@"
select
SITE_NO_Descr=SITE_NO_Descr+'['+DOCK_MODE_Descr+']',
PO_NO,
VENDOR_NO=VENDOR_NO+VENDOR_Name,
ARRIVE_DATE,
ARRIVE_START_TIME=ARRIVE_START_TIME+'-'+ARRIVE_END_TIME,
RECEIVE_PLACE_ADDR,
remark,
remark2,
SITE_NO,
ITEM_SEQ_NO,
ITEM_NO=ITEM_NO+CASE WHEN WARM_LAYER!='' THEN '('+WARM_LAYER+')' ELSE '' END,
NAME,
QTY,
IP_QTY,
WARM_LAYER,
EA_Barcode,
INNER_QTY,
MANUFACTURING_DAYS=CASE WHEN MANUFACTURING_DAYS=MATURITIES_DAYS THEN '無保存期限' ELSE Replace(MATURITIES_DAYS,'/','') END,
MATURITIES_DAYS=CASE WHEN MANUFACTURING_DAYS=MATURITIES_DAYS THEN '' ELSE Replace(MATURITIES_DAYS,'/','') END,
IP_Barcode,
CS_Barcode,
Packing_memo,
Carton_QTY=convert(varchar,Carton_QTY)+CS_UNIT,
VENDOR_ITEM_NO,
TI=convert(varchar,TI)+'底/'+convert(varchar,HI)+'高',
TotalCase
from v_T_PO
where PO_NO=@po_no
and site_no=@site_no";
object[] scaParam = { "@po_no", "NVarChar", PO,
"@site_no", "NVarChar", siteno };
dt = sca.GetDataTable("EDI_DC", sqlcommand, scaParam, 0);
dt.TableName = "v_T_PO";
if (dt.Rows.Count == 0)
{
returnstring += "找不到資料，請確認PO單號存在";
return(returnstring);
}
#endregion
XtraReport_PO xReport1 = new XtraReport_PO();
xReport1.DataSource = dt;
xReport1.DataMember = dt.TableName;
path = filepath + "\\" + PO + "_" + siteno + ".pdf";
if (path != "")
{
xReport1.ExportToPdf(path);
#region 記錄log
sqlcommand = "insert into RunLog (JobID,JobType,JobDate,JobCmd,Crt_date) "
+ " values(''," //JobID
+ "'手動產生PDF" //JobType
+ "',CONVERT(CHAR(10),GETDATE(),112)" //JobDate
+ ",'PDF_PO_Kota.exe " + PO
+ "," + System.Security.Principal.WindowsIdentity.GetCurrent().Name
+ "," + Host_string
+ "," + IP_string //JobCmd
+ "',GETDATE()) "; //Crt_date
sca.Update("LGDC", sqlcommand, scaParam, 0);
#endregion
returnstring += "PDF產生完成 " + path;
}
else
{
returnstring += PO + " 路徑有誤，請重新產生";
}
}
catch (Exception err)
{
returnstring += "發生錯誤\n" + err.Message + "\n";
//throw new Exception(err.Message);
}
finally
{
}
return(returnstring);
}
protected override void New_WSC_DLL_Form_Load(object sender, EventArgs e)
{
base.New_WSC_DLL_Form_Load(sender, e);
//批次的解說文
toolTip1.SetToolTip(txb_SupdId, "輸入完供應商代號, 按下Tab");
cmb_BatchID.SelectedIndex = 0;
//取得此表單的LoaderFormInfo
fFormInfo = XSC.ClientLoader.FormInfo(this);
//透過LoginUserId取得sqlClientAccess
sca = XSC.ClientAccess.UserAccess.sqlUserAccess(fFormInfo.LoginUserId);
#region 帳號權限判斷
Boolean IsCorrectUser = false;
bool IsPXWMS_N = false, IsPXWMS_S = false, IsPXWMS_C = false;
//取得工號
string cmdstring = "Select EMPID From XSC_Menu_Userlist where XSC_UserID=@userid";
object[] objParam = { "@userid", SqlDbType.NVarChar.ToString(), fFormInfo.LoginUserId };
DataTable dt_87 = sca.GetDataTable("EEPDC", cmdstring, objParam, 0);
if (dt_87.Rows.Count > 0)
{
IsCorrectUser = true;
UserWorkNum = dt_87.Rows[0][0].ToString();
}
if (UserWorkNum != "")
{
//取得兩倉權限
//觀音
cmdstring = "select top 1 0 from employee_data where S_empd_id=@userid";
object[] objParam1 = { "@userid", SqlDbType.NVarChar.ToString(), UserWorkNum };
DataTable dt_PXWMS_N = sca.GetDataTable("PXWMS_N", cmdstring, objParam1, 0);
if (dt_PXWMS_N.Rows.Count > 0)
{
IsPXWMS_N = true;
}
//岡山
DataTable dt_PXWMS_S = sca.GetDataTable("PXWMS_S", cmdstring, objParam1, 0);
if (dt_PXWMS_S.Rows.Count > 0)
{
IsPXWMS_S = true;
}
//梧棲
DataTable dt_PXWMS_C = sca.GetDataTable("PXWMS_C_Plus2", cmdstring, objParam1, 0);
if (dt_PXWMS_C.Rows.Count > 0)
{
IsPXWMS_C = true;
}
}
if (IsPXWMS_N == false && IsPXWMS_S == false && IsPXWMS_C == false)
{
IsCorrectUser = false;
}
#endregion
if (!IsCorrectUser)
{
MessageBox.Show("帳號無法對應三倉權限，請洽資訊部人員", ":<");
SetMasterBindingNavigator(null);
SetButtonEnable("L");
return;
}
#region 設定新刪修會用到的物件
List <object> Enable_objlist = new List <object>();
Enable_objlist.Add(gridControl1);
this.MasterObj = Enable_objlist;
#endregion
SetMasterBindingNavigator(BS_Repl);
SetButtonEnable("FL EX help");
#region 限對應三倉
bindingNavigatorEditButton.Text = "重新產生資料(&E)";
if (IsPXWMS_N)
{
cmb_SiteNo.Items.Add(new Item("觀音", "PXWMS_N"));
}
if (IsPXWMS_S)
{
cmb_SiteNo.Items.Add(new Item("岡山", "PXWMS_S"));
}
if (IsPXWMS_C)
{
cmb_SiteNo.Items.Add(new Item("梧棲", "PXWMS_C_Plus2"));
}
cmb_SiteNo.SelectedIndex = 0;
#endregion
ResizeForm.ResizeForm.WSC_Resize(this, 1);
//gridControl1.DataSource = BS_Repl;
dateTimePicker1.Value = DateTime.Now.AddDays(3);
DevExpressGridFunctions.GridviewSetup(gridView1);
DevExpressGridFunctions.GridviewSetup(gridView2);
DevExpressGridFunctions.GridviewSetup(gridView3);
btn_Close_panel1.ForeColor = Color.White;
btn_Close_panel1.BackColor = Color.Red;
Lbl_UserId.Text = fFormInfo.LoginUserId;
Lbl_WorkNum.Text = UserWorkNum;
Lbl_Version.Text = "版本：" + FileVersionInfo.GetVersionInfo(Assembly.GetExecutingAssembly().Location).FileVersion.ToString();
#region 獲取廠商清單
DataTable dt_VendorList = GetSupdId();
foreach (DataRow dr in dt_VendorList.Rows)
{
cmb_VendorNo.Items.Add(new Item(dr["NewAlias"].ToString(), dr["NewAlias"].ToString()));
}
#endregion
}