private void insertAndUpdate()
{
String sql = "";
List<String> ListSql = new List<String>();
String rid = txtRec.Text;
List<String> ListSqlQtyX = new List<String>();
String arraydetailid = "";
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";
}
if (DBConnString.clsDB.ExecuteTransaction(ListSqlQtyX.ToArray()))
{
List<String> ListSqlstsX = new List<String>();
DBConnString.clsDB.ExecuteTransaction(ListSqlstsX.ToArray());
}
if (txtRec.Enabled == true)
{
sql = "select COHeadId from COSIGNMENT_STOCK_HEAD where COHeadId = '" + rid + "'";
if (DBConnString.clsDB.QueryExecuteScalar(sql) != null)
{
sql = "DELETE FROM COSIGNMENT_STOCK_WEIGHT WHERE COHeadId = '" + rid + "' ";
ListSql.Add(sql);
sql = "DELETE FROM COSIGNMENT_STOCK_DETAIL WHERE COHeadId = '" + rid + "' ";
ListSql.Add(sql);
sql = "DELETE FROM COSIGNMENT_STOCK_HEAD WHERE COHeadId = '" + rid + "' ";
ListSql.Add(sql);
}
}
String CQNew = "";
String CQ;
for (int nbRow = 0; nbRow < dgvReceive.Rows.Count; nbRow++)
{
CQ = dgvReceive.Rows[nbRow].Cells[15].Value.ToString();
if (CQNew != CQ)
{
CQNew = CQ;
String sqldeleteR = "update CO_REQ_HEAD set COStatusId='1' where CQHeadId='" + CQNew + "'";
SqlDataReader readerUpdateHead1 = DBConnString.clsDB.QueryDataReader(sqldeleteR);
}
}
String supid = Class.Function.GetTextId(txtSupplier.Text);
String shipid = Class.Function.GetTextId(txtShipper.Text);
String rdate = Class.Function.GetDate(dptRecDate.Value);
String ct = cboReceiveType.SelectedValue.ToString();
String UserName = clsCash.sUserIdLogin;
String PriceTotal = Convert.ToDouble(TxtPriceTotal.Text.Trim()) + "";
String DisText = TxtDiscountText.Text.Trim().ToString();
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 Description = TxtDescription.Text.Trim();
if (CmboxVat.SelectedIndex == 0) { VatType = "1"; }
else if (CmboxVat.SelectedIndex == 1) { VatType = "7"; }
else if (CmboxVat.SelectedIndex == 2) { VatType = "8"; }
sql = "INSERT INTO COSIGNMENT_STOCK_HEAD(COHeadId,COHeadDate,COHeadDateTime,ShipperId,UserName,COTypeId,COStatusId," +
"ConfirmId,PriceTotal,DisText,Dis,VatBase,VatType,VatPer,Vat,TotalPrice," +
"[Description],SupplierId,Shipping,Shipper)" +
"VALUES('" + rid + "','" + rdate + "',GETDATE(),'" + shipid + "','" + UserName + "'," + ct + ",'1'," +
"'1'," + PriceTotal + ",'" + DisText + "'," + Dis + "," + VatBase + ",'" + VatType + "'," + VatPer + "," + Vat + "," + TotalPrice + "," +
"'" + Description + "','" + supid + "','" + textBox1.Text + "','" + txtShipp.Text + "')";
ListSql.Add(sql);
foreach (DataGridViewRow row in dgvReceive.Rows)
{
try
{
String ProductId = row.Cells[0].Value.ToString();
String QuantityPack = Convert.ToDouble(row.Cells[2].Value.ToString()).ToString();
String QuantitySale = Convert.ToDouble(row.Cells[4].Value.ToString()).ToString();
String QuantityWeight = Convert.ToDouble(row.Cells[6].Value.ToString()).ToString();
//String QuantityWeight = row.Cells[6].Value.ToString().ToString();
String PricePerUnit = Convert.ToDouble(row.Cells[8].Value.ToString()).ToString();
String strDisText = row.Cells[9].Value.ToString();
String strDisPerUnit = Convert.ToDouble(row.Cells[10].Value.ToString()).ToString();
String strDiscountBth = Convert.ToDouble(row.Cells[11].Value.ToString()).ToString();
String data = row.Cells[12].Value.ToString();
String type = row.Cells[13].Value.ToString();
String PriceQuantity = Convert.ToDouble(row.Cells[14].Value.ToString()).ToString();
String CQHeadId = row.Cells[15].Value.ToString();
sql = "INSERT INTO COSIGNMENT_STOCK_DETAIL(COHeadId,ProductId,CODetailQuantityPack,CODetailQuantity,CODetailWeight,CODetailPriceUnit," +
"PriceQuantity,DisText,DisPerUnit,DiscountBth,DetailType,CQHeadId) " +
"VALUES ('" + rid + "','" + ProductId + "'," + QuantityPack + "," + QuantitySale + "," + QuantityWeight + "," + PricePerUnit + "," +
"" + PriceQuantity + ",'" + strDisText + "','" + strDisPerUnit + "','" + strDiscountBth + "'," + type + ",'" + CQHeadId + "')";
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 COSIGNMENT_STOCK_WEIGHT(COHeadId,ProductId,COPackId,COPack,COWeight) " +
"VALUES ('" + rid + "','" + ProductId + "'," + Pack + "," + Quantity + "," + Weight + ")";
ListSql.Add(sql);
}
}
}
}
catch { MessageBox.Show("error กรุณาป้อนข้อมูลให้ตรงรูปแบบ"); return; }
}
if (MessageBox.Show("คุณต้องการบันทึกข้อมูลใบฝากสินค้าใหม่เลขที่ " + rid + "\nใช่หรือไม่ ?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
if (DBConnString.clsDB.ExecuteTransaction(ListSql.ToArray()))
{
List<String> ListSqlQty = new List<String>();
//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));
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(Data(), 0);
// rpt.ShowDialog();
//}
//retuenNulltext(false);
//this.DialogResult = DialogResult.OK;
}
}
}
if (MessageBox.Show("บันทึกสำเร็จ คุณต้องการพิมพ์รายงาน " + rid + "\nใช่หรือไม่ ?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
StockAsiaDrink.COReport rpt = new StockAsiaDrink.COReport(DataPrice(), 1, 0);
rpt.ShowDialog();
}
//this.DialogResult = DialogResult.OK;
if (CheckFrom == 0)
{
frmSentConsignmentNew frm = new frmSentConsignmentNew();
frm.MdiParent = this.MdiParent;
this.Close();
frm.Show();
}
else if (CheckFrom == 1)
{
CQNew = "";
for (int nbRow = 0; nbRow < dgvReceive.Rows.Count; nbRow++)
{
CQ = dgvReceive.Rows[nbRow].Cells[15].Value.ToString();
if (CQNew != CQ)
{
CQNew = CQ;
sql = "select count(CQHeadId) from CO_REQ_HEAD where CQHeadId='" + CQNew + "' and COStatusId='1'";
DataTable dtNew = Class.DBConnString.clsDB.QueryDataTable(sql);
Int32 c = Convert.ToInt32(dtNew.Rows[0][0].ToString());
if (c > 0)
{
sql = "select count(CODetailId) from COSIGNMENT_STOCK_DETAIL where CQHeadId='" + CQNew + "'";
dtNew = Class.DBConnString.clsDB.QueryDataTable(sql);
c = Convert.ToInt32(dtNew.Rows[0][0].ToString());
if (c == 0)
{
String sqldeleteR = "update CO_REQ_HEAD set COStatusId='0' where CQHeadId='" + CQNew + "'";
SqlDataReader readerUpdateHead1 = DBConnString.clsDB.QueryDataReader(sqldeleteR);
}
}
}
}
this.Close();
}
}
}
private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
if (CheckSearch == 1)
{
frmSentConsignmentNew frm = new frmSentConsignmentNew(dgvHead.Rows[e.RowIndex].Cells[0].Value.ToString(), Sta.ToString());
frm.ShowDialog();
MainStock();
}
else if (CheckSearch == 2)
{
StockAsiaDrink.frmSaleConsignmentNew frm = new StockAsiaDrink.frmSaleConsignmentNew(dgvHead.Rows[e.RowIndex].Cells[0].Value.ToString(), Sta.ToString());
frm.ShowDialog();
MainSale();
}
else if (CheckSearch == 3)
{
}
}