/*บันทึกข้อมูลใบรับสินค้าอ้างอิงใบสั่งซื้อ*/
private void insertAndUpdate()
{
try
{
String sql = "";
List<String> ListSql = new List<String>();
String rid = txtRec.Text;
String oldSql = "select d.purchaseorderdetailid from receive_detail d where d.receiveheadid = " + rid;
String oldSqlPo = "select d.purchaseoderid from receive_detail d where d.receiveheadid = " + rid + " group by d.purchaseoderid ";
List<String> ListSqlQtyX = new List<String>();
String arraydetailid = "";
DataTable oldPoX = DBConnString.clsDB.QueryDataTable(oldSqlPo);
DataTable oldDtX = Class.DBConnString.clsDB.QueryDataTable(oldSql);
if (dgvReceive.Rows.Count > 0)
{
arraydetailid += dgvReceive.Rows[0].Cells[0].Value.ToString();
for (int i = 1; i < dgvReceive.Rows.Count; i++)
{
arraydetailid += "," + dgvReceive.Rows[i].Cells[0].Value.ToString();
}
}
if (arraydetailid == "")
{
arraydetailid = "0";
}
foreach (DataRow row in oldDtX.Rows)
{
String PurchaseOrderDetailId = row[0].ToString();
String sqlSelectQty = "SELECT Case when PurchaseOrderDetailId not in(" + arraydetailid + ") then isnull(sum(ReceiveDetailQuantity),0) - " +
"(SELECT rd.ReceiveDetailQuantity FROM RECEIVE_DETAIL rd WHERE rd.ReceiveHeadId = " + rid + " AND rd.PurchaseOrderDetailId = " +
"Receive_Detail.PurchaseOrderDetailId) else isnull(sum(ReceiveDetailQuantity),0) end as q FROM Receive_Detail Where PurchaseOrderDetailId = " + PurchaseOrderDetailId + " GROUP BY PurchaseOrderDetailId";
Double a = Convert.ToDouble(Class.DBConnString.clsDB.QueryExecuteScalar(sqlSelectQty));
String sqlUpdateQty = "Update PurchaseOrderDetail Set QuantityRecieve = " + a + ",PurchaseOrderStatusid = " +
"CASE WHEN " + Convert.ToDouble(a) + " = 0 THEN 0 " +
"WHEN " + Convert.ToDouble(a) + " > 0 and " + Convert.ToDouble(a) + " < Quantity THEN 1 " +
"WHEN " + Convert.ToDouble(a) + " >= Quantity THEN 2 " +
"ELSE 0 END " +
"Where PurchaseOrderDetailId = '" + PurchaseOrderDetailId + "'";
ListSqlQtyX.Add(sqlUpdateQty);
}
if (DBConnString.clsDB.ExecuteTransaction(ListSqlQtyX.ToArray()))
{
List<String> ListSqlstsX = new List<String>();
foreach (DataRow rowp in oldPoX.Rows)
{
String Pidx = rowp[0].ToString();
String sts = "SELECT CASE WHEN sum(p.purchaseorderstatusid) = 0 THEN 0 " +
"WHEN COUNT(*)*2 = SUM(p.purchaseorderstatusid) THEN 2 " +
"ELSE 1 END " +
"FROM PurchaseOrderDetail p WHERE p.PurchaseOrderId = '" + Pidx + "'";
String up = "update purchaseorder set purchaseorderstatusid = " + Class.DBConnString.clsDB.QueryExecuteScalar(sts) + " where purchaseorderid = '" + Pidx + "'";
ListSqlstsX.Add(up);
}
DBConnString.clsDB.ExecuteTransaction(ListSqlstsX.ToArray());
}
if (txtRec.Enabled == true)
{
sql = "select ReceiveHeadId from Receive_Head where ReceiveHeadId = " + txtRec.Text;
if (DBConnString.clsDB.QueryExecuteScalar(sql) != null)
{
sql = "DELETE FROM Receive_weight WHERE ReceiveHeadId = '" + rid + "' ";
ListSql.Add(sql);
sql = "DELETE FROM Receive_detail WHERE ReceiveHeadId = '" + rid + "' ";
ListSql.Add(sql);
sql = "DELETE FROM Receive_Head WHERE ReceiveHeadId = '" + rid + "' ";
ListSql.Add(sql);
sql = "DELETE FROM RECEIVE_SHIP WHERE ReceiveHeadId = '" + rid + "' and shipType = '" + shipType + "' ";
ListSql.Add(sql);
}
}
String supid = Class.Function.GetTextId(txtSupplier.Text);
String shipid = Class.Function.GetTextId(txtShipper.Text);
String empid = Class.Function.GetTextId(txtEmployer.Text);
String rdate = Class.Function.GetDate(dptRecDate.Value);
String supbill = Class.Function.GetDate(dptDateBillSupplier.Value);
String shipbill = Class.Function.GetDate(dptDateBillShipping.Value);
String cd = cboDrawId.SelectedValue.ToString();
String ct = cboReceiveType.SelectedValue.ToString();
String drowid = txtDrawId.Text;
String packid = textBox1.Text;
String shipping = txtShipping.Text;
String invoice = txtInvoice.Text;
String UserName = clsCash.sUserIdLogin;
String PriceTotal = Convert.ToDouble(TxtPriceTotal.Text.Trim()) + "";
String DisText = TxtDiscountText.Text.Trim();
String Dis = Convert.ToDouble(TxtDiscount.Text.Trim()) + "";
String VatBase = Convert.ToDouble(TxtBaseVat.Text.Trim()) + "";
String VatType = (CmboxVat.SelectedIndex == 0) ? "NO" : (CmboxVat.SelectedIndex == 1) ? "EX" : "IN";
String VatPer = Convert.ToDouble(TxtPercentVat.Text.Trim()) + "";
String Vat = Convert.ToDouble(TxtVat.Text.Trim()) + "";
String TotalPrice = Convert.ToDouble(TxtAmountTotal.Text.Trim()) + "";
//String Trans = Convert.ToDouble(txtTrans.Text.Trim()) + "";
String Description = TxtDescription.Text.Trim();
Decimal Credit = numericUpDown1.Value;
String DueDate = Class.Function.GetDate(dateTimePicker1.Value);
sql = "INSERT INTO RECEIVE_HEAD(ReceiveHeadId,ReceiveHeadDate,ReceiveHeadDateTime,ShipperId,UserName,ReceiveTypeId,DrawId,LocationProductId,EmployerId," +
"ConfirmId,PriceTotal,DisText,Dis,VatBase,VatType,VatPer,Vat,TotalPrice," +
"[Description],SupplierId,DateBillShipping,DateBillSupplier,ShippingId,Invoice,Packing,CreditTerm,DueDate,BranchId,DueStatus)" +
"VALUES(" + rid + ",'" + rdate + "',GETDATE(),'" + shipid + "','" + UserName + "'," + ct + ",'" + drowid + "'," + cd + ",'" + empid + "'," +
"1," + PriceTotal + ",'" + DisText + "'," + Dis + "," + VatBase + ",'" + VatType + "'," + VatPer + "," + Vat + "," + TotalPrice + "," +
"'" + Description + "'," + supid + ",'" + shipbill + "','" + supbill + "','" + shipping + "','" + invoice + "','" + packid + "'," + Credit + ",'" + DueDate + "',"+Class.Function.GetTextId(textBox2.Text)+","+((checkBox1.Checked)?1:0)+")";
ListSql.Add(sql);
foreach (DataGridViewRow row in dgvReceive.Rows)
{
String PurchaseOrderDetailId = row.Cells[0].Value.ToString() + "";
String ProductId = row.Cells[1].Value.ToString();
//String ProductNameWait = (ProductId == "0000000000-00") ? row.Cells[2].Value.ToString() : "";
String CanReceive = Convert.ToDouble(row.Cells[3].Value.ToString()).ToString();
String QuantityPack = Convert.ToDouble(row.Cells[4].Value.ToString()).ToString();
String QuantitySale = Convert.ToDouble(row.Cells[6].Value.ToString()).ToString();
String QuantityWeight = Convert.ToDouble(row.Cells[8].Value.ToString()).ToString();
String PricePerUnit = Convert.ToDouble(row.Cells[10].Value.ToString()).ToString();
String PriceQuantity = Convert.ToDouble(row.Cells[11].Value.ToString()).ToString();
String DisText2 = row.Cells[12].Value.ToString();
String DisperUnit = Convert.ToDouble(row.Cells[13].Value.ToString()).ToString();
String Dis2 = Convert.ToDouble(row.Cells[14].Value.ToString()).ToString();
String TotalPrice2 = Convert.ToDouble(row.Cells[17].Value.ToString()).ToString();
String poid = row.Cells[18].Value.ToString();
String data = row.Cells[19].Value.ToString();
String type = row.Cells[20].Value.ToString();
String BranceId = row.Cells[21].Value.ToString();
String strTrans = row.Cells[23].Value.ToString();
sql = "INSERT INTO RECEIVE_DETAIL(ReceiveHeadId,ProductId,CanReceive,AmountProduct,ReceiveDetailQuantity,ReceiveDetailQuantityPack,ReceiveDetailPriceUnit," +
"PriceQuantity,DisText,DisPerUnit,DiscountBth,ReceiveDetailTotal,PurchaseOderId,PurchaseOrderDetailId,DetailType,BranchId,ShipPerUnit) " +
"VALUES (" + rid + ",'" + ProductId + "'," + CanReceive + "," + QuantityPack + "," + QuantitySale + "," + QuantityWeight + "," + PricePerUnit + "," +
"" + PriceQuantity + ",'" + DisText2 + "'," + DisperUnit + "," + Dis2 + "," + TotalPrice2 + ",'" + poid + "'," + PurchaseOrderDetailId + "," + type + "," + BranceId + "," + strTrans + ")";
ListSql.Add(sql);
if (type != "3")
{
String[] weight = data.Split('#');
for (int i = 0; i < weight.Length - 1; i++)//1 2 3,4 5 6,7 8 9
{
if (i % ((type == "1") ? 3 : 2) == 0)
{
String Pack = weight[i];
String Quantity = weight[i + 1];
String Weight = "0";
if (((type == "1") ? 3 : 2) == 3)
{
Weight = weight[i + 2];
}
sql = "INSERT INTO RECEIVE_WEIGHT(ReceiveHeadId,ProductId,ReceivePackId,ReceivePack,ReceiveWeight,PurchaseOrderDetailId) " +
"VALUES (" + rid + ",'" + ProductId + "'," + Pack + "," + Quantity + "," + Weight + "," + PurchaseOrderDetailId + ")";
ListSql.Add(sql);
}
}
}
}
if (dgvship.Rows.Count > 0)
{
foreach (DataGridViewRow row in dgvship.Rows)
{
String shipnum = Convert.ToDouble(row.Cells[0].Value.ToString()).ToString();
String shipproduct = row.Cells[1].Value.ToString();
String shipunit = Convert.ToDouble(row.Cells[3].Value.ToString()).ToString();
String shipsum = Convert.ToDouble(row.Cells[4].Value.ToString()).ToString();
String strunit = row.Cells[2].Value.ToString();
sql = "INSERT INTO RECEIVE_SHIP(Shipnum,ShipProduct,ShipUnit,ShipSum,ReceiveHeadId,unit,shipType)" +
"VALUES (" + shipnum + ",'" + shipproduct + "'," + shipunit + "," + shipsum + "," + rid + ",'" + strunit + "','" + shipType + "')";
ListSql.Add(sql);
}
}
if (MessageBox.Show("คุณต้องการบันทึกข้อมูลใบรับสินค้าใหม่เลขที่ " + rid + "\nใช่หรือไม่ ?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
if (DBConnString.clsDB.ExecuteTransaction(ListSql.ToArray()))
{
List<String> ListSqlQty = new List<String>();
/*String podetailid = "select pd.purchaseorderdetailid from purchaseorderdetail pd " +
"inner join purchaseorder p on pd.purchaseorderid = p.purchaseorderid " +
"INNER JOIN receive_detail d ON pd.PurchaseOrderDetailId = d.PurchaseOrderDetailId " +
"where d.ReceiveHeadId = "+rid;
DataTable dt = DBConnString.clsDB.QueryDataTable(podetailid);*/
DataTable oldPo = DBConnString.clsDB.QueryDataTable(oldSqlPo);
DataTable oldDt = Class.DBConnString.clsDB.QueryDataTable(oldSql);
foreach (DataRow row in oldDt.Rows)
{
String PurchaseOrderDetailId = row[0].ToString();
String sqlSelectQty = "SELECT isnull(sum(ReceiveDetailQuantity),0) as q FROM Receive_Detail Where PurchaseOrderDetailId = " + PurchaseOrderDetailId + "";
Double a = Convert.ToDouble(Class.DBConnString.clsDB.QueryExecuteScalar(sqlSelectQty));
sqlSelectQty = "SELECT isnull(sum(OutsideStockDetail_Quantity),0) as q FROM OUTSIDE_STOCK_DETAIL Where PurchaseOrderDetailId = " + PurchaseOrderDetailId + "";
a += Convert.ToDouble(Class.DBConnString.clsDB.QueryExecuteScalar(sqlSelectQty));
String sqlUpdateQty = "Update PurchaseOrderDetail Set QuantityRecieve = " + a + ",PurchaseOrderStatusid = " +
"CASE WHEN " + Convert.ToDouble(a) + " = 0 THEN 0 " +
"WHEN " + Convert.ToDouble(a) + " > 0 and " + Convert.ToDouble(a) + " < Quantity THEN 1 " +
"WHEN " + Convert.ToDouble(a) + " >= Quantity THEN 2 " +
"ELSE 0 END " +
"Where PurchaseOrderDetailId = '" + PurchaseOrderDetailId + "'";
ListSqlQty.Add(sqlUpdateQty);
}
if (DBConnString.clsDB.ExecuteTransaction(ListSqlQty.ToArray()))
{
List<String> ListSqlsts = new List<String>();
foreach (DataRow rowp in oldPo.Rows)
{
String Pidx = rowp[0].ToString();
String sts = "SELECT CASE WHEN sum(p.purchaseorderstatusid) = 0 THEN 0 " +
"WHEN COUNT(*)*2 = SUM(p.purchaseorderstatusid) THEN 2 " +
"ELSE 1 END " +
"FROM PurchaseOrderDetail p WHERE p.PurchaseOrderId = '" + Pidx + "'";
String up = "update purchaseorder set purchaseorderstatusid = " + Class.DBConnString.clsDB.QueryExecuteScalar(sts) + " where purchaseorderid = '" + Pidx + "'";
ListSqlsts.Add(up);
}
if (DBConnString.clsDB.ExecuteTransaction(ListSqlsts.ToArray()))
{
if (MessageBox.Show("บันทึกสำเร็จ คุณต้องการพิมพ์รายงาน " + rid + "\nใช่หรือไม่ ?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
receive.receiveReport rpt = new receive.receiveReport(DataPrice(), 0, 0);
rpt.ShowDialog();
}
retuenNulltext(false);
}
}
}
}
}
catch(Exception ex)
{ MessageBox.Show(ex.Message); }
}
private void รายงานใบรบแสดงราคาToolStripMenuItem_Click(object sender, EventArgs e)
{
if (dgvHead.Rows.Count <= 0) { MessageBox.Show("กรุณาเลือกใบรับ"); return; }
if (dgvHead.CurrentRow.Cells[6].Value.ToString() + "" == "")
{
if (dgvHead.CurrentRow.Cells[20].Value.ToString() + "" != "-")
{
receive.receiveReport rpt = new receive.receiveReport(DataDepPrice(), 1,0);
rpt.ShowDialog();
}
else
{
StockAsiaDrink.rptReceiveOutside frm = new StockAsiaDrink.rptReceiveOutside();
frm.setId = Convert.ToInt16(dgvHead.CurrentRow.Cells[0].Value.ToString());
frm.ShowDialog();
}
}
else
{
if (dgvHead.CurrentRow.Cells[20].Value.ToString() + "" != "-")
{
receive.receiveReport rpt = new receive.receiveReport(DataPrice(), 0,0);
rpt.ShowDialog();
}
else
{
receive.rptReceiveSelect rptReceiveSelect = new rptReceiveSelect();
rptReceiveSelect.StartPosition = FormStartPosition.CenterScreen; // กำหนด frm ย่อย ให้อยู่ตรงกลาง
rptReceiveSelect.setReceiveId = Convert.ToInt16(dgvHead.CurrentRow.Cells[0].Value.ToString());
rptReceiveSelect.setReceiveType = dgvHead.CurrentRow.Cells[11].Value.ToString();
rptReceiveSelect.ShowDialog();
}
}
}