//public static Receive GetReceive(string receiveNo, string productCode)
//{
// try
// {
// using (var conn = new MySqlConnection(Globals.CONN_STR))
// {
// conn.Open();
// var sql = @"SELECT x.*,y.farm_name,z.breeder_name
// FROM
// (SELECT a.receive_no,
// a.receive_date,
// a.transport_doc_no,
// a.truck_no,
// a.farm_code,
// a.coop_no,
// a.breeder_code,
// a.queue_no,
// a.farm_qty,
// a.farm_wgh,
// IFNULL(SUM(receive_qty),0) as factory_qty,
// IFNULL(SUM(receive_wgh),0) as factory_wgh
// FROM receives a
// LEFT JOIN receive_item b
// ON a.receive_no=b.receive_no
// WHERE a.receive_no =@receive_no
// AND b.product_code =@product_code
// ) x,farm y,breeder z
// WHERE x.farm_code =y.farm_code
// AND x.breeder_code =z.breeder_code";
// var cmd = new MySqlCommand(sql, conn);
// cmd.Parameters.AddWithValue("receive_no", receiveNo);
// cmd.Parameters.AddWithValue("product_code", productCode);
// var da = new MySqlDataAdapter(cmd);
// var ds = new DataSet();
// da.Fill(ds);
// Receive receive = new Receive();
// for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
// {
// receive.ReceiveNo = (string)ds.Tables[0].Rows[i]["receive_no"];
// receive.ReceiveDate = (DateTime)ds.Tables[0].Rows[i]["receive_date"];
// receive.TransportDocNo = (string)ds.Tables[0].Rows[i]["transport_doc_no"];
// receive.TruckNo = (string)ds.Tables[0].Rows[i]["truck_no"];
// receive.Farm = new Farm
// {
// FarmCode = (string)ds.Tables[0].Rows[i]["farm_code"],
// FarmName = (string)ds.Tables[0].Rows[i]["farm_name"]
// };
// receive.CoopNo = ds.Tables[0].Rows[i]["coop_no"].ToString();
// receive.Breeder = new Breeder
// {
// BreederCode = ds.Tables[0].Rows[i]["breeder_code"].ToString().ToInt16(),
// BreederName = (string)ds.Tables[0].Rows[i]["breeder_name"]
// };
// receive.QueueNo = ds.Tables[0].Rows[i]["queue_no"].ToString().ToInt16();
// receive.FarmQty = ds.Tables[0].Rows[i]["farm_qty"].ToString().ToInt16();
// receive.FarmWgh = ds.Tables[0].Rows[i]["farm_wgh"].ToString().ToDecimal();
// receive.FactoryQty = ds.Tables[0].Rows[i]["factory_qty"].ToString().ToInt16();
// receive.FactoryWgh = ds.Tables[0].Rows[i]["factory_wgh"].ToString().ToDecimal();
// }
// return receive;
// }
// }
// catch (Exception)
// {
// throw;
// }
//}
public static bool InsertOrUpdate(Receive receive)
{
try
{
var plant = PlantController.GetPlant();
using (var conn = new MySqlConnection(Globals.CONN_STR))
{
conn.Open();
if (string.IsNullOrEmpty(receive.ReceiveNo))
{
//ADD NEW
var sql = @"SELECT MAX(queue_no)
FROM receives
WHERE receive_date=@receive_date
GROUP BY receive_no";
var cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("receive_date", receive.ReceiveDate.ToString("yyyy/MM/dd"));
var queue_no = cmd.ExecuteScalar();
if (queue_no == null)
{
receive.QueueNo = 1;
}
else
{
receive.QueueNo = queue_no.ToString().ToInt16() + 1;
}
receive.ReceiveNo = DocumentGenerate.GetDocumentRunningFormat("REV", receive.ReceiveDate);
receive.LotNo = DocumentGenerate.GetSwineLotNo(plant.PlantId, receive.QueueNo);
sql = @"INSERT INTO receives(
receive_no,
receive_date,
transport_doc_no,
truck_id,
farm_code,
coop_no,
breeder_code,
queue_no,
lot_no,
farm_qty,
farm_wgh,
receive_flag,
create_by)
VALUES(
@receive_no,
@receive_date,
@transport_doc_no,
@truck_id,
@farm_code,
@coop_no,
@breeder_code,
@queue_no,
@lot_no,
@farm_qty,
@farm_wgh,
@receive_flag,
@create_by)";
cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("receive_no", receive.ReceiveNo);
cmd.Parameters.AddWithValue("receive_date", receive.ReceiveDate);
cmd.Parameters.AddWithValue("transport_doc_no", receive.TransportDocNo);
cmd.Parameters.AddWithValue("truck_id", receive.Truck.TruckId);
cmd.Parameters.AddWithValue("farm_code", receive.Farm.FarmCode);
cmd.Parameters.AddWithValue("coop_no", receive.CoopNo);
cmd.Parameters.AddWithValue("breeder_code", receive.Breeder.BreederCode);
cmd.Parameters.AddWithValue("queue_no", receive.QueueNo);
cmd.Parameters.AddWithValue("lot_no", receive.LotNo);
cmd.Parameters.AddWithValue("farm_qty", receive.FarmQty);
cmd.Parameters.AddWithValue("farm_wgh", receive.FarmWgh);
cmd.Parameters.AddWithValue("receive_flag", receive.ReceiveFlag);
cmd.Parameters.AddWithValue("create_by", receive.CreateBy);
cmd.ExecuteNonQuery();
//sql = @"INSERT INTO receive_item_by_product(
// receive_no,
// bom_product_code,
// product_code,
// lot_no,
// target_qty,
// target_wgh,
// actual_qty,
// actual_wgh,
// create_by
// )VALUES(
// @receive_no,
// @bom_product_code,
// @product_code,
// @lot_no,
// @target_qty,
// @target_wgh,
// @actual_qty,
// @actual_wgh,
// @create_by
// )";
////GET BOM
////00101 เครื่องในแดง
//List<string> list = BomItemController.GetBomItemByProductCode("00101");
//list.Add("00101");
////INSERT By PRODUCT
//foreach (var item in list)
//{
// int target_qty = item == "00101" ? (receive.FarmQty * (list.Count - 1)) : receive.FarmQty;
// cmd = new MySqlCommand(sql, conn);
// cmd.Parameters.AddWithValue("receive_no", receive.ReceiveNo);
// cmd.Parameters.AddWithValue("bom_product_code", "00101");
// cmd.Parameters.AddWithValue("product_code", item);
// cmd.Parameters.AddWithValue("lot_no", receive.LotNo);
// cmd.Parameters.AddWithValue("target_qty", target_qty == 0 ? receive.FarmQty : target_qty);
// cmd.Parameters.AddWithValue("target_wgh", receive.FarmWgh);
// cmd.Parameters.AddWithValue("actual_qty", 0);
// cmd.Parameters.AddWithValue("actual_wgh", 0);
// cmd.Parameters.AddWithValue("create_by", receive.CreateBy);
// cmd.ExecuteNonQuery();
//}
//list.Clear();
////00201 เครื่องในขาว
//list = BomItemController.GetBomItemByProductCode("00201");
//list.Add("00201");
////INSERT By PRODUCT
//foreach (var item in list)
//{
// int target_qty = item == "00201" ? (receive.FarmQty * (list.Count - 1)) : receive.FarmQty;
// cmd = new MySqlCommand(sql, conn);
// cmd.Parameters.AddWithValue("receive_no", receive.ReceiveNo);
// cmd.Parameters.AddWithValue("bom_product_code", "00201");
// cmd.Parameters.AddWithValue("product_code", item);
// cmd.Parameters.AddWithValue("lot_no", receive.LotNo);
// cmd.Parameters.AddWithValue("target_qty", target_qty == 0 ? receive.FarmQty : target_qty);
// cmd.Parameters.AddWithValue("target_wgh", receive.FarmWgh);
// cmd.Parameters.AddWithValue("actual_qty", 0);
// cmd.Parameters.AddWithValue("actual_wgh", 0);
// cmd.Parameters.AddWithValue("create_by", receive.CreateBy);
// cmd.ExecuteNonQuery();
//}
}
else
{
//UPDATE
var sql = @"UPDATE receives SET
transport_doc_no=@transport_doc_no,
truck_id=@truck_id,
farm_code=@farm_code,
coop_no=@coop_no,
breeder_code=@breeder_code,
farm_qty=@farm_qty,
farm_wgh=@farm_wgh,
modified_at=CURRENT_TIMESTAMP,
modified_by=@modified_by
WHERE receive_no=@receive_no";
var cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("receive_no", receive.ReceiveNo);
cmd.Parameters.AddWithValue("transport_doc_no", receive.TransportDocNo);
cmd.Parameters.AddWithValue("truck_id", receive.Truck.TruckId);
cmd.Parameters.AddWithValue("farm_code", receive.Farm.FarmCode);
cmd.Parameters.AddWithValue("coop_no", receive.CoopNo);
cmd.Parameters.AddWithValue("breeder_code", receive.Breeder.BreederCode);
cmd.Parameters.AddWithValue("farm_qty", receive.FarmQty);
cmd.Parameters.AddWithValue("farm_wgh", receive.FarmWgh);
cmd.Parameters.AddWithValue("modified_by", receive.ModifiedBy);
cmd.ExecuteNonQuery();
}
}
return(true);
}
catch (Exception)
{
throw;
}
}
public static bool Insert(Order order)
{
MySqlTransaction tr = null;
try
{
order.OrderNo = DocumentGenerate.GetDocumentRunningFormat("SO", order.RequestDate);
using (var conn = new MySqlConnection(Globals.CONN_STR))
{
conn.Open();
tr = conn.BeginTransaction();
var sql = @"INSERT INTO orders
(order_no,
order_date,
customer_code,
order_flag,
comments,
active,
create_by)
VALUES(@order_no,
@order_date,
@customer_code,
@order_flag,
@comments,
@active,
@create_by)";
var cmd = new MySqlCommand()
{
CommandText = sql,
Connection = conn,
Transaction = tr
};
cmd.Parameters.AddWithValue("order_no", order.OrderNo);
cmd.Parameters.AddWithValue("order_date", order.RequestDate.ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("customer_code", order.Customer.CustomerCode);
cmd.Parameters.AddWithValue("order_flag", order.OrderFlag);
cmd.Parameters.AddWithValue("comments", order.Comments);
cmd.Parameters.AddWithValue("active", order.Active);
cmd.Parameters.AddWithValue("create_by", order.CreateBy);
cmd.ExecuteNonQuery();
sql = @"INSERT INTO orders_item
(order_no,
product_code,
seq,
order_qty,
order_wgh,
bom_code,
order_set_qty,
order_set_wgh,
create_by)
VALUES(
@order_no,
@product_code,
@seq,
@order_qty,
@order_wgh,
@bom_code,
@order_set_qty,
@order_set_wgh,
@create_by)";
foreach (var item in order.OrderItems)
{
cmd = new MySqlCommand()
{
CommandText = sql,
Connection = conn,
Transaction = tr
};
cmd.Parameters.AddWithValue("order_no", order.OrderNo);
cmd.Parameters.AddWithValue("product_code", item.Product.ProductCode);
cmd.Parameters.AddWithValue("seq", item.Seq);
cmd.Parameters.AddWithValue("order_qty", item.OrderQty);
cmd.Parameters.AddWithValue("order_wgh", item.OrderWgh);
cmd.Parameters.AddWithValue("bom_code", item.BomCode);
cmd.Parameters.AddWithValue("order_set_qty", item.OrderSetQty);
cmd.Parameters.AddWithValue("order_set_wgh", item.OrderSetWgh);
cmd.Parameters.AddWithValue("active", order.Active);
cmd.Parameters.AddWithValue("create_by", order.CreateBy);
cmd.ExecuteNonQuery();
}
tr.Commit();
}
return(true);
}
catch (Exception)
{
tr.Rollback();
throw;
}
}
public static bool Insert(Invoice Invoice)
{
MySqlTransaction tr = null;
try
{
using (var conn = new MySqlConnection(Globals.CONN_STR))
{
Invoice.InvoiceNo = DocumentGenerate.GetDocumentRunningFormat("IV", Invoice.InvoiceDate);
Invoice.ReceiptNo = DocumentGenerate.GetDocumentRunningFormat("RC", Invoice.InvoiceDate);
conn.Open();
tr = conn.BeginTransaction();
var sql = @"INSERT
INTO invoice(
invoice_no, invoice_date, ref_document_no, receipt_no,
customer_code, truck_id, gross_amt, disc_amt, disc_amt_bill,
vat_rate, vat_amt, net_amt,
invoice_flag, comments, active,
create_by
)
VALUES( @invoice_no, @invoice_date, @ref_document_no, @receipt_no,
@customer_code, @truck_id, @gross_amt, @disc_amt, @disc_amt_bill,
@vat_rate, @vat_amt, @net_amt,
@invoice_flag, @comments, @active,
@create_by)" ;
var cmd = new MySqlCommand(sql, conn)
{
Transaction = tr
};
cmd.Parameters.AddWithValue("invoice_no", Invoice.InvoiceNo);
cmd.Parameters.AddWithValue("invoice_date", Invoice.InvoiceDate.ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("ref_document_no", Invoice.RefDocumentNo);
cmd.Parameters.AddWithValue("receipt_no", Invoice.ReceiptNo);
cmd.Parameters.AddWithValue("customer_code", Invoice.Customer.CustomerCode);
cmd.Parameters.AddWithValue("truck_id", Invoice.Truck.TruckId);
cmd.Parameters.AddWithValue("gross_amt", Invoice.GrossAmt);
cmd.Parameters.AddWithValue("disc_amt", Invoice.DiscAmt);
cmd.Parameters.AddWithValue("disc_amt_bill", Invoice.DiscAmtBill);
cmd.Parameters.AddWithValue("vat_rate", Invoice.VatRate);
cmd.Parameters.AddWithValue("vat_amt", Invoice.VatAmt);
cmd.Parameters.AddWithValue("net_amt", Invoice.NetAmt);
cmd.Parameters.AddWithValue("invoice_flag", Invoice.InvoiceFlag);
cmd.Parameters.AddWithValue("comments", Invoice.Comments);
//cmd.Parameters.AddWithValue("print_no", Invoice.PrintNo);
cmd.Parameters.AddWithValue("active", Invoice.Active);
cmd.Parameters.AddWithValue("create_by", Invoice.CreateBy);
cmd.ExecuteNonQuery();
sql = @"INSERT INTO invoice_item(
invoice_no, product_code, seq,
qty, wgh, unit_price_current, disc_per, unit_price,
gross_amt, net_amt, sale_unit_method, create_by
)
VALUES (
@invoice_no, @product_code, @seq,
@qty, @wgh, @unit_price_current, @disc_per, @unit_price,
@gross_amt, @net_amt, @sale_unit_method, @create_by )" ;
foreach (var item in Invoice.InvoiceItems)
{
cmd = new MySqlCommand(sql, conn)
{
Transaction = tr
};
cmd.Parameters.AddWithValue("invoice_no", Invoice.InvoiceNo);
cmd.Parameters.AddWithValue("product_code", item.Product.ProductCode);
cmd.Parameters.AddWithValue("seq", item.Seq);
cmd.Parameters.AddWithValue("qty", item.Qty);
cmd.Parameters.AddWithValue("wgh", item.Wgh);
cmd.Parameters.AddWithValue("unit_price_current", item.UnitPriceCurrent);
cmd.Parameters.AddWithValue("disc_per", item.DiscPer);
cmd.Parameters.AddWithValue("unit_price", item.UnitPrice);
cmd.Parameters.AddWithValue("gross_amt", item.GrossAmt);
cmd.Parameters.AddWithValue("net_amt", item.NetAmt);
cmd.Parameters.AddWithValue("sale_unit_method", item.SaleUnitMethod);
cmd.Parameters.AddWithValue("create_by", Invoice.CreateBy);
cmd.ExecuteNonQuery();
}
Int64 invoiceFlagOfSo = (Invoice.Active == true) ? 1 : 0;
sql = @"UPDATE orders
SET invoice_flag=@invoice_flag
WHERE order_no=@order_no" ;
cmd = new MySqlCommand(sql, conn)
{
Transaction = tr
};
cmd.Parameters.AddWithValue("invoice_flag", invoiceFlagOfSo);
cmd.Parameters.AddWithValue("order_no", Invoice.RefDocumentNo);
cmd.ExecuteNonQuery();
tr.Commit();
}
return(true);
}
catch (Exception)
{
tr.Rollback();
throw;
}
}