/// ///////////////////////////////////////////////////////////////呈穎 public DataTable GetPurchasingOrderDetailByPurchasingOrderID(string PurchasingOrderID) { string strCmd = @"select pod.PurchasingOrderDetailListOID as '採購單明細識別碼',p.PartNumber as '料件編號', p.PartName as '料件品名', si.SupplierName as '供應商名稱', sl.Batch as '批量', p.UnitPrice AS '批量單價', pod.Qty as '採購數量', sl.Discount*sl.Batch*p.UnitPrice*pod.Qty AS '小計' from PurchasingOrderDetail pod JOIN [SourceList] sl ON pod.SourceListOID = sl.SourceListOID and pod.PurchasingOrderID = @PurchasingOrderID JOIN [Part] p ON sl.PartNumber = p.PartNumber JOIN [SupplierInfo] si ON si.SupplierCode = p.SupplierCode where not EXISTS ( select * from SourceOrderList sol where sol.PurchasingOrderDetailListOID = pod.PurchasingOrderDetailListOID )"; List <SqlParameter> parameters = new List <SqlParameter>(); parameters.Add(SqlHelper.CreateParameter("@PurchasingOrderID", SqlDbType.VarChar, 14, PurchasingOrderID)); DataTable dt = SqlHelper.AdapterFill(strCmd, parameters); if (dt.Rows.Count == 0) { return(null); } return(dt); }
public DataTable GetSourceList(string SupplierName, string PartName, string PartNumber) //設定條件 { //條件敘述 string strCmd = @"select SourceListOID,SupplierName,sup.SupplierCode,PartName,s.PartNumber,PartUnitName,UnitPrice,Batch,Discount,DiscountBeginDate,DiscountEndDate,PartSpec,CreateDate from Part p join SourceList s on s.PartNumber=p.PartNumber join [dbo].[SupplierInfo]sup on p.SupplierCode = sup.SupplierCode join [dbo].[PartUnit]px on p.PartUnitOID=px.PartUnitOID where (s.PartNumber = @PartNumber and PartName=@PartName)or SupplierName = @SupplierName"; //設定參數 List <SqlParameter> parameters = new List <SqlParameter>(); /*or SupplierName = @SupplierName*/ parameters.Add(SqlHelper.CreateParameter("@PartNumber", SqlDbType.NVarChar, 10, PartNumber)); parameters.Add(SqlHelper.CreateParameter("@PartName", SqlDbType.NVarChar, 30, PartName)); parameters.Add(SqlHelper.CreateParameter("@SupplierName", SqlDbType.NVarChar, 30, SupplierName)); DataTable dt = SqlHelper.AdapterFill(strCmd, parameters); if (dt.Rows.Count == 0) //如果沒有傳筆數 return null { return(null); } return(dt); }
public DataTable FineSourceListForPOAddMain(List <int> sourceListOid) { List <SqlParameter> parameters = new List <SqlParameter>(); StringBuilder sb = new StringBuilder(); sb.Append(@"select sl.SourceListOID, sl.PartNumber as '料件編號', p.PartName as '料件品名', si.SupplierName as '供應商名稱', Batch as '批量', (p.UnitPrice * sl.Discount * Batch) as '批量單價' from SourceList sl join Part p on sl.PartNumber = p.PartNumber join SupplierInfo si on si.SupplierCode = p.SupplierCode where SourceListOID in ("); for (int i = 0; i < sourceListOid.Count; i++) { if (i < sourceListOid.Count - 1) { sb.Append($"@SourceListOID{i}, "); } else { sb.Append($"@SourceListOID{i}"); } parameters.Add(SqlHelper.CreateParameter($"@SourceListOID{i}", SqlDbType.Int, sourceListOid[i])); } sb.Append(")"); DataTable dt = SqlHelper.AdapterFill(sb.ToString(), parameters); if (dt.Rows.Count == 0) { return(null); } return(dt); }
//仰1 public static DataTable getAllBuyerAccount() { string strCmd = @"select [AccountStatus] as '啟用狀態', emp.[EmployeeID] as '員工帳號', [Name] as '姓名',[Email] as '電子信箱', [Mobile] as '手機', [Tel] as '市話' from [dbo].[Buyer] as b join [dbo].[Employee] as emp on emp.EmployeeID = b.EmployeeID"; DataTable dt = SqlHelper.AdapterFill(strCmd, CommandType.Text); return(dt); }
//查詢原始資料 public DataTable FindSupplierPersonAccount() { string strcmd = @"select si.SupplierName, sa.ContactName, sa.Email, sa.Mobile, sa.Tel from [dbo].[SupplierAccount] sa join [dbo].[SupplierInfo] si on sa.SupplierCode = si.SupplierCode where sa.SupplierAccountID = 'S000000001'"; DataTable dt = SqlHelper.AdapterFill(strcmd); return(dt); }
public DataTable TopTenSourceList() { string strcmd = @"select Top(9) s.[PartNumber]+'_'+cast([Batch] as nvarchar) as '項目', [Discount]*p.UnitPrice as '價格' from [dbo].[SourceList] s join Part p on p.PartNumber = s.PartNumber order by [DiscountBeginDate] desc"; DataTable dt = SqlHelper.AdapterFill(strcmd); return(dt); }
//============================================================================ //公司基本資料查詢 public DataTable FindSupplierCompany() { string strcmd = @"select sa.[SupplierAccountID],[SupplierName],[TaxID],si.[Tel],si.[Email],si.[Address] from [dbo].[SupplierInfo] si join [dbo].[SupplierAccount] sa on si.SupplierCode = sa.SupplierCode where sa.SupplierAccountID = 'S000000001'"; DataTable dt = SqlHelper.AdapterFill(strcmd); return(dt); }
public DataTable FindPartUnitName() { string strCmd = @"Select PartUnitName as'物料單位', PartUnitOID as'物料單位編號' from PartUnit"; List <SqlParameter> parameters = new List <SqlParameter>(); //parameters.Add(SqlHelper.CreateParameter("@PartUnitName", SqlDbType.NVarChar, 6, PartUnitName)); DataTable dt = SqlHelper.AdapterFill(strCmd, parameters); return(dt); }
public DataTable SupplierCreate(string supplierName, string taxID, string tel, string email, string addr, string rate) { DataTable dt = new DataTable(); int SupplierInfoOID = -1; using (Transactions tr = new Transactions(600)) { string strcmd = @"INSERT INTO [dbo].[SupplierInfo] ([SupplierCode],[SupplierName],[TaxID],[Email],[Tel],[Address],[SupplierRatingOID]) VALUES ((select 'S' + right(format((select MAX(SupplierInfoOID)+1 from SupplierInfo with (TABLOCKX)), '0000#'), 5)) ,@SupplierName ,@TaxID,@Email,@Tel,@Address, @SupplierRatingOID)"; List <SqlParameter> list = new List <SqlParameter>(); list.Add(SqlHelper.CreateParameter("@SupplierName", SqlDbType.NVarChar, 30, supplierName.Replace(" ", ""))); list.Add(SqlHelper.CreateParameter("@TaxID", SqlDbType.VarChar, 10, taxID.Replace(" ", ""))); list.Add(SqlHelper.CreateParameter("@Email", SqlDbType.VarChar, 64, email.Replace(" ", ""))); list.Add(SqlHelper.CreateParameter("@Tel", SqlDbType.VarChar, 30, tel.Replace(" ", ""))); list.Add(SqlHelper.CreateParameter("@Address", SqlDbType.NVarChar, 256, addr.Replace(" ", ""))); list.Add(SqlHelper.CreateParameter("@SupplierRatingOID", SqlDbType.Int, 1, rate.Replace(" ", ""))); try { tr.ExecuteNonQuery(strcmd, list); } catch (SqlException ex) { tr.RollbackTransactions(); throw ex; } catch (Exception) { tr.RollbackTransactions(); } try { SupplierInfoOID = Convert.ToInt32(tr.ExecuteScalar("SELECT IDENT_CURRENT ('dbo.SupplierInfo')", new List <SqlParameter>())); } catch (SqlException ex) { tr.RollbackTransactions(); throw ex; } catch (Exception) { tr.RollbackTransactions(); throw; } tr.commitTransaction(); } string cmd = "select * from SupplierInfo where SupplierInfoOID = @SupplierInfoOID"; List <SqlParameter> li = new List <SqlParameter>(); li.Add(SqlHelper.CreateParameter("@SupplierInfoOID", SqlDbType.NVarChar, 30, SupplierInfoOID)); dt = SqlHelper.AdapterFill(cmd, li, CommandType.Text); return(dt); }
public DataGridView checkOrderFormForPublicUse(DataGridView dataGridView1, string orderID) { //轉換FORM 需要 秀出 訂單資料 //====================================================== // 新增 兩個按鈕COLUMN //DataGridViewButtonColumn btn = new DataGridViewButtonColumn(); //btn.Name = "edit"; //btn.HeaderText = "編輯"; //btn.DefaultCellStyle.NullValue = "編輯"; //DataGridViewButtonColumn btn2 = new DataGridViewButtonColumn(); //btn2.Name = "delete"; //btn2.HeaderText = "刪除"; //btn2.DefaultCellStyle.NullValue = "刪除"; //dataGridView1.Columns.Add(btn); //dataGridView1.Columns.Add(btn2); //=========================================================== string strcmd = @"SELECT [OrderPart].PartNumber as '料件編號', [OrderPart].PartName as '料件品名', [SupplierInfo].SupplierName as '供應商名稱', [Order].DateRequired as '需求日期', [SourceOrderList].Batch as '批量', [OrderPart].UnitPrice * ([SourceOrderList].Discount) as '批量單價', [SourceOrderList].Qty as '採購數量', sum([SourceOrderList].Batch*[OrderPart].UnitPrice * ([SourceOrderList].Discount)*[SourceOrderList].Qty) AS '小計', SourceOrderListOID FROM [order] JOIN [OrderPart] ON [OrderPart].OrderID = [Order].OrderID join [SourceOrderList] on [SourceOrderList].OrderPartOID = [OrderPart].OrderPartOID JOIN [SupplierInfo] ON [SupplierInfo].SupplierCode = [Order].SupplierCode group by [Order].OrderID, [OrderPart].PartNumber, [SupplierInfo].SupplierName, [Order].DateRequired, [SourceOrderList].Batch, [OrderPart].UnitPrice * ([SourceOrderList].Discount) , [OrderPart].PartName, [SourceOrderList].Qty, SourceOrderListOID HAVING [order].OrderID = @orderID" ; //orderID = "20190912000001"; List <SqlParameter> sqlParameters = new List <SqlParameter>(); sqlParameters.Add(SqlHelper.CreateParameter("@orderID", SqlDbType.VarChar, 14, orderID)); DataTable a = SqlHelper.AdapterFill(strcmd, sqlParameters); dataGridView1.DataSource = a; dataGridView1.Columns[8].Visible = false; return(dataGridView1); }
public static DataTable getDataOfSupCodenotInSupAcc() { string strCmd = @"select supInfo.[SupplierName] +' ('+ supInfo.[SupplierCode] + ')', supInfo.[SupplierCode] + ' (' + supInfo.[SupplierName] + ')', supInfo.[SupplierCode] from [dbo].[SupplierInfo] supInfo full outer join [dbo].[SupplierAccount] sa on supInfo.[SupplierCode] = sa.[SupplierCode] where supInfo.[SupplierCode] is null or sa.[SupplierCode] is null"; DataTable dt = SqlHelper.AdapterFill(strCmd, CommandType.Text); return(dt); }
public List <PurchasingOrderDetail> FindAll() { string strCmd = @"SELECT PurchasingOrderDetailListOID, PurchasingOrderID, SourceListOID, Qty FROM PurchasingOrderDetail"; DataTable dt = SqlHelper.AdapterFill(strCmd); List <PurchasingOrderDetail> pods = new List <PurchasingOrderDetail>(); foreach (DataRow dr in dt.Rows) { pods.Add(this.GetPurchasingOrderDetail(dr)); } return(pods); }
public DataTable FindSupplierCodeBySupplierName(string SupplierName) { string strCmd = @"Select [SupplierCode] as'供應商代碼' from SupplierInfo where SupplierName=@SupplierName"; List <SqlParameter> parameters = new List <SqlParameter>(); parameters.Add(SqlHelper.CreateParameter("@SupplierName", SqlDbType.NVarChar, 30, SupplierName)); DataTable dt = SqlHelper.AdapterFill(strCmd, parameters); return(dt); }
public List <PartUnit> FindAll() { string strCmd = @"SELECT PartUnitOID, PartUnitName FROM dbo.PartUnit"; DataTable dt = SqlHelper.AdapterFill(strCmd); List <PartUnit> pus = new List <PartUnit>(); foreach (DataRow dr in dt.Rows) { pus.Add(this.GetPartUnit(dr)); } return(pus); }
void setEmpData() { string strCmd = @"select * from [dbo].[Employee]"; DataTable dt = SqlHelper.AdapterFill(strCmd); PurchasingOrder.EmployeeDao d = new PurchasingOrder.EmployeeDao(); Employee emp = d.FindEmployeeByEmployeeID(loginEmployeeID); this.txtName.Text = emp.Name; this.txtEmail.Text = emp.Email; this.txtMobile.Text = emp.Mobile; this.txtTel.Text = emp.Tel; }
public static DataTable getAllSupplierAccount() { string strCmd = @"select [AccountStatus] as '啟用狀態', sinfo.SupplierName as '供應商名稱', sa.[SupplierCode] as '供應商代碼', [SupplierAccountID] as '供應商帳號', sa.[Email] as '電子信箱', [ContactName] as '聯絡人', [Mobile] as '手機', sa.[Tel] as '聯絡電話', sr.RatingName as '供應商等級' from [dbo].[SupplierAccount] as sa join [dbo].[SupplierInfo] as sinfo on sa.SupplierCode = sinfo.SupplierCode join [dbo].[SupplierRating] sr on sinfo.SupplierRatingOID = sr.SupplierRatingOID "; DataTable dt = SqlHelper.AdapterFill(strCmd, CommandType.Text); return(dt); }
public List <Model.Part> FindAll() { string strCmd = @"SELECT PartOID, PartNumber, PartName, PartSpec, SupplierCode, PartUnitOID, UnitPrice, CreatedDate, PictureAdress, PictureDescription FROM Part"; DataTable dt = SqlHelper.AdapterFill(strCmd); List <Model.Part> parts = new List <Model.Part>(); foreach (DataRow dr in dt.Rows) { parts.Add(this.GetPart(dr)); } return(parts); }
public List <Model.SourceList> FindAll() { string strCmd = @"SELECT SourceListOID, PartNumber, Batch, Discount, DiscountBeginDate, DiscountEndDate, CreateDate FROM SourceList"; DataTable dt = SqlHelper.AdapterFill(strCmd); List <Model.SourceList> sls = new List <Model.SourceList>(); foreach (DataRow dr in dt.Rows) { sls.Add(this.GetSourceList(dr)); } return(sls); }
public DataTable GetSupplierInfo() { string strCmd = @"select SupplierName,SupplierCode from SupplierInfo "; //設定參數 List <SqlParameter> parameters = new List <SqlParameter>(); /*or SupplierName = @SupplierName*/ DataTable dt = SqlHelper.AdapterFill(strCmd, parameters); if (dt.Rows.Count == 0) //如果沒有傳筆數 return null { return(null); } return(dt); }
public DataTable GetPartName()//抓料件名稱 { //條件敘述 string strCmd = @"select PartName from Part "; //設定參數 List <SqlParameter> parameters = new List <SqlParameter>(); /*or SupplierName = @SupplierName*/ DataTable dt = SqlHelper.AdapterFill(strCmd, parameters); if (dt.Rows.Count == 0) //如果沒有傳筆數 return null { return(null); } return(dt); }
public DataTable PurchasingECount(string employeeID) { string strcmd = @"select cast(DATEPART(YEAR,[RequestDate]) as nvarchar) +'/'+ right('00'+ cast(datepart(month,[RequestDate]) as nvarchar),2) as '年/月', count(cast(DATEPART(YEAR,[RequestDate]) as nvarchar) +'/'+ right('00'+ cast(datepart(month,[RequestDate]) as nvarchar),2)) as '數量' from [dbo].[OrderChanged] oc join [dbo].[Order] o on oc.[OrderID] = o.[OrderID] where [OrderChangedCategoryCode] = 'E' and o.[EmployeeID] = @EmployeeID group by cast(DATEPART(YEAR,[RequestDate]) as nvarchar) +'/'+ right('00'+ cast(datepart(month,[RequestDate]) as nvarchar),2)"; List <SqlParameter> lis = new List <SqlParameter>(); lis.Add(SqlHelper.CreateParameter("@EmployeeID", SqlDbType.VarChar, 10, employeeID)); DataTable dt = (SqlHelper.AdapterFill(strcmd, lis, CommandType.Text)); return(dt); }
public DataTable EveryMonthSum(string employeeID) { string strcmd = @"select SUM([UnitPrice]) as '當月銷售總金額', cast(DATENAME(year,DateReleased) as nvarchar) +'/'+ right('00'+ cast(DATEPART(month,DateReleased) as nvarchar),2) as '年/月' from [dbo].[OrderPart] op join [dbo].[Order] p on op.OrderID = p.OrderID where p.[EmployeeID] = @EmployeeID group by cast(DATENAME(year,DateReleased) as nvarchar) +'/'+ right('00'+ cast(DATEPART(month,DateReleased) as nvarchar),2) order by cast(DATENAME(year,DateReleased) as nvarchar) +'/'+ right('00'+ cast(DATEPART(month,DateReleased) as nvarchar),2)"; List <SqlParameter> lis = new List <SqlParameter>(); lis.Add(SqlHelper.CreateParameter("@EmployeeID", SqlDbType.VarChar, 10, employeeID)); DataTable dt = (SqlHelper.AdapterFill(strcmd, lis, CommandType.Text)); return(dt); }
public static DataTable SearchBuyerAccount(string txtEmpID, string txtEmpName) { List <SqlParameter> paramList = new List <SqlParameter>(); string strCmd = @"select [AccountStatus] as '啟用狀態', emp.[EmployeeID] as '員工帳號', [Name] as '姓名',[Email] as '電子信箱', [Mobile] as '手機', [Tel] as '市話' from [dbo].[Buyer] as b join [dbo].[Employee] as emp on emp.EmployeeID = b.EmployeeID where emp.[EmployeeID] = @EmployeeID or [Name] = @EmpName"; SqlParameter EmployeeID = SqlHelper.CreateParameter("@EmployeeID", SqlDbType.VarChar, 10, txtEmpID); SqlParameter EmpName = SqlHelper.CreateParameter("@EmpName", SqlDbType.NVarChar, 30, txtEmpName); paramList.Add(EmployeeID); paramList.Add(EmpName); DataTable dt = SqlHelper.AdapterFill(strCmd, paramList, CommandType.Text); return(dt); }
public List <SupplierRating> FindAll() { string strCmd = @"select SupplierRatingOID, RatingName from dbo.SupplierRating"; DataTable dt = SqlHelper.AdapterFill(strCmd); if (dt.Rows.Count == 0) { return(null); } List <SupplierRating> srs = new List <SupplierRating>(); foreach (DataRow dr in dt.Rows) { srs.Add(this.GetSupplierRating(dr)); } return(srs); }
/// //////////////////////////////////////////////////////////////////////呈穎 public DataTable GetBuyerName(string EmployeeID) { string strCmd = @"select * from Employee where EmployeeID = @EmployeeID"; List <SqlParameter> parameters = new List <SqlParameter>(); parameters.Add(SqlHelper.CreateParameter("@EmployeeID", SqlDbType.VarChar, 10, EmployeeID)); DataTable dt = SqlHelper.AdapterFill(strCmd, parameters); if (dt.Rows.Count == 0) { return(null); } return(dt); }
/// <summary> /// SearchEmployee Employee 資料表 /// </summary> /// <param name="txtEmpID"></param> /// <param name="AccountStatus"></param> /// <param name="Name"></param> /// <param name="Email"></param> /// <param name="Mobile"></param> /// <param name="Tel"></param> /// <returns></returns> public static DataTable SearchEmployee(string txtEmpID, out string EmployeeID, out string Name, out string Email, out string Mobile, out string Tel) { string strCmd = @"select emp.[EmployeeID] as '員工帳號', [Name] as '姓名',[Email] as '電子信箱', [Mobile] as '手機', [Tel] as '市話' from [dbo].[Employee] as emp where EmployeeID = @EmployeeID"; SqlParameter sEmployeeID = SqlHelper.CreateParameter("@EmployeeID", SqlDbType.VarChar, 10, txtEmpID); List <SqlParameter> paramList = new List <SqlParameter>(); paramList.Add(sEmployeeID); DataTable dt = SqlHelper.AdapterFill(strCmd, paramList, CommandType.Text); EmployeeID = (string)(dt.Rows[0])["員工帳號"]; Name = (string)(dt.Rows[0])["姓名"]; Email = (string)(dt.Rows[0])["電子信箱"]; Mobile = (string)(dt.Rows[0])["手機"]; Tel = (string)(dt.Rows[0])["市話"]; return(dt); }
public Model.OrderPart FindOrderPartByOrderPartOID(int OrderPartOID) { string strCmd = @"SELECT [OrderPartOID], [OrderID], [PartNumber], [PartName], [PartSpec], [PartUnitName], [UnitPrice] FROM [dbo].[OrderPart] where [OrderPartOID] = @OrderPartOID"; List <SqlParameter> parameters = new List <SqlParameter>(); parameters.Add(SqlHelper.CreateParameter("@OrderPartOID", SqlDbType.Int, OrderPartOID)); DataTable dt = SqlHelper.AdapterFill(strCmd, parameters); if (dt.Rows.Count == 0) { return(null); } Model.OrderPart op = this.GetOrderPart(dt.Rows[0]); return(op); }
public List <Model.OrderPart> FindAll() { string strCmd = @"SELECT [OrderPartOID], [OrderID], [PartNumber], [PartName], [PartSpec], [PartUnitName], [UnitPrice] FROM [dbo].[OrderPart]"; DataTable dt = SqlHelper.AdapterFill(strCmd); if (dt.Rows.Count == 0) { return(null); } List <Model.OrderPart> ops = new List <Model.OrderPart>(); foreach (DataRow dr in dt.Rows) { ops.Add(this.GetOrderPart(dr)); } return(ops); }
public Model.SourceOrderList FindSourceOrderListBySourceOrderListOID(int SourceOrderListOID) { string strCmd = @"SELECT [SourceOrderListOID], [OrderPartOID], [Batch], [Discount], [Qty], [PurchasingOrderDetailListOID] FROM [dbo].[SourceOrderList] where [SourceOrderListOID] = @SourceOrderListOID"; List <SqlParameter> parameters = new List <SqlParameter>(); parameters.Add(SqlHelper.CreateParameter("@SourceOrderListOID", SqlDbType.Int, SourceOrderListOID)); DataTable dt = SqlHelper.AdapterFill(strCmd, parameters); if (dt.Rows.Count == 0) { return(null); } Model.SourceOrderList sol = this.GetSourceOrderList(dt.Rows[0]); return(sol); }
public List <Model.SourceOrderList> FindAll() { string strCmd = @"SELECT [SourceOrderListOID], [OrderPartOID], [Batch], [Discount], [Qty], [PurchasingOrderDetailListOID] FROM [dbo].[SourceOrderList]"; DataTable dt = SqlHelper.AdapterFill(strCmd); if (dt.Rows.Count == 0) { return(null); } List <Model.SourceOrderList> sols = new List <Model.SourceOrderList>(); foreach (DataRow dr in dt.Rows) { sols.Add(this.GetSourceOrderList(dr)); } return(sols); }