/// ///////////////////////////////////////////////////////////////呈穎
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);
}
