public frmRptReport(object case_)
{
InitializeComponent();
cls = new Class.clsDatabase("192.168.0.3", "KTC", "KTCNET", "khu@9wbe", "");
this.case_ = (Case_)case_;
comboBox1.SelectedIndex = 0;
}
public RptReport()
{
InitializeComponent();
cls = new Class.clsDatabase("192.168.0.3", "KTC", "KTCNET", "khu@9wbe", "");
comboBox1.SelectedIndex = 0;
}
public RptReport(string id,string base_,object case_)
{
InitializeComponent();
ID = id;
Base = base_;
cls = new Class.clsDatabase("192.168.0.3", base_, "KTCNET", "khu@9wbe", "");
this.case_ = (Case_)case_;
label1.Visible = false;
dateTimePicker1.Visible = false;
btnOK.Visible = false;
label1.Visible = false;
comboBox1.Visible = false;
LoadSlip();
}
/*แสดงรายงานทะเบียนคุมใบเบิก*/
private void button1_Click(object sender, EventArgs e)
{
Class.clsDatabase cls = null;
String local = "select GrStockDetailId,ip,db,dbuser,dbpass FROM GrStockDetail WHERE GrStockDetailId = " + Id;
DataRow d = Class.DBConnString.clsDB.QueryDataTable(local).Rows[0];
if (d["GrStockDetailId"].ToString() == "") return;
cls = new Class.clsDatabase(d["ip"].ToString(), d["db"].ToString()
, d["dbuser"].ToString(), d["dbpass"].ToString(), "");
String sql = "";
if (Class.DBConnString.sDb == "7CSK")
{
sql = "select h.SdateId,h.TranferReqHeadDateSave as saleappheaddatesave,h.TranferReqHeadDate as saleappheaddate,c.GrStockDetailLname as customername,s.tranferstatusname as statusname " +
",CASE WHEN h.sdateid IN (SELECT tbb.SdateId FROM [7csk].dbo.TranferBetweenBrnDt tbb) THEN (SELECT tranferid FROM [7csk].dbo.TranferBetweenBrnDt tbb WHERE tbb.SdateId = h.SdateId ) ELSE '-' END as saleheadid,(select sh.tranferdate from [7csk].dbo.TranferBetweenBrn sh where h.Sdateid in(SELECT tbbd.SdateId FROM [7csk].dbo.TranferBetweenBrnDt tbbd WHERE sh.TranferId = tbbd.TranferId)) as saledate ";
sql += "," +
"(SELECT grstockdetailLname from grstockdetail where grstockdetailid = " + Id + ") AS CompanyName ";
sql += "from tranferreqhead h inner join grstockdetail c on h.grstockdetailid = c.grstockdetailid inner join tranferstatus s on h.statusid = s.tranferstatusid " +
"where h.tranferreqheaddatesave between '" + Class.Function.GetDate(dateTimePicker1.Value) + " 00:00:00' and '" + Class.Function.GetDate(dateTimePicker2.Value) + " 23:59:59' ";
}
else {
sql = "select h.SdateId,h.TranferReqHeadDateSave as saleappheaddatesave,h.TranferReqHeadDate as saleappheaddate,c.GrStockDetailLname as customername,s.tranferstatusname as statusname " +
",CASE WHEN h.sdateid IN (SELECT tbb.SdateId FROM TranferBetweenBrnDt tbb) THEN (SELECT tranferid FROM TranferBetweenBrnDt tbb WHERE tbb.SdateId = h.SdateId ) ELSE '-' END as saleheadid,(select sh.tranferdate from TranferBetweenBrn sh where h.Sdateid in(SELECT tbbd.SdateId FROM TranferBetweenBrnDt tbbd WHERE sh.TranferId = tbbd.TranferId)) as saledate ";
sql += "," +
"(SELECT grstockdetailLname from grstockdetail where grstockdetailid = " + Id + ") AS CompanyName ";
sql += "from tranferreqhead h inner join grstockdetail c on h.grstockdetailid = c.grstockdetailid inner join tranferstatus s on h.statusid = s.tranferstatusid " +
"where h.tranferreqheaddatesave between '" + Class.Function.GetDate(dateTimePicker1.Value) + " 00:00:00' and '" + Class.Function.GetDate(dateTimePicker2.Value) + " 23:59:59' ";
}
Report.CtrRequisRegis Crv = new Report.CtrRequisRegis();
Crv.SetDataSource(cls.QueryDataTable(sql));
Crv.SetParameterValue("sdate", dateTimePicker1.Value);
Crv.SetParameterValue("edate", dateTimePicker2.Value);
((TextObject)Crv.Section2.ReportObjects["Text9"]).Text = "สาขาผู้รับโอน";
((TextObject)Crv.Section2.ReportObjects["Text11"]).Text = "หมายเลขใบโอน";
((TextObject)Crv.Section2.ReportObjects["Text13"]).Text = "วันที่โอน";
crystalReportViewer1.ReportSource = Crv;
crystalReportViewer1.Refresh();
crystalReportViewer1.Show();
}
/*เก็บข้อมูลจากใบเบิกโอนสาขาลงใน form*/
private void setValue()
{
String sql = "select * from tranferbetweenbrn " +
"where tranferid = '" + tid + "' ";
Class.clsDatabase cls = null;
String local = "select * from GrStockDetail where GrStockDetailId = " + gid;
DataRow dx = Class.DBConnString.clsDB.QueryDataTable(local).Rows[0];
if (Class.DBConnString.clsDB.QueryDataTable(local).Rows.Count > 0)
{
cls = new Class.clsDatabase(dx["ip"].ToString(), dx["db"].ToString(),
dx["dbuser"].ToString(), dx["dbpass"].ToString(), "");
}
SqlDataReader reader = cls.QueryDataReader(sql);
if (reader.Read())
{
dptRecDate.Value = Convert.ToDateTime(reader["TranferDate"]);
textBox4.Text = Gr(reader["TranferSource"].ToString());
textBox4.Enabled = false;
txt777.Text = Gr(reader["TranferDestination"].ToString());
txt777.Enabled = false;
textBox3.Text = subAbount(reader["RoundN"].ToString(), 0);
textBox2.Text = subAbount(reader["RoundN"].ToString(), 1);
textBox1.Text = reader["ReqN"].ToString();
if (reader["ConfirmId"].ToString() == "3")
btnSave.Enabled = false;
}
dataGridView1.ColumnCount = 1;
dataGridView1.Columns[0].Name = "รหัสใบเบิก";
dataGridView1.Columns[0].Width = 120;
sql = "select SdateId from tranferbetweenbrnDt where tranferid = '" + tid + "'";
reader = cls.QueryDataReader(sql);
int i = 0;
while (reader.Read())
{
String[] addReceive = new String[] { };
addReceive = new String[] {
reader["SdateId"].ToString()+""
};
ProductListOld.Add(new ProductList { ProductId = reader["SdateId"].ToString() });
dataGridView1.Rows.Add(addReceive);
i++;
}
if (dataGridView1.Rows.Count <= 0) return;
ShowDetailDgv(dataGridView1.Rows[0], Class.Function.GetTextId(textBox4.Text));
HeadGrid();
//SetDgvWeightH(dgvReceive.Rows[0].Cells[9].Value.ToString(), dgvWeight, dgvReceive.Rows[0].Cells[8].Value.ToString());
}
private string saleId(int i = 0)
{
/*ดึงเลขทีใบโอนสาขาใหม่*/
Class.clsDatabase cls = null;
if (textBox4.Text == "" || txt777.Text == "") return "";
try
{
String sql = "DECLARE @date datetime SET @date = '" + Class.Function.GetDate(dptRecDate.Value) + "' " +
"DECLARE @ids int SET @ids = '" + Class.Function.GetTextId(this.textBox4.Text) + "' " +
"DECLARE @id int SET @id = '" + Class.Function.GetTextId(txt777.Text) + "' " +
"select (SELECT 'F'+ SUBSTRING(code,3,1) from GrStockDetail where GrStockDetailId = @ids) + (SELECT SUBSTRING(code,3,1) from GrStockDetail where GrStockDetailId = @id) + RIGHT(cast(YEAR(@date) AS int)+43,2) + RIGHT('0' + CONVERT(VARCHAR, MONTH(@date)),2) + " +
"Right('00000' + cast((select isnull(max(SUBSTRING(s.TranferId,8,5)),0)+1 FROM dbo.TranferBetweenBrn s " +
"WHERE year(@date) = isnull(year(s.TranferDate),NULL) and " +
"(MONTH(@date)) = isnull(MONTH(s.TranferDate),NULL) AND SUBSTRING(s.TranferId,1,3) = (SELECT 'F'+ SUBSTRING(code,3,1) from GrStockDetail where GrStockDetailId = @ids) + + (SELECT SUBSTRING(code,3,1) from GrStockDetail where GrStockDetailId = @id)) as varchar(10)), 5) ";
String local = "select * from GrStockDetail where GrStockDetailId = " + Class.Function.GetTextId(this.textBox4.Text) + "";
DataRow d = Class.DBConnString.clsDB.QueryDataTable(local).Rows[0];
if (Class.DBConnString.clsDB.QueryDataTable(local).Rows.Count > 0)
{
cls = new Class.clsDatabase(d["ip"].ToString(), d["db"].ToString(),
d["dbuser"].ToString(), d["dbpass"].ToString(), "");
return cls.QueryExecuteScalarNonAlert(sql).ToString();
}
else
{
return Class.DBConnString.clsDB.QueryExecuteScalarNonAlert(sql).ToString();
}
//return Class.DBConnString.clsDB.QueryExecuteScalarNonAlert(sql).ToString();
}
catch
{
return "";
}
}
public TranferAddReq(String type, String id, String tw, String qtyp, String qty,String gid,String price)
{
ID = id;
Type = type;
Qty = qty;
InitializeComponent();
Class.clsDatabase cls = null;
DataTable dt = null;
String sql = "select p.productid,p.productname,u.unitpackname,us.unitsalename from product p left join unitpack u on p.unitpackid = u.unitpackid " +
"left join unitsale us on p.unitsaleid = us.unitsaleid where p.productid = '" + id + "'";
try
{
String local = "select * from GrStockDetail where GrStockDetailId = '" + gid + "'";
DataRow d = Class.DBConnString.clsDB.QueryDataTable(local).Rows[0];
if (Class.DBConnString.clsDB.QueryDataTable(local).Rows.Count > 0)
{
cls = new Class.clsDatabase(d["ip"].ToString(), d["db"].ToString(),
d["dbuser"].ToString(), d["dbpass"].ToString(), "");
dt = cls.QueryDataTable(sql);
}
else
{
dt = Class.DBConnString.clsDB.QueryDataTable(sql);
}
}
catch {
if (Class.DBConnString.sDb == "SHOPFR")
{
DBConnString.strConn = "Data Source=192.168.0.3;Initial Catalog=SHOP; Persist Security Info=True;User ID=KTCNET;Password=khu@9wbe";
DBConnString.sServer = "192.168.0.3";
DBConnString.clsDB = new clsDatabase(DBConnString.strConn, DBConnString.sServer);
}
dt = Class.DBConnString.clsDB.QueryDataTable(sql);
}
DataTable dtNew = dt;
foreach (DataRow row in dtNew.Rows)
{
TxtProductId.Text = row["ProductId"].ToString();
TxtProductName.Text = row["ProductName"].ToString();
lblSale1.Text = row["UnitSaleName"].ToString();
lblPack.Text = row["UnitPackName"].ToString();
}
this.Text = "หน้าจอเพิ่มรายการเบิกโอนสินค้า";
if (Type == "1")
{
dgvWeight.ColumnCount = 3;
TranferRequisition f = new TranferRequisition();
f.SetDgvWeightH(tw, dgvWeight, Type);
txtSale.Text = CellSum(1).ToString();
txtWeight.Text = CellSum(2).ToString();
if (Class.DBConnString.sDb != "SHOP" || Class.DBConnString.sDb != "SHOPFR")
{
textBox1.Text = price;
}
}
if (Type == "2")
{
dgvWeight.ColumnCount = 2;
TranferRequisition f = new TranferRequisition();
f.SetDgvWeightH(tw, dgvWeight, Type);
txtSale.Text = CellSum(1).ToString();
txtWeight.Text = "0";
if (Class.DBConnString.sDb != "SHOP" || Class.DBConnString.sDb != "SHOPFR")
{
textBox1.Text = price;
}
}
if (Type == "3")
{
dgvWeight.ColumnCount = 2;
txtPack.Text = qtyp;
txtSale.Text = qty;
dgvWeight.Visible = false;
txtPack.Enabled = true; txtPack.BackColor = Color.White;
txtSale.Enabled = true; txtSale.BackColor = Color.White;
btnAdd.Enabled = false;
btnDel.Enabled = false;
txtRunPack.Enabled = false;
txtWeight.Text = "0";
txtPack.Focus();
txtPack.SelectAll();
if (Class.DBConnString.sDb != "SHOP" || Class.DBConnString.sDb != "SHOPFR")
{
textBox1.Text = price;
}
}
}
private void load(String q)
{
if (Txt != "")
{
q = " and isnull(SdateId,saleappheadId) like '%" + textBox1.Text + "%'";
}
String sql = "select Sdateid,saleappheadid,[AbountId],[RequisitionId],[SaleAppHeadDate],c.customername,u.SureName " +
"from sale_app_head h " +
"left join customer c on h.customerid = c.customerid " +
"left join userlogin u on h.UserName = u.UserName " +
"left join sale_app_status s on h.statusId = s.statusId " +
//"where 1=1 and h.statusid = 2 " + q + " " +
"where 1=1 and h.statusid = 2 " +
"and c.customername like '%" + textBox1.Text + "%'" +
"and Sdateid is not null " +
"and saleappheadid in (select saleappheadid from SALE_APP_DETAIL)" +
"order by saleappheadid desc";
Class.clsDatabase cls = new Class.clsDatabase(DB, IP,"");
DataTable dt = cls.QueryDataTable(sql);
dataGridView1.DataSource = dt;
formatHead();
}
/*แสดงข้อมูลใบโอนสาขา*/
private void loadGridHd(String w = "")
{
Class.clsDatabase cls = null;
if ((int)toolStripComboBox1.ComboBox.SelectedValue > 0)
{
String sql = "select b.TranferId,b.TranferDate,b.TranferDateSave,b.Total,b.RoundN,b.ReqN," +
"(select grstockdetailLname from grstockdetail where grstockdetailId = b.TranferSource) " +
"as TSourceName,(select grstockdetailLname from grstockdetail where grstockdetailId = b.TranferDestination) " +
"as TDestinationName,c.confirmname,u.surename,t.tranferstatusName,b.Description,b.TranferSource,b.TranferDestination from TranferBetweenBrn b inner join confirm c " +
"on c.confirmid = b.confirmid inner join userlogin u on u.UserName = b.UserName inner join tranferstatus t " +
"on t.tranferstatusid = b.statusid " + w;
String local = "select * from GrStockDetail where GrStockDetailId = " + toolStripComboBox1.ComboBox.SelectedValue;
DataRow dx = Class.DBConnString.clsDB.QueryDataTable(local).Rows[0];
if (Class.DBConnString.clsDB.QueryDataTable(local).Rows.Count > 0)
{
cls = new Class.clsDatabase(dx["ip"].ToString(), dx["db"].ToString(),
dx["dbuser"].ToString(), dx["dbpass"].ToString(), "");
dataGridView1.DataSource = cls.QueryDataTable(sql);
}
else
{
dataGridView1.DataSource = Class.DBConnString.clsDB.QueryDataTable(sql);
}
}
else {
String sql = "";
String local = "select * from GrStockDetail where GrStockId = " + stock;
DataTable dx = Class.DBConnString.clsDB.QueryDataTable(local);
foreach (DataRow d in dx.Rows) {
sql += "select b.TranferId,b.TranferDate,b.TranferDateSave,b.Total,b.RoundN,b.ReqN," +
"(select grstockdetailLname from grstockdetail where grstockdetailId = b.TranferSource) " +
"as TSourceName,(select grstockdetailLname from grstockdetail where grstockdetailId = b.TranferDestination) " +
"as TDestinationName,c.confirmname,u.surename,t.tranferstatusName,b.Description,b.TranferSource,b.TranferDestination from [" + d["db"].ToString() + "].dbo.TranferBetweenBrn b inner join [" + d["db"].ToString() + "].dbo.confirm c " +
"on c.confirmid = b.confirmid inner join [" + d["db"].ToString() + "].dbo.userlogin u on u.UserName = b.UserName inner join [" + d["db"].ToString() + "].dbo.tranferstatus t " +
"on t.tranferstatusid = b.statusid " + w + " and b.TranferSource = " + d["grstockdetailId"].ToString() + " UNION ALL ";
}
sql = "select * from (" + sql.Substring(0, sql.Length - 10) + ") as tt order by TranferId desc";
dataGridView1.DataSource = Class.DBConnString.clsDB.QueryDataTable(sql);
}
dataGridView1.Columns["TranferId"].HeaderText = "รหัสโอน";
dataGridView1.Columns["TranferDate"].HeaderText = "วันที่โอน";
dataGridView1.Columns["TranferDateSave"].HeaderText = "วันที่บันทึก";
dataGridView1.Columns["TSourceName"].HeaderText = "ต้นทาง";
dataGridView1.Columns["TDestinationName"].HeaderText = "ปลายทาง";
dataGridView1.Columns["Total"].HeaderText = "ต้นทุนรวม";
dataGridView1.Columns["Description"].HeaderText = "รายละเอียด";
dataGridView1.Columns["ConfirmName"].HeaderText = "อนุมัติ";
dataGridView1.Columns["surename"].HeaderText = "ผู้บันทึก";
dataGridView1.Columns["RoundN"].HeaderText = "รอบ";
dataGridView1.Columns["ReqN"].HeaderText = "เลขที่";
dataGridView1.Columns["tranferstatusName"].HeaderText = "สถานะ";
dataGridView1.Columns["TranferSource"].Visible = false;
dataGridView1.Columns["TranferDestination"].Visible = false;
if (dataGridView1.Rows.Count < 1) return;
loadGridRq(dataGridView1.Rows[0]);
}
public static unsafe DataRow SaleTopReturnPriceAndDate(String Id, String db, String cusid = "", String Date = "GETDATE()", String Edate = "GETDATE()")
{
string strCustId = cusid;
if (cusid != "") {
cusid = " AND rh.CustomerId = "+cusid+" ";
}
String odate = "";
if (Date != "GETDATE()") {
Date = "'" + Date + "'";
odate = " AND rh.saleheaddatetime <= " + Date + "";
}
else if (Edate != "GETDATE()")
{
Edate = "'" + Edate + "'";
odate = " AND rh.saleheaddatetime between " + Edate + " and " + Date + " ";
}
else {
odate = " AND rh.saleheaddatetime <= GETDATE()";
}
String sql = "DECLARE @s int;SET @s = (select custypeid from customer where customerid = '" + strCustId + "') " +
"select case when @s = 1 then p.Level1 when @s = 2 then p.Level2 when @s = 3 then p.Level3 " +
"when @s = 4 then p.Level4 when @s = 5 then p.Level5 when @s = 6 then p.Level6 when @s = 7 then p.Level7 " +
"when @s = 8 then p.Level8 when @s = 9 then p.Level9 when @s = 10 then p.Level10 when @s = 11 then p.Level11 " +
"when @s = 12 then p.Level12 when @s = 13 then p.Level13 when @s = 14 then p.Level14 else p.Level15 end cost," +
"'' as date,'' as name " +
"from ProductSetPriceLevel p where p.ProductId = '" + Id + "' ";
Class.clsDatabase cls = new Class.clsDatabase(db);
DataTable dt = cls.QueryDataTable(sql);
if (dt.Rows.Count == 0)
{
sql = "SELECT rd.saledetailpriceunit as cost,rh.saleheaddatetime as date,c.customername as name FROM sale_detail_view rd " +
"INNER JOIN dbo.sale_head_view rh ON rh.saleheadid = rd.saleheadid inner join customer c on rh.customerid = c.customerid " +
"WHERE rd.ProductId = '" + Id + "' " + odate + " " + cusid + " " +
"ORDER BY rh.saleheaddatetime DESC";
dt = cls.QueryDataTable(sql);
}
if (dt.Rows.Count > 0)
{
return dt.Rows[0];
}
else
{
return null;
}
}
/*ดึงข้อมูลรายงานใบโอนสาขา*/
private void SaleData()
{
sb = new StringBuilder();
String sql;
sb.Remove(0, sb.Length);
string title = (DBConnString.sCode == "KTC") ? "เค.ที.ซี" : (DBConnString.sCode == "SHOP") ? "ร้านคูไต้เจี่ย" : (DBConnString.sCode == "7CSK") ? "7Sea สงขลา" : "";
string t = "select * from GrStockDetail where GrStockDetailId = " + db + "";
DataRow d = Class.DBConnString.clsDB.QueryDataTable(t).Rows[0];
if (db != "")
{
title = d["GrStockDetailLName"].ToString();
cls = new Class.clsDatabase(d["ip"].ToString(), d["db"].ToString(),
d["dbuser"].ToString(), d["dbpass"].ToString(), "");
}
String newdb = d["db"].ToString();
String sss = "select tranferid from TranferBetweenBrn where tranferid = '" + SaleAppHeadId + "'";
String id = "";
String a = "";
String s = "select h.TranferReqHeadId from TranferBetweenBrnDt d inner join dbo.TranferReqHead h ON h.SdateId = d.SdateId where tranferid = '" + SaleAppHeadId + "'";
DataTable dss = cls.QueryDataTable(s);
if (dss.Rows.Count > 0)
{
a = "'" + dss.Rows[0][0].ToString() + "'";
if (dss.Rows.Count > 1)
{
for (int i = 1; i < dss.Rows.Count; i++)
{
a += ",'" + dss.Rows[i][0].ToString() + "'";
}
}
}
else
{
return;
}
String dbf = "";
if (newdb == "SHOPFR") {
dbf = "SHOP.DBO.";
}
//String xxx = "select AbountId from sale_app_head where saleappheadid = '" + SaleAppHeadId + "'";
//String x = Class.DBConnString.clsDB.QueryExecuteScalar(xxx);
id = cls.QueryExecuteScalar(sss).ToString();
sb.Append("SELECT saleappdetailid,SaleAppHeadId,SaleAppDetailQuantityPack,SaleAppDetailQuantity,SaleAppDetailWeight,ProductName,SaleAppHeadDate,TitleName,CustomerName,FirstName,UserName,SaleAppPackId,SurName,ProductId,ProductDescript,ProductNameEng,SaleAppWeight,SaleAppPack,AbountId,RequisitionId FROM (");
sb.Append("SELECT TranferReqDetail.TranferReqDetailId as saleappdetailid,tranferbetweenbrn.tranferid as SaleAppHeadId, ");
sb.Append("TranferReqDetail.QuantityPack as SaleAppDetailQuantityPack, ");
sb.Append("TranferReqDetail.Quantity as SaleAppDetailQuantity, ");
sb.Append(" TranferReqDetail.Weight as SaleAppDetailWeight, ");
sb.Append("TranferReqWeight.Weight as SaleAppWeight, ");
sb.Append("TranferReqWeight.Pack as SaleAppPack, ");
sb.Append("PRODUCT.ProductName, ");
sb.Append("CONVERT(VARCHAR(10),TranferReqHead.TranferReqHeadDate,105) AS SaleAppHeadDate, "); //SALE_APP_HEAD.SaleAppHeadDate
sb.Append(" '" + title + "' AS TitleName, ");
sb.Append(" GrStockDetail.GrStockDetailLName as CustomerName, ");
sb.Append("UNITSALE.UnitSaleName AS FirstName, ");
sb.Append("UNITPACK.UnitPackName AS UserName, ");
sb.Append("TranferReqWeight.PackId as SaleAppPackId, ");//convert (int, A.my_NvarcharColumn) FROM A
sb.Append("'ก.ก' AS SurName, ");
sb.Append("PRODUCT.ProductId,TranferReqDetail.costperunit as productdescript, ");
sb.Append("TranferReqDetail.Note AS ProductNameEng,AbountId,RequisitionId ");
sb.Append("FROM TranferReqHead ");
sb.Append("INNER JOIN TranferReqDetail ON TranferReqHead.TranferReqHeadId = TranferReqDetail.TranferReqHeadId ");
sb.Append("INNER JOIN TranferReqWeight ON TranferReqDetail.ProductId = TranferReqWeight.ProductId ");
sb.Append("INNER JOIN tranferbetweenbrndt ON tranferbetweenbrndt.sdateid = TranferReqHead.sdateid ");
sb.Append("INNER JOIN tranferbetweenbrn ON tranferbetweenbrn.tranferid = tranferbetweenbrndt.tranferid ");
sb.Append("INNER JOIN " + dbf + "PRODUCT ON TranferReqDetail.ProductId = PRODUCT.ProductId ");
sb.Append("INNER JOIN GrStockDetail ON TranferReqHead.GrStockDetailId = GrStockDetail.GrStockDetailId ");
sb.Append("INNER JOIN " + dbf + "UNITSALE ON PRODUCT.UnitSaleId = UNITSALE.UnitSaleId ");
sb.Append("INNER JOIN " + dbf + "UNITPACK ON PRODUCT.UnitPackId = UNITPACK.UnitPackId ");
sb.Append("where TranferReqHead.TranferReqHeadId = TranferReqWeight.TranferReqHeadId ");
sb.Append("AND dbo.tranferbetweenbrn.statusid <> 1 ");
sb.Append("AND TranferReqHead.TranferReqHeadId in("+a+") ");
sb.Append("AND TranferReqDetail.ProductAppType = 1 ");
//////////////////////////////////////////////////////////////////////////////////////////////////
sb.Append("UNION all ");
sb.Append("SELECT TranferReqDetail.TranferReqDetailId,tranferbetweenbrn.tranferid as SaleAppHeadId, ");
sb.Append("TranferReqDetail.QuantityPack, ");
sb.Append("TranferReqDetail.Quantity, ");
sb.Append("TranferReqDetail.Weight, ");
sb.Append("TranferReqWeight.Weight, ");
sb.Append("TranferReqWeight.Pack, ");
sb.Append("PRODUCT.ProductName, ");
sb.Append("CONVERT(VARCHAR(10),TranferReqHead.TranferReqHeadDate,105), "); //SALE_APP_HEAD.SaleAppHeadDate
sb.Append(" '" + title + "', ");
sb.Append(" GrStockDetail.GrStockDetailLName, ");
sb.Append("UNITSALE.UnitSaleName, ");
sb.Append("UNITPACK.UnitPackName, ");
sb.Append("TranferReqWeight.PackId,");
sb.Append("'', ");
sb.Append("PRODUCT.ProductId,TranferReqDetail.costperunit, ");
sb.Append("TranferReqDetail.Note,AbountId,RequisitionId ");
sb.Append("FROM TranferReqHead ");
sb.Append("INNER JOIN TranferReqDetail ON TranferReqHead.TranferReqHeadId = TranferReqDetail.TranferReqHeadId ");
sb.Append("INNER JOIN TranferReqWeight ON TranferReqDetail.ProductId = TranferReqWeight.ProductId ");
sb.Append("INNER JOIN " + dbf + "PRODUCT ON TranferReqDetail.ProductId = PRODUCT.ProductId ");
sb.Append("INNER JOIN tranferbetweenbrndt ON tranferbetweenbrndt.sdateid = TranferReqHead.sdateid ");
sb.Append("INNER JOIN tranferbetweenbrn ON tranferbetweenbrn.tranferid = tranferbetweenbrndt.tranferid ");
sb.Append("INNER JOIN GrStockDetail ON TranferReqHead.GrStockDetailId = GrStockDetail.GrStockDetailId ");
sb.Append("INNER JOIN " + dbf + "UNITSALE ON PRODUCT.UnitSaleId = UNITSALE.UnitSaleId ");
sb.Append("INNER JOIN " + dbf + "UNITPACK ON PRODUCT.UnitPackId = UNITPACK.UnitPackId ");
sb.Append("where TranferReqHead.TranferReqHeadId = TranferReqWeight.TranferReqHeadId ");
sb.Append("AND dbo.tranferbetweenbrn.statusid <> 1 ");
sb.Append("AND TranferReqHead.TranferReqHeadId in("+a+") ");
sb.Append("AND TranferReqDetail.ProductAppType = 2 ");
//sb.Append("order by SALE_APP_HEAD.SaleAppHeadId ");
/////////////////////////////////////////////////////////////////////////////////////////////////////////
sb.Append("UNION all ");
sb.Append("SELECT TranferReqDetail.TranferReqDetailId,tranferbetweenbrn.tranferid as SaleAppHeadId, ");
sb.Append("TranferReqDetail.QuantityPack, ");
sb.Append("TranferReqDetail.Quantity, ");
sb.Append("TranferReqDetail.Weight, ");
sb.Append(" '', ");
sb.Append(" '', ");
sb.Append("PRODUCT.ProductName, ");
sb.Append(" CONVERT(VARCHAR(10),TranferReqHead.TranferReqHeadDate,105), ");
sb.Append(" '" + title + "', ");
sb.Append(" GrStockDetail.GrStockDetailLName, ");
sb.Append("UNITSALE.UnitSaleName , ");
sb.Append("UNITPACK.UnitPackName , ");
sb.Append("'', ");
sb.Append("'', ");
sb.Append("PRODUCT.ProductId,TranferReqDetail.costperunit, ");
sb.Append("TranferReqDetail.Note,AbountId,RequisitionId ");
sb.Append("FROM TranferReqHead ");
sb.Append(" INNER JOIN TranferReqDetail ON TranferReqHead.TranferReqHeadId = TranferReqDetail.TranferReqHeadId ");
sb.Append("INNER JOIN " + dbf + "PRODUCT ON TranferReqDetail.ProductId = PRODUCT.ProductId ");
sb.Append("INNER JOIN tranferbetweenbrndt ON tranferbetweenbrndt.sdateid = TranferReqHead.sdateid ");
sb.Append("INNER JOIN tranferbetweenbrn ON tranferbetweenbrn.tranferid = tranferbetweenbrndt.tranferid ");
sb.Append("INNER JOIN GrStockDetail ON TranferReqHead.GrStockDetailId = GrStockDetail.GrStockDetailId ");
sb.Append("INNER JOIN " + dbf + "UNITSALE ON PRODUCT.UnitSaleId = UNITSALE.UnitSaleId ");
sb.Append("INNER JOIN " + dbf + "UNITPACK ON PRODUCT.UnitPackId = UNITPACK.UnitPackId ");
sb.Append("where TranferReqDetail.ProductAppType = 3 ");
sb.Append("AND dbo.tranferbetweenbrn.statusid <> 1 ");
sb.Append("AND TranferReqHead.TranferReqHeadId in("+a+") ");
sb.Append(") AS T ORDER BY saleappdetailid,T.SaleAppPackId ");
sql = sb.ToString();
//textBox1.Text = sql + "\n" + SaleAppHeadId;
//textBox1.Text = sql + "\n" + SaleAppHeadId;
com = new SqlCommand(sql, Conn);
//com.Parameters.Add("@SaleAppHeadId", SqlDbType.Text).Value = a;
//
SaleDa = new SqlDataAdapter(com);
SaleDa.Fill(SaleDataset, "SaleApp");
Conn.Close();
String fname = @"ImageBarCode.jpg";
String strData = id+"";
Image img = GenerateBarcode(strData, TYPE.CODE128);
img.Save(fname);
DataTable dtImg = DtImageForReport(fname, "DT_IMGBAR", "imgBar");
Report.CtrSaleAppShowTran2 cc = new KTCERP.Report.CtrSaleAppShowTran2();
cc.SetDataSource(SaleDataset.Tables[0]);
cc.Subreports["DETAIL_SUB"].Database.Tables[0].SetDataSource(dtImg);
crystalReportViewer1.ReportSource = cc;
crystalReportViewer1.Refresh();
crystalReportViewer1.Show();
File.Delete(fname);
}
private void ShowDgvProduct()
{
Class.clsDatabase cls = null;
DataTable dt = null;
String StrFind = TbxFind.Text.Trim();
String sql = "SELECT t.SdateId,t.TranferreqheadId,t.TranferreqheadDate,g.GrStockDetailLName,s.tranferstatusname from Tranferreqhead t inner join "+
"grstockdetail g on t.grstockdetailid = g.grstockdetailid inner join tranferstatus s on t.statusId = s.tranferstatusId " +
"WHERE t.statusId in(2,4) AND ((t.SdateId = '" + StrFind + "' AND t.statusId in(2,4) ) " +
"OR (t.TranferreqheadId = '" + StrFind + "' AND t.statusId in(2,4) )" +
"OR g.GrStockDetailLName like '%" + StrFind + "%' OR (";
String[] arr = StrFind.Split(' ');
sql += " g.GrStockDetailLName like '%" + arr[0] + "%' ";
for (int i = 1; i < arr.Length; i++)
{
sql += " AND g.GrStockDetailLName like '%" + arr[i] + "%' ";
}
sql += " )";
sql += " ) " + w;
String local = "select * from GrStockDetail where GrStockDetailId = " + id;
DataRow d = Class.DBConnString.clsDB.QueryDataTable(local).Rows[0];
if (Class.DBConnString.clsDB.QueryDataTable(local).Rows.Count > 0)
{
cls = new Class.clsDatabase(d["ip"].ToString(), d["db"].ToString(),
d["dbuser"].ToString(), d["dbpass"].ToString(), "");
dt = cls.QueryDataTable(sql);
}
else {
dt = Class.DBConnString.clsDB.QueryDataTable(sql);
}
DgvProduct.DataSource = dt;
FormatDgvPProduct();
}
private void toolStripButton2_Click(object sender, EventArgs e)
{
dataGridView1.DataSource = null;
dataGridView2.DataSource = null;
dataGridView3.DataSource = null;
String s = "";
if (textBox1.Text != "") {
s += " and (SdateId like '%" + textBox1.Text + "%' OR tranferreqheadid like '%" + textBox1.Text + "%') ";
}
if (textBox2.Text != "")
{
s += " and u.SureName like '%" + textBox2.Text + "%' ";
}
if (textBox3.Text != "")
{
s += " and c.grstockdetailid = " + Class.Function.GetTextId(textBox3.Text) + " ";
}
if (textBox4.Text != "")
{
s += " and (AbountId like '%" + textBox4.Text + "%' OR RequisitionId like '%" + textBox4.Text + "%') ";
}
if (checkBox3.Checked == true) {
s += " and TranferReqHeadDate between '" + Class.Function.GetDate(dateTimePicker1.Value) + "' and '" + Class.Function.GetDate(dateTimePicker2.Value) + "' ";
}
if (txtProduct.Text != "")
{
s += "AND h.TranferReqHeadId IN (SELECT trd.TranferReqHeadId FROM dbo.TranferReqDetail trd \n"
+ "INNER JOIN dbo.PRODUCT p ON p.ProductId = trd.ProductId \n"
+ "WHERE trd.ProductId LIKE '%" + txtProduct.Text + "%' OR p.ProductName LIKE '%" + txtProduct.Text + "%') \n";
}
//if (checkBox4.Checked == true)
//{
// s += " and TranferReqHeadDateSave between '" + Class.Function.GetDate(dateTimePicker3.Value) + " 00:00:00' and '" + Class.Function.GetDate(dateTimePicker4.Value) + " 23:59:59' ";
//}
String sql = "";
String local = "select GrStockDetailId,ip,db,dbuser,dbpass,grstockdetaillname FROM GrStockDetail WHERE GrStockId = " + stock;
DataTable Gr = Class.DBConnString.clsDB.QueryDataTable(local);
if (toolStripComboBox1.ComboBox.SelectedIndex > 0)
{
foreach (DataRow d in Gr.Rows)
{
if (d["GrStockDetailId"].ToString() == toolStripComboBox1.ComboBox.SelectedValue.ToString())
{
sql += "select h.[Sid],isnull(h.SdateId,'-') as SdateId,h.TranferReqHeadid,h.[AbountId],h.[RequisitionId],h.[TranferReqHeadDate], " +
"h.[TranferReqHeadDateSave],c.grstockdetaillname,u.SureName ,s.tranferstatusName, " +
"(select top 1 tranferid from tranferbetweenbrn where tranferid in(select tranferid from tranferbetweenbrndt where sdateid = h.sdateid )) as SaleHeadid,h.totalcost,'" + d["GrStockDetailLname"].ToString() + "' as t,'" + d["GrStockDetailId"].ToString() + "' as id from TranferReqHead h inner join grstockdetail c on h.grstockdetailid = c.grstockdetailid " +
"left join userlogin u on h.UserName = u.UserName left join TranferStatus s on h.statusId = s.tranferstatusid " +
"where 1=1 and c.grstockdetailid not in(" + d["GrStockDetailId"].ToString() + ") " + s + " order by Sid desc";
cls = new Class.clsDatabase(d["ip"].ToString(), d["db"].ToString(),
d["dbuser"].ToString(), d["dbpass"].ToString(), "");
stat = d["db"].ToString();
gid = d["GrStockDetailId"].ToString();
dataGridView1.DataSource = cls.QueryDataTable(sql);
}
}
}
else {
foreach (DataRow d in Gr.Rows)
{
sql += "select h.[Sid],isnull(h.SdateId,'-') as SdateId,h.TranferReqHeadid,h.[AbountId],h.[RequisitionId],h.[TranferReqHeadDate], " +
"h.[TranferReqHeadDateSave],c.grstockdetaillname,u.SureName ,s.tranferstatusName, " +
"(select top 1 tranferid from [" + d["db"].ToString() + "].dbo.tranferbetweenbrn where tranferid in(select tranferid from [" + d["db"].ToString() + "].dbo.tranferbetweenbrndt where sdateid = h.sdateid )) as SaleHeadid,h.totalcost,'" + d["GrStockDetailLname"].ToString() + "' as t,'" + d["GrStockDetailId"].ToString() + "' as id from [" + d["db"].ToString() + "].dbo.TranferReqHead h inner join [" + d["db"].ToString() + "].dbo.grstockdetail c on h.grstockdetailid = c.grstockdetailid " +
"left join [" + d["db"].ToString() + "].dbo.userlogin u on h.UserName = u.UserName left join [" + d["db"].ToString() + "].dbo.TranferStatus s on h.statusId = s.tranferstatusid " +
"where 1=1 and c.grstockdetailid not in(" + d["GrStockDetailId"].ToString() + ") " + s + " UNION ALL ";
}
if (stock == 1)
{
cls1 = new Class.clsDatabase("7CSK");
}
else {
cls1 = new Class.clsDatabase("SHOP");
}
sql = "select * from ("+sql.Substring(0, sql.Length - 10) + ") as tt order by Sid desc";
dataGridView1.DataSource = cls1.QueryDataTable(sql);
}
if (dataGridView1.Rows.Count > 0)
{
foreach (DataGridViewRow r in dataGridView1.Rows)
{
if (r.Cells[9].Value.ToString() == "โอนแล้ว")
{
r.DefaultCellStyle.ForeColor = Color.Blue;
}
else if (r.Cells[9].Value.ToString() == "ยกเลิก")
{
r.DefaultCellStyle.ForeColor = Color.Red;
}
}
ColorSort(dataGridView1);
ShowDetail(dataGridView1.Rows[0].Cells[2].Value.ToString(), dataGridView1.Rows[0].Cells[13].Value.ToString());
}
formatHead();
}
private void ShowWeight(string r,string d,string id)
{
/*แสดงน้ำหนักสินค้าของใบเบิกโอนสาขา*/
String local = "select GrStockDetailId,ip,db,dbuser,dbpass,grstockdetaillname FROM GrStockDetail WHERE GrStockdetailId = " + id;
DataTable Gr = Class.DBConnString.clsDB.QueryDataTable(local);
foreach (DataRow dd in Gr.Rows)
{
cls = new Class.clsDatabase(dd["ip"].ToString(), dd["db"].ToString(),
dd["dbuser"].ToString(), dd["dbpass"].ToString(), "");
stat = dd["db"].ToString();
gid = dd["GrStockDetailId"].ToString();
}
String sqlw = "select PackId,Pack,Weight from TranferReqWeight d where d.TranferReqHeadid = " + r + " and productid = '" + d + "'";
if (cls.QueryDataTable(sqlw).Rows.Count > 0)
{
dataGridView3.DataSource = cls.QueryDataTable(sqlw);
ColorSort(dataGridView3);
formatWeight();
}
else {
dataGridView3.DataSource = null;
}
}
private void ShowDetail(string r,string id)
{
/*แสดงรายการสินค้าของใบเบิกโอนสาขา*/
String local = "select GrStockDetailId,ip,db,dbuser,dbpass,grstockdetaillname FROM GrStockDetail WHERE GrStockdetailId = " + id;
DataTable Gr = Class.DBConnString.clsDB.QueryDataTable(local);
foreach (DataRow d in Gr.Rows){
cls = new Class.clsDatabase(d["ip"].ToString(), d["db"].ToString(),
d["dbuser"].ToString(), d["dbpass"].ToString(), "");
stat = d["db"].ToString();
gid = d["GrStockDetailId"].ToString();
}
String sqld = "select d.ProductId,p.ProductName,cast(d.QuantityPack as decimal(10,1)) as SaleAppDetailQuantityPack,up.unitpackname, "+
"cast(d.Quantity as float) as SaleAppDetailQuantity,us.unitsalename,cast(d.Weight as decimal(10,1)) as SaleAppDetailWeight,'กก.' as unit,d.Note,d.costperunit,d.total from TranferReqDetail d " +
"inner join Product p On d.productid = p.productid left join unitpack up on p.unitpackid = up.unitpackid left join unitsale us on "+
"p.unitsaleid = us.unitsaleid where d.TranferReqHeadid = '" + r + "'";
if (cls.QueryDataTable(sqld).Rows.Count > 0)
{
dataGridView2.DataSource = cls.QueryDataTable(sqld);
ShowWeight(r, Convert.ToString(dataGridView2.CurrentRow.Cells[0].Value), Convert.ToString(dataGridView1.CurrentRow.Cells[13].Value));
ColorSort(dataGridView2);
formatDetail();
}
else {
dataGridView2.DataSource = null;
dataGridView3.DataSource = null;
}
}
public TranferAdd(String type, String id, String tw, String qtyp, String qty,String gid)
{
ID = id;
Type = type;
InitializeComponent();
Class.clsDatabase cls = null;
DataTable dt = null;
String sql = "select p.productid,p.productname,u.unitpackname,us.unitsalename from product p left join unitpack u on p.unitpackid = u.unitpackid " +
"left join unitsale us on p.unitsaleid = us.unitsaleid where p.productid = '" + id + "'";
try
{
String local = "select * from GrStockDetail where GrStockDetailId = " + gid;
DataRow d = Class.DBConnString.clsDB.QueryDataTable(local).Rows[0];
if (Class.DBConnString.clsDB.QueryDataTable(local).Rows.Count > 0)
{
cls = new Class.clsDatabase(d["ip"].ToString(), d["db"].ToString(),
d["dbuser"].ToString(), d["dbpass"].ToString(), "");
dt = cls.QueryDataTable(sql);
}
else
{
dt = Class.DBConnString.clsDB.QueryDataTable(sql);
}
}
catch { dt = Class.DBConnString.clsDB.QueryDataTable(sql); }
DataTable dtNew = dt;
foreach (DataRow row in dtNew.Rows)
{
TxtProductId.Text = row["ProductId"].ToString();
TxtProductName.Text = row["ProductName"].ToString();
lblSale1.Text = row["UnitSaleName"].ToString();
lblPack.Text = row["UnitPackName"].ToString();
}
this.Text = "หน้าจอเพิ่มรายการเบิกขายสินค้า";
if (Type == "1")
{
dgvWeight.ColumnCount = 3;
TranferCreate f = new TranferCreate();
f.SetDgvWeightH(tw, dgvWeight, Type);
txtSale.Text = CellSum(1).ToString();
txtWeight.Text = CellSum(2).ToString();
}
if (Type == "2")
{
dgvWeight.ColumnCount = 2;
TranferCreate f = new TranferCreate();
f.SetDgvWeightH(tw, dgvWeight, Type);
txtSale.Text = CellSum(1).ToString();
txtWeight.Text = "0";
}
if (Type == "3")
{
dgvWeight.ColumnCount = 2;
txtPack.Text = qtyp;
txtSale.Text = qty;
dgvWeight.Visible = false;
txtPack.Enabled = true; txtPack.BackColor = Color.White;
txtSale.Enabled = true; txtSale.BackColor = Color.White;
btnAdd.Enabled = false;
btnDel.Enabled = false;
txtRunPack.Enabled = false;
txtWeight.Text = "0";
txtPack.Focus();
txtPack.SelectAll();
}
}
private DataTable DtOfSend(String where)
{
/*แสดงรายงานของใบโอนสาาขาในรูปแบบต่างๆ*/
//if (srpt == "PORC") {
// where += " AND po.purchaseorderstatusid in(0,1) AND (isnull(pod.QuantityRecieve,0) < pod.Quantity) ";
//}
Class.clsDatabase cls = null;
String sql = ""+
"SELECT tbb.TranferId,tbb.TranferDate,tbb.TranferSource,(select gsd.GrStockDetailLname from dbo.GrStockDetail gsd where gsd.GrStockDetailId = tbb.TranferSource) AS TranferSourceName," +
"tbb.TranferDestination,(select gsd.GrStockDetailLname from dbo.GrStockDetail gsd where gsd.GrStockDetailId = tbb.TranferDestination) AS TranferDestinationName,tbb.Total,tbb.Description,tbb.RoundN,tbb.ReqN," +
"tbbd.SdateId,trh.TranferReqHeadId,trh.TranferReqHeadDate,trh.TotalCost,trd.ProductId,p.ProductName,trd.QuantityPack,u.UnitPackName,"+
"trd.Quantity,u2.UnitSaleName,trd.Weight,trd.CostPerUnit,trd.Total,trh.TotalCost,trw.PackId,trw.Pack,trw.Weight "+
"FROM dbo.TranferBetweenBrn tbb INNER JOIN dbo.TranferBetweenBrnDt tbbd "+
"ON tbbd.TranferId = tbb.TranferId INNER JOIN dbo.TranferReqHead trh ON trh.SdateId = tbbd.SdateId "+
"INNER JOIN dbo.TranferReqDetail trd ON trd.TranferReqHeadId = trh.TranferReqHeadId "+
"INNER JOIN dbo.PRODUCT p ON p.ProductId = trd.ProductId INNER JOIN dbo.UNITPACK u ON u.UnitPackId = p.UnitPackId "+
"INNER JOIN dbo.UNITSALE u2 ON u2.UnitSaleId = p.UnitSaleId left JOIN dbo.TranferReqWeight trw ON trw.TranferReqHeadId = trd.TranferReqHeadId "+
"AND trw.ProductId = trd.ProductId where " + where + " order by tbb.TranferDate";
String local = "select * from GrStockDetail where GrStockDetailId = " + comboBox1.SelectedValue;
DataRow dx = Class.DBConnString.clsDB.QueryDataTable(local).Rows[0];
if (Class.DBConnString.clsDB.QueryDataTable(local).Rows.Count > 0)
{
cls = new Class.clsDatabase(dx["ip"].ToString(), dx["db"].ToString(),
dx["dbuser"].ToString(), dx["dbpass"].ToString(), "");
return cls.QueryDataTable(sql);
}
else {
return Class.DBConnString.clsDB.QueryDataTable(sql);
}
}
private void ShowDetailDgv(DataGridViewRow r,String id)
{
/*แสดงรายการสินค้าของใบโอนสาขา*/
Class.clsDatabase cls = null;
DataTable dt = null;
String dbpro = "";
if (Class.DBConnString.sDb == "SHOP") {
dbpro = "shop.dbo.";
}
String sql = "SELECT d.TranferReqHeadId,d.ProductId,p.ProductName,d.QuantityPack,u.UnitPackName,d.Quantity,u2.UnitSaleName,d.Weight,'กก.' as unit,d.ProductAppType, " +
"'' AS t,d.Note,d.CostPerUnit,d.Total "+
"FROM dbo.TranferReqDetail d INNER JOIN dbo.TranferReqHead h ON h.TranferReqHeadId = d.TranferReqHeadId "+
"INNER JOIN " + dbpro + "PRODUCT p ON d.ProductId = p.ProductId LEFT JOIN " + dbpro + "UNITPACK u ON u.UnitPackId = p.UnitPackId " +
"LEFT JOIN " + dbpro + "UNITSALE u2 ON u2.UnitSaleId = p.UnitSaleId " +
"where h.sdateid = '" + r.Cells[0].Value.ToString()+"'";
String local = "select * from GrStockDetail where GrStockDetailId = " + id;
DataRow d = Class.DBConnString.clsDB.QueryDataTable(local).Rows[0];
if (Class.DBConnString.clsDB.QueryDataTable(local).Rows.Count > 0)
{
cls = new Class.clsDatabase(d["ip"].ToString(), d["db"].ToString(),
d["dbuser"].ToString(), d["dbpass"].ToString(), "");
dt = cls.QueryDataTable(sql);
}
else
{
dt = Class.DBConnString.clsDB.QueryDataTable(sql);
}
dgvReceive.DataSource = dt;
HeadGrid();
ShowWeightDgv(dt.Rows[0],Class.Function.GetTextId(textBox4.Text));
}
private void ShowWeightDgv(DataRow r,String id)
{
/*แสดงน้ำหนักสินค้าของใบโอนสาขา*/
Class.clsDatabase cls = null;
DataTable dt = null;
if (dgvReceive.Rows.Count > 0)
{
String sql = "select packid,pack,weight from tranferreqweight w where w.TranferReqHeadId = '" + r[0].ToString() + "' "+
"and w.productid = '" + r[1].ToString() + "'";
//dgvWeight.DataSource = Class.DBConnString.clsDB.QueryDataTable(sql);
String local = "select * from GrStockDetail where GrStockDetailId = " + id;
DataRow d = Class.DBConnString.clsDB.QueryDataTable(local).Rows[0];
if (Class.DBConnString.clsDB.QueryDataTable(local).Rows.Count > 0)
{
cls = new Class.clsDatabase(d["ip"].ToString(), d["db"].ToString(),
d["dbuser"].ToString(), d["dbpass"].ToString(), "");
dgvWeight.DataSource = cls.QueryDataTable(sql);
}
else
{
dgvWeight.DataSource = Class.DBConnString.clsDB.QueryDataTable(sql);
}
dgvWeight.Columns[0].HeaderText = "กระสอบ";
dgvWeight.Columns[1].HeaderText = "ปริมาณ";
dgvWeight.Columns[2].HeaderText = "น้ำหนัก";
}
}
void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
cls = new Class.clsDatabase("192.168.0.3", comboBox1.Text, "KTCNET", "khu@9wbe", "");
if (comboBox1.Text == this.Base)
{
txtNo.Text = this.EditId;
}
else
{
txtNo.Text = CashierId();
}
}
public static unsafe DataRow CostTopReturnPriceAndDate(String Id,String db, String Date = "GETDATE()")
{
if (Date != "GETDATE()")
{
Date = "'" + Date + "'";
}
String sql = "SELECT TOP 1 rd.ReceiveDetailTotal,rd.ReceiveDetailQuantity,rh.DisText,rh.VatType,rh.VatPer,rd.receivedetailpriceunit,rh.receiveheaddate FROM RECEIVE_DETAIL rd " +
"INNER JOIN dbo.RECEIVE_HEAD rh ON rh.ReceiveHeadId = rd.ReceiveHeadId " +
"WHERE rd.ProductId = '" + Id + "' AND rh.ReceiveHeadDate <= " + Date + " " +
"ORDER BY rh.receiveheaddate DESC";
Class.clsDatabase cls = new Class.clsDatabase(db);
DataTable dd = Class.DBConnString.clsDB.QueryDataTable(sql);
if (dd == null) return null;
if (dd.Rows.Count > 0)
{
DataRow dr = dd.Rows[0];
return InFomularPriceAndDate(ref dr);
}
else
{
return null;
}
}
/*แสดงน้ำหนักสินค้าของใบโอนสาขา*/
private void loadGridWg(DataGridViewRow r)
{
Class.clsDatabase cls = null;
String sql = "select PackId,Pack,Weight from TranferReqWeight where tranferreqheadid = '" + r.Cells[0].Value.ToString() + "'" +
" and productid = '"+r.Cells[1].Value.ToString()+"'";
String local = "select * from GrStockDetail where GrStockDetailId = " + dataGridView1.CurrentRow.Cells["TranferSource"].Value.ToString();
DataRow dx = Class.DBConnString.clsDB.QueryDataTable(local).Rows[0];
if (Class.DBConnString.clsDB.QueryDataTable(local).Rows.Count > 0)
{
cls = new Class.clsDatabase(dx["ip"].ToString(), dx["db"].ToString(),
dx["dbuser"].ToString(), dx["dbpass"].ToString(), "");
dataGridView3.DataSource = cls.QueryDataTable(sql);
}
else
{
dataGridView3.DataSource = Class.DBConnString.clsDB.QueryDataTable(sql);
}
dataGridView3.Columns["PackId"].HeaderText = "กระสอบ";
dataGridView3.Columns["Pack"].HeaderText = "ปริมาณ";
dataGridView3.Columns["Weight"].HeaderText = "น้ำหนัก";
}
/*แสดงรายการสินค้าของใบโอนสาขา*/
private void loadGridDt(DataGridViewRow r)
{
String dbpro = "";
if (Class.DBConnString.sDb == "SHOP") {
dbpro = "shop.dbo.";
}
Class.clsDatabase cls = null;
String sql = "select d.tranferreqheadid,d.ProductId,p.productname,d.QuantityPack,up.unitpackname,d.Quantity,us.unitsalename,"+
"d.Weight,d.CostPerUnit,d.Total,d.Note from TranferReqDetail d inner join " + dbpro + "product p on p.productid = d.productid " +
"inner join " + dbpro + "unitpack up on p.unitpackid = up.unitpackid inner join " + dbpro + "unitsale us on p.unitsaleid = us.unitsaleid " +
"where d.tranferreqheadid = "+
"(select tranferreqheadid from tranferreqhead where sdateid = '" + r.Cells[0].Value.ToString() + "')";
String local = "select * from GrStockDetail where GrStockDetailId = " + dataGridView1.CurrentRow.Cells["TranferSource"].Value.ToString();
DataRow dx = Class.DBConnString.clsDB.QueryDataTable(local).Rows[0];
if (Class.DBConnString.clsDB.QueryDataTable(local).Rows.Count > 0)
{
cls = new Class.clsDatabase(dx["ip"].ToString(), dx["db"].ToString(),
dx["dbuser"].ToString(), dx["dbpass"].ToString(), "");
dataGridView2.DataSource = cls.QueryDataTable(sql);
}
else
{
dataGridView2.DataSource = Class.DBConnString.clsDB.QueryDataTable(sql);
}
dataGridView2.Columns["tranferreqheadid"].Visible = false;
dataGridView2.Columns["ProductId"].HeaderText = "รหัสสินค้า";
dataGridView2.Columns["productname"].HeaderText = "ชื่อสินค้า";
dataGridView2.Columns["QuantityPack"].HeaderText = "จำนวนแพ็ค";
dataGridView2.Columns["unitpackname"].HeaderText = "หน่วยแพ็ค";
dataGridView2.Columns["Quantity"].HeaderText = "จำนวน";
dataGridView2.Columns["unitsalename"].HeaderText = "หน่วย";
dataGridView2.Columns["Weight"].HeaderText = "นน./หน่วย";
dataGridView2.Columns["Note"].HeaderText = "หมายเหตุ";
dataGridView2.Columns["CostPerUnit"].HeaderText = "ต้นทุน/หน่วย";
dataGridView2.Columns["Total"].HeaderText = "รวมทั้งสิ้น";
if (dataGridView2.Rows.Count < 1) return;
loadGridWg(dataGridView2.Rows[0]);
}
private void setValue()
{
Class.clsDatabase cls = null;
String sql = "select * from TranferReqHead h left join GrStockDetail c on " +
"h.GrStockDetailId = c.GrStockDetailId left join userlogin u on h.username = u.username " +
"where h.sdateid = '" + SDATEID + "' ";
String con = "select * from grstockdetail where grstockdetailid = " + Db;
DataRow dx = Class.DBConnString.clsDB.QueryDataTable(con).Rows[0];
if (Class.DBConnString.clsDB.QueryDataTable(con).Rows.Count > 0)
{
cls = new Class.clsDatabase(dx["ip"].ToString(), dx["db"].ToString(),
dx["dbuser"].ToString(), dx["dbpass"].ToString(), "");
}
SqlDataReader reader = cls.QueryDataReader(sql);
if (reader.Read())
{
if (reader["Statusid"].ToString() == "2")
{
btnSave.Enabled = true;
}
else
{
btnSave.Enabled = false;
}
txtRec.Text = SDATEID;
dptRecDate.Value = Convert.ToDateTime(reader["TranferReqHeadDate"]);
textBox4.Text = "[" + reader["GrStockDetailId"].ToString() + "] " + reader["GrStockDetailLName"].ToString();
textBox4.Enabled = false;
txtInvoice.Text = reader["surename"].ToString();
textBox3.Text = subAbount(reader["abountid"].ToString(), 0);
textBox2.Text = subAbount(reader["abountid"].ToString(), 1);
textBox1.Text = reader["RequisitionId"].ToString();
}
HeadGrid();
sql = "select * from TranferReqDetail d inner join product p on d.productid = p.productid " +
"inner join unitpack up on p.unitpackid = up.unitpackid inner join unitsale us on " +
"p.unitsaleid = us.unitsaleid inner join TranferReqHead h on d.TranferReqHeadId = h.TranferReqHeadId " +
"where h.sdateid = '" + SDATEID + "'";
reader = cls.QueryDataReader(sql);
int i = 0;
while (reader.Read())
{
String[] addReceive = new String[] { };
String t = "";
t = reader["ProductAppType"].ToString();
addReceive = new String[] {
reader["ProductId"].ToString()+"",//รหัสสินค้า
reader["ProductName"].ToString()+"",//ชื่อสินค้า
reader["QuantityPack"].ToString()+"",//จำนวนแพ็ค
reader["UnitPackName"].ToString()+"",//หน่วยแพ็ค
reader["Quantity"].ToString()+"",//จำนวน/
reader["UnitSaleName"].ToString()+"",//หน่วยขาย
reader["Weight"].ToString()+"",//น้ำหนักรวม
"ก.ก",//หน่วย
t,
"",
reader["Note"].ToString()+"",
reader["CostPerUnit"].ToString()+"",
reader["Total"].ToString()+""
};
dgvReceive.Rows.Add(addReceive);
sql = "select * from TranferReqWeight w inner join TranferReqHead h on w.TranferReqHeadId = h.TranferReqHeadId " +
"where w.productid = '" + reader["ProductId"].ToString() + "' " +
"and h.sdateid = '" + SDATEID + "' ";
SqlDataReader readerw = cls.QueryDataReader(sql);
if (t != "3")
{
while (readerw.Read())
{
dgvReceive.Rows[i].Cells[9].Value += readerw["PackId"].ToString() + "#";
dgvReceive.Rows[i].Cells[9].Value += readerw["Pack"].ToString() + "#";
if (t == "1")
{
dgvReceive.Rows[i].Cells[9].Value += readerw["Weight"].ToString() + "#";
}
}
}
i++;
}
if (dgvReceive.Rows.Count <= 0) return;
SetDgvWeightH(dgvReceive.Rows[0].Cells[9].Value.ToString(), dgvWeight, dgvReceive.Rows[0].Cells[8].Value.ToString());
}
/*ดึงรหัสใบเบิกกับคลังสินค้า*/
private void loadGridRq(DataGridViewRow r)
{
Class.clsDatabase cls = null;
String sql = "select SdateId from TranferBetweenBrnDt where tranferid = '"+r.Cells[0].Value.ToString()+"'";
String local = "select * from GrStockDetail where GrStockDetailId = " + r.Cells["TranferSource"].Value.ToString();
DataRow dx = Class.DBConnString.clsDB.QueryDataTable(local).Rows[0];
if (Class.DBConnString.clsDB.QueryDataTable(local).Rows.Count > 0)
{
cls = new Class.clsDatabase(dx["ip"].ToString(), dx["db"].ToString(),
dx["dbuser"].ToString(), dx["dbpass"].ToString(), "");
dataGridView4.DataSource = cls.QueryDataTable(sql);
}
else
{
dataGridView4.DataSource = Class.DBConnString.clsDB.QueryDataTable(sql);
}
dataGridView4.Columns["SdateId"].HeaderText = "รหัสใบเบิก";
if (dataGridView4.Rows.Count < 1) return;
loadGridDt(dataGridView4.Rows[0]);
}
private void button1_Click(object sender, EventArgs e)
{
String where = "";
if (checkBox1.Checked) {
where += " and TranferReqHeadDate between '" + Class.Function.GetDate(dateTimePicker1.Value) + "' and '"
+ Class.Function.GetDate(dateTimePicker2.Value) + "' ";
}
if (textBox1.Text != "") {
where += " and AbountId = '" + textBox1.Text + "' ";
}
if (textBox2.Text != "")
{
where += " and RequisitionId = '" + textBox2.Text + "' ";
}
if (textBox3.Text != "")
{
where += " and SdateId = '" + textBox3.Text + "' ";
}
string t = "select * from GrStockDetail where grstockdetailid = '" + comboBox1.SelectedValue + "'";
string title = "";
DataRow d = Class.DBConnString.clsDB.QueryDataTable(t).Rows[0];
title = d["GrStockDetailLName"].ToString();
cls = new Class.clsDatabase(d["ip"].ToString(), d["db"].ToString(),d["dbuser"].ToString(), d["dbpass"].ToString(), "");
String sql = "SELECT SaleAppHeadId,SaleAppDetailQuantityPack,SaleAppDetailQuantity,SaleAppDetailWeight,ProductName,"+
"SaleAppHeadDate,TitleName,CustomerName,FirstName,UserName,SaleAppPackId,SurName,ProductId,ProductDescript,ProductNameEng,"+
"SaleAppWeight,SaleAppPack,AbountId,RequisitionId FROM ("+
"SELECT TranferReqDetail.TranferReqDetailId,isnull(SdateId,TranferReqHead.TranferReqHeadId) as SaleAppHeadId, "+
"TranferReqDetail.QuantityPack as SaleAppDetailQuantityPack, "+
"TranferReqDetail.Quantity as SaleAppDetailQuantity, "+
" TranferReqDetail.Weight as SaleAppDetailWeight, "+
"TranferReqWeight.Weight as SaleAppWeight, "+
"TranferReqWeight.Pack as SaleAppPack, "+
"PRODUCT.ProductName, "+
"CONVERT(VARCHAR(10),TranferReqHead.TranferReqHeadDate,105) AS SaleAppHeadDate, "+ //SALE_APP_HEAD.SaleAppHeadDate
" '" + title + "' AS TitleName, "+
" GrStockDetail.GrStockDetailLName as CustomerName, "+
"UNITSALE.UnitSaleName AS FirstName, "+
"UNITPACK.UnitPackName AS UserName, "+
"TranferReqWeight.PackId as SaleAppPackId, "+//convert (int, A.my_NvarcharColumn) FROM A
"'ก.ก' AS SurName, "+
"PRODUCT.ProductId,TranferReqDetail.costperunit as productdescript, "+
"TranferReqDetail.Note AS ProductNameEng,AbountId,RequisitionId "+
"FROM TranferReqHead "+
"INNER JOIN TranferReqDetail ON TranferReqHead.TranferReqHeadId = TranferReqDetail.TranferReqHeadId "+
"INNER JOIN TranferReqWeight ON TranferReqDetail.ProductId = TranferReqWeight.ProductId "+
"INNER JOIN PRODUCT ON TranferReqDetail.ProductId = PRODUCT.ProductId "+
"INNER JOIN GrStockDetail ON TranferReqHead.GrStockDetailId = GrStockDetail.GrStockDetailId "+
"INNER JOIN UNITSALE ON PRODUCT.UnitSaleId = UNITSALE.UnitSaleId "+
"INNER JOIN UNITPACK ON PRODUCT.UnitPackId = UNITPACK.UnitPackId "+
"where TranferReqHead.TranferReqHeadId = TranferReqWeight.TranferReqHeadId "+
where +
"AND TranferReqDetail.ProductAppType = 1 and TranferReqHead.statusId<>1 " +
//////////////////////////////////////////////////////////////////////////////////////////////////
"UNION all "+
"SELECT TranferReqDetail.TranferReqDetailId,isnull(SdateId,TranferReqHead.TranferReqHeadId) as SaleAppHeadId, "+
"TranferReqDetail.QuantityPack, "+
"TranferReqDetail.Quantity, "+
"TranferReqDetail.Weight, "+
"TranferReqWeight.Weight, "+
"TranferReqWeight.Pack, "+
"PRODUCT.ProductName, "+
"CONVERT(VARCHAR(10),TranferReqHead.TranferReqHeadDate,105), "+ //SALE_APP_HEAD.SaleAppHeadDate
" '" + title + "', "+
" GrStockDetail.GrStockDetailLName, "+
"UNITSALE.UnitSaleName, "+
"UNITPACK.UnitPackName, "+
"TranferReqWeight.PackId,"+
"'', "+
"PRODUCT.ProductId,TranferReqDetail.costperunit, "+
"TranferReqDetail.Note,AbountId,RequisitionId "+
"FROM TranferReqHead "+
"INNER JOIN TranferReqDetail ON TranferReqHead.TranferReqHeadId = TranferReqDetail.TranferReqHeadId "+
"INNER JOIN TranferReqWeight ON TranferReqDetail.ProductId = TranferReqWeight.ProductId "+
"INNER JOIN PRODUCT ON TranferReqDetail.ProductId = PRODUCT.ProductId "+
"INNER JOIN GrStockDetail ON TranferReqHead.GrStockDetailId = GrStockDetail.GrStockDetailId "+
"INNER JOIN UNITSALE ON PRODUCT.UnitSaleId = UNITSALE.UnitSaleId "+
"INNER JOIN UNITPACK ON PRODUCT.UnitPackId = UNITPACK.UnitPackId "+
"where TranferReqHead.TranferReqHeadId = TranferReqWeight.TranferReqHeadId "+
where +
"AND TranferReqDetail.ProductAppType = 2 and TranferReqHead.statusId<>1 " +
//"order by SALE_APP_HEAD.SaleAppHeadId "
/////////////////////////////////////////////////////////////////////////////////////////////////////////
"UNION all "+
"SELECT TranferReqDetail.TranferReqDetailId,isnull(SdateId,TranferReqHead.TranferReqHeadId) as SaleAppHeadId, "+
"TranferReqDetail.QuantityPack, "+
"TranferReqDetail.Quantity, "+
"TranferReqDetail.Weight, "+
" '', "+
" '', "+
"PRODUCT.ProductName, "+
" CONVERT(VARCHAR(10),TranferReqHead.TranferReqHeadDate,105), "+
" '" + title + "', "+
" GrStockDetail.GrStockDetailLName, "+
"UNITSALE.UnitSaleName , "+
"UNITPACK.UnitPackName , "+
"'', "+
"'', "+
"PRODUCT.ProductId,TranferReqDetail.costperunit, "+
"TranferReqDetail.Note,AbountId,RequisitionId "+
"FROM TranferReqHead "+
" INNER JOIN TranferReqDetail ON TranferReqHead.TranferReqHeadId = TranferReqDetail.TranferReqHeadId "+
"INNER JOIN PRODUCT ON TranferReqDetail.ProductId = PRODUCT.ProductId "+
"INNER JOIN GrStockDetail ON TranferReqHead.GrStockDetailId = GrStockDetail.GrStockDetailId "+
"INNER JOIN UNITSALE ON PRODUCT.UnitSaleId = UNITSALE.UnitSaleId "+
"INNER JOIN UNITPACK ON PRODUCT.UnitPackId = UNITPACK.UnitPackId "+
"where TranferReqDetail.ProductAppType = 3 and TranferReqHead.statusId<>1 " +
where +
") AS T WHERE T.SaleAppHeadId LIKE '%"+d["Code"].ToString().Substring(2,1)+"%' ORDER BY ProductId";
DataTable dd = Class.DBConnString.clsDB.QueryDataTable(sql);
printAllProduct p = new printAllProduct(dd, textBox1.Text == "" ? false : true);
p.Show();
}
private void ยกเลกใบเบกToolStripMenuItem_Click(object sender, EventArgs e)
{
Class.clsDatabase cls = null;
Class.clsDatabase cls2 = null;
List<string> up = new List<string>();//ต้นทาง
List<string> upDes = new List<string>();//ปลายทาง
foreach (DataGridViewRow d in dataGridView1.SelectedRows) {
if (d.Cells["confirmName"].Value.ToString() == "อนุมัติ") {
MetroFramework.MetroMessageBox.Show(this,"รายการที่ถูกอนุมัติไม่สามารถยกเลิกได้");
return;
}
}
String local = "select * from GrStockDetail where GrStockDetailId = " + dataGridView1.CurrentRow.Cells["TranferSource"].Value.ToString();
DataRow dx = Class.DBConnString.clsDB.QueryDataTable(local).Rows[0];
String local2 = "select * from GrStockDetail where GrStockDetailId = " + dataGridView1.CurrentRow.Cells["TranferDestination"].Value.ToString();
DataRow dx2 = Class.DBConnString.clsDB.QueryDataTable(local2).Rows[0];
if (Class.DBConnString.clsDB.QueryDataTable(local).Rows.Count > 0)
{
cls = new Class.clsDatabase(dx["ip"].ToString(), dx["db"].ToString(),
dx["dbuser"].ToString(), dx["dbpass"].ToString(), "");
}
if (Class.DBConnString.clsDB.QueryDataTable(local2).Rows.Count > 0)
{
cls2 = new Class.clsDatabase(dx2["ip"].ToString(), dx2["db"].ToString(),
dx2["dbuser"].ToString(), dx2["dbpass"].ToString(), "");
}
if (!cls.IsConnected())
{
MessageBox.Show("ไม่สามารถเชื่อมต่อต้นทาง การบันทึกข้อมูลล้มเหลว กรุณาตรวจสอบเครือข่าย");
return;
}
if (!cls2.IsConnected())
{
MessageBox.Show("ไม่สามารถเชื่อมต่อปลายทาง การบันทึกข้อมูลล้มเหลว กรุณาตรวจสอบเครือข่าย");
return;
}
foreach (DataGridViewRow d in dataGridView1.SelectedRows) {
if (d.Cells["tranferstatusName"].Value.ToString() == "ปกติ")
{
//String
//up.Add("update tranferreqhead set statusid = 2 where sdateid in('" + d.Cells[0].Value.ToString() + "')");
upDes.Add("DELETE FROM TranferReqDetail where tranferreqheadid in(select tranferreqheadid from tranferreqhead WHERE Sdateid in(select SdateId from TranferBetweenBrnDt where tranferid = '" + d.Cells["tranferid"].Value.ToString() + "')) ");
upDes.Add("DELETE FROM TranferReqHead WHERE Sdateid in(select SdateId from TranferBetweenBrnDt where tranferid = '" + d.Cells["tranferid"].Value.ToString() + "') ");
upDes.Add("DELETE FROM TranferBetweenBrnDt WHERE tranferid = '" + d.Cells["tranferid"].Value.ToString() + "' ");
upDes.Add("DELETE FROM TranferBetweenBrn WHERE tranferid = '" + d.Cells["tranferid"].Value.ToString() + "' ");
up.Add("update tranferbetweenbrn set statusid = 1 where tranferid = '" + d.Cells[0].Value.ToString() + "'");
foreach (DataGridViewRow x in dataGridView4.Rows) {
up.Add("update TranferReqHead set statusid = 2 where SdateId = '" + x.Cells[0].Value.ToString() + "'");
}
}
else {
string sql = "select * from TranferBetweenBrn where tranferid = '" + d.Cells["tranferid"].Value.ToString() + "' ";
DataRow ds = Class.DBConnString.clsDB.QueryDataTable(sql).Rows[0];
upDes.Add("INSERT INTO TranferBetweenBrn(TranferId,TranferDate,TranferDateSave,TranferSource,TranferDestination,total,RoundN,ReqN,Description,confirmId,UserName,statusid) " +
"VALUES('" + ds["tranferid"] + "','" + Class.Function.GetDate(Convert.ToDateTime(ds["TranferDate"])) + "',GETDATE()," + ds["TranferSource"] + "," + ds["TranferDestination"] + "," + ds["total"] + ",'" + ds["RoundN"] + "','" + ds["ReqN"] + "','" + ds["Description"] + "',1,'" + ds["UserName"] + "',0)");
up.Add("update tranferbetweenbrn set statusid = 0 where tranferid = '" + d.Cells[0].Value.ToString() + "'");
Int64 runid = Convert.ToInt64(getSaleAppHeadId(cls2));
foreach (DataGridViewRow x in dataGridView4.Rows)
{
upDes.Add("INSERT INTO TranferBetweenBrnDt(TranferId,SdateId) VALUES ('" + d.Cells["tranferid"].Value.ToString() + "','" + x.Cells[0].Value.ToString() + "')");
up.Add("update TranferReqHead set statusid = 3 where SdateId = '" + x.Cells[0].Value.ToString() + "'");
String xx = "select * from tranferreqhead where sdateid = '" + x.Cells[0].Value.ToString() + "'";
DataRow dd = Class.DBConnString.clsDB.QueryDataTable(xx).Rows[0];
upDes.Add("INSERT INTO TranferReqHead(SdateId,TranferReqHeadId,TranferReqHeadDate,TranferReqHeadDateSave,GrStockDetailId,UserName,AbountId,RequisitionId,statusId,Descript,TotalCost) " +
"VALUES('" + dd["SdateId"] + "','" + runid + "','" + Class.Function.GetDate(Convert.ToDateTime(dd["TranferReqHeadDate"])) + "',getdate()," + dd["GrStockDetailId"] + ",'" + dd["UserName"] + "','" +
dd["AbountId"] + "','" + dd["RequisitionId"] + "'," + dd["statusId"] + ",'" + dd["Descript"] + "'," + dd["TotalCost"] + ")");
String xd = "select * from tranferreqdetail where TranferReqHeadId = '" + dd["TranferReqHeadId"] + "'";
DataTable ddd = cls.QueryDataTable(xd);
foreach (DataRow xdd in ddd.Rows)
{
sql = "INSERT INTO TranferReqDetail(TranferReqHeadId,ProductId,ProductAppType,QuantityPack,Quantity,Weight,Note,costperunit,total) " +
"VALUES ('" + runid + "','" + xdd["ProductId"] + "'," + xdd["ProductAppType"] + "," + xdd["QuantityPack"] + "," + xdd["Quantity"] + "," + xdd["Weight"] + ",'" + xdd["Note"] + "'," + xdd["costperunit"] + "," + xdd["total"] + ")";
upDes.Add(sql);
}
runid++;
}
}
}
if (cls.ExecuteTransaction(up.ToArray())){
if (cls2.ExecuteTransaction(upDes.ToArray()))
{
MetroFramework.MetroMessageBox.Show(this, "ปรับสถานะรายการที่เลือกแล้ว", "ปรับสถานะใบโอน", MessageBoxButtons.OK, MessageBoxIcon.Warning);
loadGridHd();
}
}
}
/*บันทึกใบโอนสาขาลงในฐานข้อมูล*/
private void insertAndUpdate()
{
String sql = "";
List<String> ListSql = new List<String>();
String rid = txtRec.Text;
//if (rid != saleId() && rid != saleId(1))
//{
//DialogResult dialogResult = MessageBox.Show("รหัส " + rid + " ถูกใช้แล้ว หากกด YES ระบบจะบันทึกและสร้างรหัสอัตโนมัติ หากกด NO ระบบจะยังไม่บันทึก แต่จะเรียกรหัสใหม่อีกครั้ง", "แจ้งเตือน", MessageBoxButtons.YesNo);
//if (dialogResult == DialogResult.Yes)
//{
//rid = saleId();
//}
//}
if (tid!="")
{
sql = "update TranferReqHead set statusid = 2 where Sdateid in(select SdateId from TranferBetweenBrnDt where tranferid = '"+tid+"')";
ListSql.Add(sql);
sql = "DELETE FROM TranferBetweenBrnDt WHERE tranferid = '" + rid + "' ";
ListSql.Add(sql);
sql = "DELETE FROM TranferBetweenBrn WHERE tranferid = '" + rid + "' ";
ListSql.Add(sql);
}
String t1 = Class.Function.GetTextId(textBox4.Text);
String t2 = Class.Function.GetTextId(txt777.Text);
String rdate = Class.Function.GetDate(dptRecDate.Value);
String round = textBox3.Text + textBox2.Text;
String number = textBox1.Text;
String UserName = Class.clsCash.sUserIdLogin;
String Description = TxtDescription.Text;
String querytotal = "select isnull(sum(totalcost),0) as total from TranferReqHead where SdateId in(";
int i = 0;
foreach (DataGridViewRow d in dataGridView1.Rows) {
if (i == 0)
{
querytotal += "'" + d.Cells[0].Value.ToString() + "'";
}
else
{
querytotal += ",'" + d.Cells[0].Value.ToString() + "'";
}
i++;
}
querytotal += ")";
Class.clsDatabase cls = null;
Class.clsDatabase cls2 = null;
String sum = "";
String local = "select * from GrStockDetail where GrStockDetailId = " + Class.Function.GetTextId(textBox4.Text);
String local2 = "select * from GrStockDetail where GrStockDetailId = " + Class.Function.GetTextId(txt777.Text);
DataRow dx = Class.DBConnString.clsDB.QueryDataTable(local).Rows[0];
DataRow dx2 = Class.DBConnString.clsDB.QueryDataTable(local2).Rows[0];
if (Class.DBConnString.clsDB.QueryDataTable(local).Rows.Count > 0)
{
cls = new Class.clsDatabase(dx["ip"].ToString(), dx["db"].ToString(),
dx["dbuser"].ToString(), dx["dbpass"].ToString(), "");
sum = cls.QueryExecuteScalarNonAlert(querytotal).ToString();
}
if (Class.DBConnString.clsDB.QueryDataTable(local2).Rows.Count > 0)
{
cls2 = new Class.clsDatabase(dx2["ip"].ToString(), dx2["db"].ToString(),
dx2["dbuser"].ToString(), dx2["dbpass"].ToString(), "");
sum = cls.QueryExecuteScalarNonAlert(querytotal).ToString();
}
sql = "INSERT INTO TranferBetweenBrn(TranferId,TranferDate,TranferDateSave,TranferSource,TranferDestination,total,RoundN,ReqN,Description,confirmId,UserName,statusid) " +
"VALUES('" + rid + "','" + rdate + "',GETDATE()," + t1 + "," + t2 + "," + sum + ",'" + round + "','" + number + "','" + Description + "',1,'" + UserName + "',0)";
ListSql.Add(sql);
//String sum = Class.DBConnString.clsDB.QueryExecuteScalarNonAlert(querytotal);
List<string> listPro = ProductListOld.Select(n => n.ProductId).Distinct().ToList();
//foreach (var list in listPro)
//{
foreach (DataGridViewRow row in dataGridView1.Rows)
{
String reqid = row.Cells[0].Value.ToString();
if (listPro.Contains(reqid))
{
sql = "INSERT INTO TranferBetweenBrnDt(TranferId,SdateId) VALUES ('" + rid + "','" + reqid + "')";
ListSql.Add(sql);
sql = "update TranferReqHead set statusid = 3 where SdateId = '" + reqid + "'";
ListSql.Add(sql);
}
else {
sql = "update TranferReqHead set statusid = 2 where SdateId = '" + reqid + "'";
ListSql.Add(sql);
}
}
//}
if (MetroFramework.MetroMessageBox.Show(this,"คุณต้องการบันทึกข้อมูลใบโอนใหม่เลขที่ " + rid + "\nใช่หรือไม่ ?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
if (!cls.IsConnected())
{
MessageBox.Show("ไม่สามารถเชื่อมต่อต้นทาง การบันทึกข้อมูลล้มเหลว กรุณาตรวจสอบเครือข่าย");
return;
}
if (!cls2.IsConnected())
{
MessageBox.Show("ไม่สามารถเชื่อมต่อปลายทาง การบันทึกข้อมูลล้มเหลว กรุณาตรวจสอบเครือข่าย");
return;
}
if (cls.ExecuteTransaction(ListSql.ToArray()))
{
if (tid != "")
{
string ctn = "select count(dbo.TranferReqDetail.TranferReqDetailId) from TranferReqDetail where tranferreqheadid "+
"in(select tranferreqheadid from tranferreqhead WHERE Sdateid in(select SdateId from TranferBetweenBrnDt where "+
"tranferid = '" + tid + "'))";
if (cls2.QueryExecuteScalar(ctn) != "0")
{
sql = "DELETE FROM TranferReqDetail where tranferreqheadid in(select tranferreqheadid from tranferreqhead WHERE Sdateid in(select SdateId from TranferBetweenBrnDt where tranferid = '" + tid + "')) ";
ListSql.Add(sql);
}
ctn = "select count(dbo.TranferReqHead.SdateId) from TranferReqHead WHERE Sdateid " +
"in(select SdateId from TranferBetweenBrnDt where tranferid = '" + tid + "')";
if (cls2.QueryExecuteScalar(ctn) != "0")
{
sql = "DELETE FROM TranferReqHead WHERE Sdateid in(select SdateId from TranferBetweenBrnDt where tranferid = '" + tid + "') ";
ListSql.Add(sql);
}
}
Int64 runid = Convert.ToInt64(getSaleAppHeadId(cls2));
foreach (DataGridViewRow row in dataGridView1.Rows)
{
String reqid = row.Cells[0].Value.ToString();
if (listPro.Contains(reqid))
{
String x = "select * from tranferreqhead where sdateid = '" + reqid + "'";
DataRow d = cls.QueryDataTable(x).Rows[0];
sql = "INSERT INTO TranferReqHead(SdateId,TranferReqHeadId,TranferReqHeadDate,TranferReqHeadDateSave,GrStockDetailId,UserName,AbountId,RequisitionId,statusId,Descript,TotalCost) " +
"VALUES('" + reqid + "','" + runid + "','" + Class.Function.GetDate(Convert.ToDateTime(d["TranferReqHeadDate"])) + "','" + Class.Function.GetDate(Convert.ToDateTime(d["TranferReqHeadDateSave"])) + "'," + d["GrStockDetailId"] + ",'" + d["UserName"] + "','" + d["AbountId"] + "','" + d["RequisitionId"] + "'," + d["statusId"] + ",'" + d["Descript"] + "'," + d["TotalCost"] + ")";
ListSql.Add(sql);
String xd = "select * from tranferreqdetail where TranferReqHeadId = '" + d["TranferReqHeadId"] + "'";
DataTable dd = cls.QueryDataTable(xd);
foreach (DataRow xdd in dd.Rows)
{
sql = "INSERT INTO TranferReqDetail(TranferReqHeadId,ProductId,ProductAppType,QuantityPack,Quantity,Weight,Note,costperunit,total) " +
"VALUES ('" + runid + "','" + xdd["ProductId"] + "'," + xdd["ProductAppType"] + "," + xdd["QuantityPack"] + "," + xdd["Quantity"] + "," + xdd["Weight"] + ",'" + xdd["Note"] + "'," + xdd["costperunit"] + "," + xdd["total"] + ")";
ListSql.Add(sql);
}
runid++;
}
}
if (cls2.ExecuteTransaction(ListSql.ToArray()))
{
//if (MessageBox.Show("บันทึกสำเร็จ คุณต้องการพิมพ์รายงาน " + rid + "\nใช่หรือไม่ ?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
//{
// Requisition.rptSaleApp rpt = new Requisition.rptSaleApp();
// rpt.StartPosition = FormStartPosition.CenterScreen;
// rpt.setSaleAppHeadId = Convert.ToInt32(idHead);
// rpt.ShowDialog();
//}
retuenNulltext();
this.DialogResult = DialogResult.OK;
}
}
}
}
private void อนมตToolStripMenuItem_Click(object sender, EventArgs e)
{
Class.clsDatabase cls = null;
List<string> up = new List<string>();
foreach (DataGridViewRow d in dataGridView1.SelectedRows)
{
if (d.Cells["tranferstatusName"].Value.ToString() == "ยกเลิก")
{
MetroFramework.MetroMessageBox.Show(this, "รายการที่ถูกยกเลิกไม่สามารถอนุมัติได้");
return;
}
}
foreach (DataGridViewRow d in dataGridView1.SelectedRows)
{
if (d.Cells["confirmName"].Value.ToString() == "รออนุมัติ")
{
up.Add("update tranferbetweenbrn set confirmId = 3 where tranferid = '" + d.Cells[0].Value.ToString() + "'");
}
else
{
up.Add("update tranferbetweenbrn set confirmId = 1 where tranferid = '" + d.Cells[0].Value.ToString() + "'");
}
}
String local = "select * from GrStockDetail where GrStockDetailId = " + dataGridView1.CurrentRow.Cells["TranferSource"].Value.ToString();
DataRow dx = Class.DBConnString.clsDB.QueryDataTable(local).Rows[0];
String local2 = "select * from GrStockDetail where GrStockDetailId = " + dataGridView1.CurrentRow.Cells["TranferDestination"].Value.ToString();
DataRow dx2 = Class.DBConnString.clsDB.QueryDataTable(local2).Rows[0];
if (Class.DBConnString.clsDB.QueryDataTable(local).Rows.Count > 0)
{
cls = new Class.clsDatabase(dx["ip"].ToString(), dx["db"].ToString(),
dx["dbuser"].ToString(), dx["dbpass"].ToString(), "");
if (cls.ExecuteTransaction(up.ToArray()))
{
cls = new Class.clsDatabase(dx2["ip"].ToString(), dx2["db"].ToString(),
dx2["dbuser"].ToString(), dx2["dbpass"].ToString(), "");
if (cls.ExecuteTransaction(up.ToArray()))
{
MetroFramework.MetroMessageBox.Show(this, "ปรับสถานะรายการที่เลือกแล้ว", "ปรับสถานะใบโอน", MessageBoxButtons.OK, MessageBoxIcon.Warning);
loadGridHd();
}
}
}
else
{
if (Class.DBConnString.clsDB.ExecuteTransaction(up.ToArray()))
{
MetroFramework.MetroMessageBox.Show(this, "ปรับสถานะรายการที่เลือกแล้ว", "ปรับสถานะใบโอน", MessageBoxButtons.OK, MessageBoxIcon.Warning);
loadGridHd();
}
}
}
private void toolStripButton2_Click(object sender, EventArgs e)
{
String s = "";
dataGridView1.DataSource = null;
dataGridView2.DataSource = null;
dataGridView3.DataSource = null;
if (textBox1.Text != "") {
s += " and (SdateId like '%" + textBox1.Text + "%' OR tranferreqheadid like '%" + textBox1.Text + "%') ";
}
if (textBox2.Text != "")
{
s += " and u.SureName like '%" + textBox2.Text + "%' ";
}
if (textBox3.Text != "")
{
s += " and c.grstockdetailid = " + Class.Function.GetTextId(textBox3.Text) + " ";
}
if (textBox4.Text != "")
{
s += " and (AbountId like '%" + textBox4.Text + "%' OR RequisitionId like '%" + textBox4.Text + "%') ";
}
if (checkBox3.Checked == true) {
s += " and TranferReqHeadDate between '" + Class.Function.GetDate(dateTimePicker1.Value) + "' and '" + Class.Function.GetDate(dateTimePicker2.Value) + "' ";
}
if (checkBox4.Checked == true)
{
s += " and TranferReqHeadDateSave between '" + Class.Function.GetDate(dateTimePicker3.Value) + " 00:00:00' and '" + Class.Function.GetDate(dateTimePicker4.Value) + " 23:59:59' ";
}
String sql = "select [Sid],isnull(SdateId,'-') as SdateId,TranferReqHeadid,[AbountId],[RequisitionId],[TranferReqHeadDate], "+
"[TranferReqHeadDateSave],c.grstockdetaillname,u.SureName ,s.tranferstatusName, "+
"'' as SaleHeadid,h.totalcost from TranferReqHead h inner join grstockdetail c on h.grstockdetailid = c.grstockdetailid "+
"left join userlogin u on h.UserName = u.UserName left join TranferStatus s on h.statusId = s.tranferstatusid "+
"where 1=1 and c.db <> '" + Class.DBConnString.sDb + "' " + s + " order by Sid desc";
String local = "select GrStockDetailId,ip,db,dbuser,dbpass FROM GrStockDetail WHERE GrStockId = " + stock;
if (Class.DBConnString.clsDB.QueryDataTable(local).Rows.Count > 0) {
foreach (DataRow d in Class.DBConnString.clsDB.QueryDataTable(local).Rows) {
if (d["db"].ToString() == Class.DBConnString.sDb)
{
cls = new Class.clsDatabase(d["ip"].ToString(), d["db"].ToString(),
d["dbuser"].ToString(), d["dbpass"].ToString(), "");
stat = d["db"].ToString();
gid = d["GrStockDetailId"].ToString();
}
}
}
dataGridView1.DataSource = cls.QueryDataTable(sql);
if (dataGridView1.Rows.Count > 0)
{
foreach (DataGridViewRow r in dataGridView1.Rows)
{
if (r.Cells[9].Value.ToString() == "โอนแล้ว")
{
r.DefaultCellStyle.ForeColor = Color.Blue;
}
else if (r.Cells[9].Value.ToString() == "ยกเลิก")
{
r.DefaultCellStyle.ForeColor = Color.Red;
}
}
ColorSort(dataGridView1);
ShowDetail(dataGridView1.Rows[0].Cells[2].Value.ToString());
}
formatHead();
}
