public void insertCKGPartOrder(CKGPartOrder p_code)
{
string l_strSql = "INSERT INTO [KG].[dbo].[tbKGPartOrder] ([f_ExchangeID],[f_Vendor],[f_EngineNo],[f_AssistantSmid],[f_SalesSmid],[f_branchid],[f_TotalCost],[f_TotalPrice],[f_IsSend],[f_Memo],[f_InsertDate],[f_InsertIP],[f_EditDate],[f_TotalSale])VALUES(";
l_strSql += " '" + p_code.f_ExchangeID請購單號 + "' ";
l_strSql += ", '" + p_code.f_Vendor請購廠商 + "' ";
l_strSql += ", '" + p_code.f_EngineNo引擎號碼 + "' ";
l_strSql += ", '" + p_code.f_AssistantSmid助理員編 + "' ";
l_strSql += ", '" + p_code.f_SalesSmid業代員編 + "' ";
l_strSql += ", '" + p_code.f_AssistantBranchid助理單位編號 + "' ";
l_strSql += ", '" + p_code.f_TotalCost總成本價 + "' ";
l_strSql += ", '" + p_code.f_TotalPrice總計價格 + "' ";
l_strSql += ", '" + p_code.f_IsSend是否發送 + "' ";
l_strSql += ", '" + p_code.f_Memo備註說明 + "' ";
l_strSql += ", '" + p_code.f_InsertDate請購日期 + "' ";
l_strSql += ", '" + p_code.f_InsertIP輸入電腦IP + "' ";
l_strSql += ", '" + p_code.f_EditDate編輯日期 + "' ";
l_strSql += ", " + p_code.f_TotalSale總販賣價 + " ";
l_strSql += ")";
ivContext.資料管理員.excuteSqlNonquery(l_strSql);
CKGPartOrderDetail[] l_codes = p_code.明細;
CKGPartOrderDetailFactory l_factory = new CKGPartOrderDetailFactory(ivContext);
l_factory.insertCKGPartOrderDetail(l_codes);
}
private CKGPartOrder[] queryBySql(string p_sql)
{
DataView l_dv = ivContext.資料管理員.getDataViewBySql(p_sql);
List <CKGPartOrder> l_datas = new List <CKGPartOrder>();
if (l_dv.Count > 0)
{
for (int i = 0; i < l_dv.Count; i++)
{
CKGPartOrder l_code = createCKGPartOrder();
l_code.f_ExchangeID請購單號 = l_dv[i]["f_ExchangeID"].ToString();
l_code.f_Vendor請購廠商 = l_dv[i]["f_Vendor"].ToString();
l_code.f_EngineNo引擎號碼 = l_dv[i]["f_EngineNo"].ToString();
l_code.f_AssistantSmid助理員編 = l_dv[i]["f_AssistantSmid"].ToString();
l_code.f_SalesSmid業代員編 = l_dv[i]["f_SalesSmid"].ToString();
l_code.f_TotalCost總成本價 = Convert.ToInt32(l_dv[i]["f_TotalCost"].ToString());
l_code.f_TotalPrice總計價格 = Convert.ToInt32(l_dv[i]["f_TotalPrice"].ToString());
l_code.f_Memo備註說明 = l_dv[i]["f_Memo"].ToString();
l_code.f_InsertDate請購日期 = l_dv[i]["f_InsertDate"].ToString();
l_code.f_InsertIP輸入電腦IP = l_dv[i]["f_InsertIP"].ToString();
l_code.f_EditDate編輯日期 = l_dv[i]["f_EditDate"].ToString();
l_code.f_Branchid請購單位 = l_dv[i]["f_branchid"].ToString();
l_code.f_TotalSale總販賣價 = Convert.ToInt32("0" + l_dv[i]["f_TotalSale"].ToString());
l_datas.Add(l_code);
}
return(l_datas.ToArray());
}
else
{
return(null);
}
}
/// <summary>
/// (Yu 20090507) 取得各所月報表的請購總金額
/// </summary>
/// <param name="p_str所別"></param>
/// <param name="p_str廠商"></param>
/// <param name="p_strStartDay"></param>
/// <param name="p_strEndDay"></param>
/// <returns></returns>
public CKGPartOrder[] get各所月報的請購總金額(string p_str所別, string p_str廠商, string p_strStartMonth, string p_strEndMonth, bool p_bool含直販)
{
string l_strSql = "SELECT Substring(f_ExchangeID,2,6) AS f_InsertDate,f_SalesBranch,Sum(f_TotalCost) AS f_TotalCost,Sum(f_TotalPrice) AS f_TotalPrice, ";
l_strSql += setReturnBranchid();
l_strSql += " FROM dbo.tbKGPartOrder WHERE 1=1";
if (!"".Equals(p_str所別))
{
l_strSql += " AND f_SalesBranch ='" + p_str所別 + "'";
}
if (!"".Equals(p_str廠商))
{
l_strSql += " AND f_Vendor ='" + p_str廠商 + "'";
}
if (!p_bool含直販)
{
l_strSql += " AND f_SalesBranchid not in ('FB50075')";
}
if (!"".Equals(p_strStartMonth) && "".Equals(p_strEndMonth))
{
l_strSql += " AND f_InsertDate >= '" + p_strStartMonth + "'";
}
else if ("".Equals(p_strStartMonth) && !"".Equals(p_strEndMonth))
{
l_strSql += " AND f_InsertDate <='" + p_strEndMonth + "'";
}
else if (!"".Equals(p_strStartMonth) && !"".Equals(p_strEndMonth))
{
l_strSql += " AND f_InsertDate BETWEEN '" + p_strStartMonth + "' AND '" + p_strEndMonth + "'";
}
l_strSql += " GROUP BY f_SalesBranch,Substring(f_ExchangeID,2,6) ";
l_strSql += " ORDER BY Substring(f_ExchangeID,2,6),Branchid ";
DataView l_dv = ivContext.資料管理員.getDataViewBySql(l_strSql);
List <CKGPartOrder> l_datas = new List <CKGPartOrder>();
if (l_dv.Count > 0)
{
for (int i = 0; i < l_dv.Count; i++)
{
CKGPartOrder l_code = createCKGPartOrder();
l_code.f_InsertDate請購日期 = l_dv[i]["f_InsertDate"].ToString();
l_code.f_SalesBranch業代單位 = l_dv[i]["f_SalesBranch"].ToString();
l_code.f_TotalCost總成本價 = Convert.ToInt32(l_dv[i]["f_TotalCost"].ToString());
l_code.f_TotalPrice總計價格 = Convert.ToInt32(l_dv[i]["f_TotalPrice"].ToString());
l_datas.Add(l_code);
}
return(l_datas.ToArray());
}
else
{
return(null);
}
}
public void update總計成本與總計價格By請購明細取消物品項目(CKGPartOrder p_code)
{
string l_strSql = "UPDATE [KG].[dbo].[tbKGPartOrder] SET ";
l_strSql += "[f_TotalCost] = '" + p_code.f_TotalCost總成本價 + "',";
l_strSql += "[f_TotalPrice] = '" + p_code.f_TotalPrice總計價格 + "',";
l_strSql += "[f_EditDate] = '" + p_code.f_EditDate編輯日期 + "'";
l_strSql += " WHERE f_ExchangeID = '" + p_code.f_ExchangeID請購單號 + "'";
ivContext.資料管理員.excuteSqlNonquery(l_strSql);
}
/// <summary>
/// (Yu 20090507) 取得各所日報表的請購總金額
/// </summary>
/// <param name="p_str所別"></param>
/// <param name="p_str廠商"></param>
/// <param name="p_strStartDay"></param>
/// <param name="p_strEndDay"></param>
/// <param name="p_bool含直販">Treu: 含直販課，False:不含直販課</param>
/// <returns></returns>
public CKGPartOrder[] get各所日報的請購總金額(string p_str所別, string p_str廠商, string p_strStartDay, string p_strEndDay)
{
string l_strSql = "SELECT f_branchid,f_InsertDate,Sum(f_TotalCost) AS f_TotalCost,Sum(f_TotalPrice) AS f_TotalPrice ,Sum(f_TotalSale) AS f_TotalSale";
//l_strSql += setReturnBranchid();
l_strSql += " FROM [KG].[dbo].[tbKGPartOrder] ";
l_strSql += " WHERE 1=1 ";
if (!"".Equals(p_str所別))
{
l_strSql += " AND f_branchid ='" + p_str所別 + "'";
}
if (!"".Equals(p_str廠商))
{
l_strSql += " AND f_Vendor ='" + p_str廠商 + "'";
}
if (!"".Equals(p_strStartDay) && "".Equals(p_strEndDay))
{
l_strSql += " AND f_InsertDate >='" + p_strStartDay + "'";
}
else if ("".Equals(p_strStartDay) && !"".Equals(p_strEndDay))
{
l_strSql += " AND f_InsertDate <='" + p_strEndDay + "'";
}
else if (!"".Equals(p_strStartDay) && !"".Equals(p_strEndDay))
{
l_strSql += " AND f_InsertDate BETWEEN '" + p_strStartDay + "' AND '" + p_strEndDay + "'";
}
l_strSql += " GROUP BY f_branchid,f_InsertDate";
l_strSql += " ORDER BY f_branchid,f_InsertDate";
DataView l_dv = ivContext.資料管理員.getDataViewBySql(l_strSql);
List <CKGPartOrder> l_datas = new List <CKGPartOrder>();
if (l_dv.Count > 0)
{
for (int i = 0; i < l_dv.Count; i++)
{
CKGPartOrder l_code = createCKGPartOrder();
l_code.f_SalesBranch業代單位 = l_dv[i]["f_branchid"].ToString();
l_code.f_InsertDate請購日期 = l_dv[i]["f_InsertDate"].ToString();
l_code.f_TotalCost總成本價 = Convert.ToInt32(l_dv[i]["f_TotalCost"].ToString());
l_code.f_TotalPrice總計價格 = Convert.ToInt32(l_dv[i]["f_TotalPrice"].ToString());
l_code.f_TotalSale總販賣價 = Convert.ToInt32("0" + l_dv[i]["f_TotalSale"].ToString());
l_datas.Add(l_code);
}
return(l_datas.ToArray());
}
else
{
return(null);
}
}
public void update訂購或點收後請購單的狀態(CKGPartOrder p_codes)
{
string l_strSql = " BEGIN ";
//for (int i = 0; i < p_codes.Length; i++)
//{
l_strSql += "UPDATE [KG].[dbo].[tbKGPartOrder] SET ";
l_strSql += "[f_IsSend] = 'True'";
l_strSql += " WHERE f_ExchangeID = '" + p_codes.f_ExchangeID請購單號 + "' ;";
//}
l_strSql += " END";
ivContext.資料管理員.excuteSqlNonquery(l_strSql);
}
/// <summary>
/// (Yu 20090424) 多載 取得條件為請購單號
/// </summary>
/// <param name="p_strExchengID">請購單號</param>
/// <param name="p_AssistantSmid">助理員編</param>
/// <returns></returns>
public CKGPartOrder getKGPartOrderBy請購單號(string p_strExchangeID, string p_AssistantSmid)
{
string l_strSql = "SELECT *,h.name,h.branch FROM [KG].[dbo].[tbKGPartOrder] o";
l_strSql += " left join kdnews..T_HRPersonel h on h.emp_id = o.f_SalesSmid ";
l_strSql += " WHERE f_ExchangeID = '" + p_strExchangeID + "' ";
if (!"".Equals(p_AssistantSmid))
{
l_strSql += " AND f_AssistantSmid ='" + p_AssistantSmid + "'";
}
DataView l_dv = ivContext.資料管理員.getDataViewBySql(l_strSql);
List <CKGPartOrder> l_datas = new List <CKGPartOrder>();
if (l_dv.Count > 0)
{
for (int i = 0; i < l_dv.Count; i++)
{
CKGPartOrder l_code = createCKGPartOrder();
l_code.f_ExchangeID請購單號 = l_dv[i]["f_ExchangeID"].ToString();
l_code.f_Vendor請購廠商 = l_dv[i]["f_Vendor"].ToString();
l_code.f_EngineNo引擎號碼 = l_dv[i]["f_EngineNo"].ToString();
l_code.f_AssistantSmid助理員編 = l_dv[i]["f_AssistantSmid"].ToString();
l_code.f_SalesSmid業代員編 = l_dv[i]["f_SalesSmid"].ToString();
l_code.f_SalesName業代姓名 = l_dv[i]["name"].ToString();
l_code.f_SalesBranch業代單位 = l_dv[i]["branch"].ToString();
l_code.f_TotalCost總成本價 = Convert.ToInt32(l_dv[i]["f_TotalCost"].ToString());
l_code.f_TotalPrice總計價格 = Convert.ToInt32(l_dv[i]["f_TotalPrice"].ToString());
l_code.f_TotalSale總販賣價 = Convert.ToInt32("0" + l_dv[i]["f_TotalSale"].ToString());
l_code.f_Memo備註說明 = l_dv[i]["f_Memo"].ToString();
l_code.f_InsertDate請購日期 = l_dv[i]["f_InsertDate"].ToString();
l_code.f_InsertIP輸入電腦IP = l_dv[i]["f_InsertIP"].ToString();
l_code.f_EditDate編輯日期 = l_dv[i]["f_EditDate"].ToString();
l_datas.Add(l_code);
}
}
else
{
return(null);
}
if (l_datas == null)
{
return(null);
}
else
{
return(l_datas.ToArray()[0]);
}
}
public void updateCKGPartOrder(CKGPartOrder p_code)
{
string l_strSql = "UPDATE [KG].[dbo].[tbKGPartOrder] SET ";
l_strSql += "[f_AssistantSmid] = '" + p_code.f_AssistantSmid助理員編 + "',";
l_strSql += "[f_AssistantName] = '" + p_code.f_AssistantName助理姓名 + "',";
l_strSql += "[f_AssistantBranch] = '" + p_code.f_AssistantBranch助理單位 + "',";
l_strSql += "[f_SalesSmid] = '" + p_code.f_SalesSmid業代員編 + "',";
l_strSql += "[f_SalesName] = '" + p_code.f_SalesName業代姓名 + "',";
l_strSql += "[f_SalesBranch] = '" + p_code.f_SalesBranch業代單位 + "',";
l_strSql += "[f_TotalCost] = '" + p_code.f_TotalCost總成本價 + "',";
l_strSql += "[f_TotalPrice] = '" + p_code.f_TotalPrice總計價格 + "',";
l_strSql += "[f_Status] = '" + p_code.f_Status請購狀態 + "',";
l_strSql += "[f_Memo] = '" + p_code.f_Memo備註說明 + "',";
l_strSql += "[f_InsertDate] = '" + p_code.f_InsertDate請購日期 + "',";
l_strSql += "[f_InsertIP] = '" + p_code.f_InsertIP輸入電腦IP + "',";
l_strSql += "[f_EditDate] = '" + p_code.f_EditDate編輯日期 + "'";
l_strSql += " WHERE f_ExchangeID = '" + p_code.f_ExchangeID請購單號 + "'";
ivContext.資料管理員.excuteSqlNonquery(l_strSql);
}
private string set訂單內容(CKGPartOrder p_codes)
{
//CKGPartOutOrderFactory l_factory訂單主檔 = new CKGPartOutOrderFactory(iv_context);
//CKGPartOutOrder l_code訂單主檔 = l_factory訂單主檔.get訂單資料By編號(p_strBID);
//CKGPartOutOrderDetailFactory l_factory訂單明細檔 = new CKGPartOutOrderDetailFactory(iv_context);
//CKGPartOutOrderDetail[] l_codes = l_factory訂單明細檔.get明細By訂單編號(p_strBID);
CKGPartOrderFactory l_factory主檔 = new CKGPartOrderFactory(ivContext);
CKGPartOrder l_code主檔 = new CKGPartOrder();
string l_strHtml = "";
l_code主檔 = p_codes;
l_code主檔.明細 = new CKGPartOrderDetailFactory(ivContext).get請購明細ByExchangeID(l_code主檔.f_ExchangeID請購單號);
l_strHtml += set頁首(l_code主檔.f_Vendor請購廠商, l_code主檔.f_Branchid請購單位, l_code主檔.f_Memo備註說明);
l_strHtml += 組Html(l_code主檔);
return(l_strHtml);
}
public void 設定Mail(string p_str請購單位, CKGPartOrder p_codes)
{
iv_strMailTo收件者 = "";
string iv_strMailTo收件者_備份 = "";
l_mail = new MailMessage();
if ("KC".Equals(p_codes.f_Vendor請購廠商) || "KCF".Equals(p_codes.f_Vendor請購廠商) || "KCF".Equals(p_codes.f_Vendor請購廠商) || "亙長".Equals(p_codes.f_Vendor請購廠商))
{
iv_strMailTo收件者 = "*****@*****.**";
iv_strMailTo收件者_備份 = "*****@*****.**";
}
else if ("PDS".Equals(p_codes.f_Vendor請購廠商) || "PDS".Equals(p_codes.f_Vendor請購廠商))
{
//iv_strMailTo收件者 = "*****@*****.**";
//l_mail.To.Add("*****@*****.**");
//l_mail.To.Add("*****@*****.**");
//l_mail.To.Add("*****@*****.**");
//l_mail.To.Add("*****@*****.**");
}
else if ("HM".Equals(p_codes.f_Vendor請購廠商) || "惠明".Equals(p_codes.f_Vendor請購廠商))
{
iv_strMailTo收件者 = "*****@*****.**";
}
else
{
iv_strMailTo收件者 = "*****@*****.**";
iv_strMailTo收件者_備份 = "*****@*****.**";
}
l_mail.From = new MailAddress(this.iv_strMailFrom, this.iv_MailToDisplayName);
l_mail.To.Add(iv_strMailTo收件者);
l_mail.Bcc.Add(iv_strMailTo收件者_備份);
l_mail.Bcc.Add("*****@*****.**");
l_mail.Subject = "小百貨訂貨單(" + DateTime.Today.ToString("yyyy/MM/dd") + ")由 " + get轉換據點中英文(p_str請購單位) + " 傳來，訂單編號：" + p_codes.f_ExchangeID請購單號 + " ，引擎號碼：" + p_codes.f_EngineNo引擎號碼;
l_mail.SubjectEncoding = Encoding.Default;
l_mail.IsBodyHtml = true;
l_mail.BodyEncoding = Encoding.Default;
l_mail.Body = set訂單內容(p_codes);
}
public CKGPartOrder get顧客姓名ByEngo(string p_strEngo)
{
string l_strSql = "select * from kddb..orders where engno = '" + p_strEngo + "'";
DataView l_dv = ivContext.資料管理員.getDataViewBySql(l_strSql);
List <CKGPartOrder> l_datas = new List <CKGPartOrder>();
if (l_dv.Count > 0)
{
for (int i = 0; i < l_dv.Count; i++)
{
CKGPartOrder l_code = createCKGPartOrder();
l_code.f_customername顧客姓名 = l_dv[i]["ordpnm"].ToString();
l_datas.Add(l_code);
}
return(l_datas.ToArray()[0]);
}
else
{
return(null);
}
}
private string 組Html(CKGPartOrder p_code請購主檔)
{
string l_str = set表頭資訊(p_code請購主檔);
for (int i = 0; i < p_code請購主檔.明細.Length; i++)
{
if (i == 0)
{
l_str += 組TR(p_code請購主檔.明細[i], p_code請購主檔.f_Memo備註說明, i + 1);
}
else
{
l_str += 組TR(p_code請購主檔.明細[i], "", i + 1);
}
}
l_str += "</table>";
l_str += "<br />";
l_str += "<br />";
l_str += "<br />";
l_str += "<br />";
l_str += " </body>";
return(l_str);
}
/// <summary>
/// (Yu 20090505)
/// </summary>
/// <param name="p_str廠商"></param>
/// <param name="p_str所別"></param>
/// <returns></returns>
public CKGPartOrder[] getGroupBy未訂購所別筆數(string p_str廠商, string p_str所別)
{
string l_strSql = " SELECT f_branchid,f_Vendor,Count(f_ExchangeID) as NonSendConut FROM [KG].[dbo].[tbKGPartOrder] WHERE f_IsSend='False'";//狀態False者表示尚未訂購
//string l_strSql = " SELECT f_branchid,f_Vendor,Count(f_ExchangeID) as NonSendConut FROM [KG].[dbo].[tbKGPartOrder] WHERE f_exchangeid = 'EF21S20131105830' ";//狀態False者表示尚未訂購
if (!"".Equals(p_str廠商))
{
l_strSql += " AND f_Vendor ='" + p_str廠商 + "'";
}
if (!"".Equals(p_str所別))
{
l_strSql += " AND f_branchid ='" + p_str所別 + "'";
}
l_strSql += " GROUP BY f_branchid,f_Vendor ";
DataView l_dv = ivContext.資料管理員.getDataViewBySql(l_strSql);
List <CKGPartOrder> l_datas = new List <CKGPartOrder>();
if (l_dv.Count > 0)
{
for (int i = 0; i < l_dv.Count; i++)
{
CKGPartOrder l_code = createCKGPartOrder();
l_code.f_SalesBranch業代單位 = l_dv[i]["f_branchid"].ToString();
l_code.f_Vendor請購廠商 = l_dv[i]["f_Vendor"].ToString();
l_code.NonSendCount未發送的請購筆數 = l_dv[i]["NonSendConut"].ToString();
l_datas.Add(l_code);
}
return(l_datas.ToArray());
}
else
{
return(null);
}
}
private string set表頭資訊(CKGPartOrder p_code)
{
string l_str廠商 = "";
if ("KC".Equals(p_code.f_Vendor請購廠商) || "KCS".Equals(p_code.f_Vendor請購廠商) || "KCF".Equals(p_code.f_Vendor請購廠商))
{
l_str廠商 = "亙長企業有限公司";
}
else if ("LD".Equals(p_code.f_Vendor請購廠商) || "LDS".Equals(p_code.f_Vendor請購廠商) || "LDF".Equals(p_code.f_Vendor請購廠商))
{
l_str廠商 = "劦大企業有限公司";
}
else if ("HM".Equals(p_code.f_Vendor請購廠商))
{
l_str廠商 = "惠明企業有限公司";
}
else
{
l_str廠商 = "";
}
string l_str = "";
l_str += " <table style=\"width: 720px\" cellspacing=\"0\" >";
l_str += " <tr>";
l_str += " <td colspan=\"6\" style=\"font-weight: bold; font-size: 24pt;\" align=\"center\">";
l_str += " 高輊汽車小百貨訂貨單</td>";
l_str += " </tr>";
l_str += " <tr>";
l_str += " <td colspan=\"6\"> ";
l_str += " </td>";
l_str += " </tr>";
l_str += " <tr>";
l_str += " <td style=\"width: 85px; font-size: 11pt;\" align=\"center\">";
l_str += " 訂購單號：</td>";
l_str += " <td style=\"width: 121px\">";
l_str += " " + p_code.f_ExchangeID請購單號 + "</td>";
l_str += " <td style=\"width: 85px; font-size: 11pt;\" align=\"center\">";
l_str += " 訂購日期：</td>";
l_str += " <td style=\"width: 100px; font-size: 11pt;\">";
l_str += " " + p_code.f_InsertDate請購日期 + "</td>";
l_str += " <td style=\"width: 75px; font-size: 11pt;\"align=\"center\">";
l_str += " 經辦人：</td>";
l_str += " <td style=\"width: 100px\">";
CUser l_user = new CUserFactory(ivContext).get高都員工檔(p_code.f_AssistantSmid助理員編);
//l_str += " " + p_code.f_AssistantName助理姓名 + "</td>";
if (l_user != null)
{
l_str += " " + l_user.f_username姓名 + "</td>";
}
else
{
l_str += " </td>";
}
l_str += " </tr>";
l_str += " <tr>";
l_str += " <td align=\"center\" style=\"font-size: 11pt; width: 85px\">";
l_str += " 請購編號：</td>";
l_str += " <td style=\"width: 121px\">";
l_str += " " + p_code.f_ExchangeID請購單號 + " </td>";
l_str += " <td align=\"center\" style=\"width: 85px\">";
l_str += " </td>";
l_str += " <td style=\"width: 100px\">";
l_str += " </td>";
l_str += " <td style=\"width: 75px; font-size: 11pt;\"align=\"center\">";
l_str += " 請購者：</td>";
l_str += "<td style=\"width: 100px\">";
CUser l_user業代 = new CUserFactory(ivContext).get高都員工檔(p_code.f_SalesSmid業代員編);
if (l_user業代 == null)
{
l_str += " </td>";
}
else
{
l_str += " " + l_user業代.f_username姓名 + " </td>";
}
//l_str += " " + p_code.f_SalesName業代姓名 + " </td>";
l_str += " </tr>";
l_str += " <tr>";
l_str += " <td style=\"width: 85px; font-size: 11pt;\" align=\"center\">";
l_str += " 廠商對象：</td>";
l_str += " <td colspan=\"2\">";
l_str += " " + l_str廠商 + "</td>";
l_str += " <td style=\"width: 100px; font-size: 11pt;\" align=\"center\">";
l_str += " 傳送時間：</td>";
l_str += " <td colspan=\"2\">";
l_str += " " + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "</td>";
l_str += " </tr>";
l_str += " </table>";
l_str += " <table style=\"width: 720px;\" border=\"1\" bordercolor=\"black\" cellspacing=\"0\">";
l_str += " <tr>";
l_str += " <td style=\"width: 50px\" align=\"center\">";
l_str += " 項次</td>";
l_str += " <td style=\"width: 200px\" align=\"center\">";
l_str += " 產品編號</td>";
l_str += " <td style=\"width: 320px\" align=\"center\">";
l_str += " 產品名稱</td>";
l_str += " <td style=\"width: 50px\" align=\"center\">";
l_str += " 數量</td>";
l_str += " <td style=\"width: 100px\" align=\"center\">";
l_str += " 備註</td>";
l_str += " </tr>";
return(l_str);
}
/// <summary>
/// (Yu 20090515) 取得請購單By條件
/// </summary>
/// <param name="p_str助理員編">助理員編</param>
/// <param name="p_str業代員編">業代員編</param>
/// <param name="p_str請購單號">請購單號</param>
/// <param name="p_str請購狀態">請購狀態 Check:點收完成;Buy:訂購待貨中;空白:f_Status不是空白者</param>
/// <param name="p_strStartDay">請購單起始日期</param>
/// <param name="p_strEndDay">請購單結束日期</param>
/// <returns></returns>
public CKGPartOrder[] getAll請購單號By條件(string p_str助理員編, string p_str業代員編, string p_str請購單號, string p_str請購狀態, string p_strStartDay, string p_strEndDay)
{
string l_strSql = "SELECT o.*,h.name,h.branch FROM [KG].[dbo].[tbKGPartOrder] o";
l_strSql += " left join kdnews..T_HRPersonel h on h.emp_id = o.f_SalesSmid ";
l_strSql += " WHERE 1=1 ";
if (!"".Equals(p_str助理員編))
{
l_strSql += " AND f_AssistantSmid = '" + p_str助理員編 + "' ";
}
if (!"".Equals(p_str請購單號))
{
l_strSql += " AND f_ExchangeID = '" + p_str請購單號 + "' ";
}
if (!"".Equals(p_str業代員編))
{
l_strSql += " AND f_SalesSmid = '" + p_str業代員編 + "' ";
}
if (!"".Equals(p_strStartDay) && "".Equals(p_strEndDay))
{
l_strSql += " AND f_InsertDate >='" + p_strStartDay + "'";
}
else if ("".Equals(p_strStartDay) && !"".Equals(p_strEndDay))
{
l_strSql += " AND f_InsertDate <='" + p_strEndDay + "'";
}
else if (!"".Equals(p_strStartDay) && !"".Equals(p_strEndDay))
{
l_strSql += " AND f_InsertDate BETWEEN '" + p_strStartDay + "' AND '" + p_strEndDay + "'";
}
l_strSql += " ORDER BY f_SalesSmid,f_InsertDate";
DataView l_dv = ivContext.資料管理員.getDataViewBySql(l_strSql);
List <CKGPartOrder> l_datas = new List <CKGPartOrder>();
if (l_dv.Count > 0)
{
for (int i = 0; i < l_dv.Count; i++)
{
CKGPartOrder l_code = createCKGPartOrder();
l_code.f_ExchangeID請購單號 = l_dv[i]["f_ExchangeID"].ToString();
l_code.f_Vendor請購廠商 = l_dv[i]["f_Vendor"].ToString();
l_code.f_EngineNo引擎號碼 = l_dv[i]["f_EngineNo"].ToString();
l_code.f_AssistantSmid助理員編 = l_dv[i]["f_AssistantSmid"].ToString();
l_code.f_SalesSmid業代員編 = l_dv[i]["f_SalesSmid"].ToString();
l_code.f_SalesName業代姓名 = l_dv[i]["name"].ToString();
l_code.f_SalesBranch業代單位 = l_dv[i]["branch"].ToString();
l_code.f_TotalCost總成本價 = Convert.ToInt32(l_dv[i]["f_TotalCost"].ToString());
l_code.f_TotalPrice總計價格 = Convert.ToInt32(l_dv[i]["f_TotalPrice"].ToString());
l_code.f_Memo備註說明 = l_dv[i]["f_Memo"].ToString();
l_code.f_InsertDate請購日期 = l_dv[i]["f_InsertDate"].ToString();
l_code.f_InsertIP輸入電腦IP = l_dv[i]["f_InsertIP"].ToString();
l_code.f_EditDate編輯日期 = l_dv[i]["f_EditDate"].ToString();
l_datas.Add(l_code);
}
return(l_datas.ToArray());
}
else
{
return(null);
}
}
