public static bool check_received(int id)
{
CE sqlCe = new CE();
string query = "SELECT * FROM cerpdb.delivery_items WHERE did = '" + id + "' AND delivery_id > 0";
foreach (var l in sqlCe.Select(query, new int[] { 0 }))
{
if (l != null)
{
return true;
}
}
return false;
}
static bool Is_Exist(Int64 id)
{
CE sqlCe = new CE();
foreach (var l in sqlCe.Select(
"SELECT did FROM tbl_delivery_items_received WHERE did = " + id, // - select query
new int[] { 0 } // - index columns
))
{
if (l != null)
{
return true;
}
}
return false;
}
public static void save_to_localdb()
{
try
{
CE sqlCe = new CE();
dt = new DataTable("table");
dt = config.get(api.GetDeliveryItems(delivery_id));
DataRow[] result = dt.Select();
string query = string.Empty;
foreach (DataRow l in result)
{
if (!Is_Exist(Convert.ToInt64(l["id"])))
{
query = "INSERT INTO tbl_delivery_items_received (delivery_id, did, mid, code, po_qty, received_qty, status) " +
"VALUES " +
"('" + delivery_id + "', " +
"'" + l["id"].ToString() + "', " +
"'" + l["mid"].ToString() + "', " +
"'" + l["code"].ToString() + "', " +
"'" + l["po_qty"].ToString() + "', " +
"'" + l["received"].ToString() + "', " +
"'22');";
sqlCe.Execute(query);
}
}
query = "SELECT delivery_id, did, mid, code, po_qty, received_qty " +
"FROM tbl_delivery_items_received WHERE delivery_id = " + delivery_id;
dt = new DataTable("table");
dt = sqlCe.DataTable(query);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static bool update_qty(int id, Int64 received_qty, int status, string remarks)
{
CE sqlCe = new CE();
string query = "UPDATE tbl_delivery_items_received SET " +
"received_qty = '" + received_qty + "', " +
"status = '" + status + "', " +
"remarks = '" + remarks + "' " +
"WHERE did = " + id;
return sqlCe.Execute(query);
}
public static void process()
{
sqlCe = new CE();
api = new CERPService();
dt = new DataTable("table");
dt = sqlCe.dt(api.GetMaterialIssuance());
DataRow[] result1 = dt.Select();
foreach (DataRow m in result1)
{
int i = Convert .ToInt32(m["id"]);
if (!id(i))
{
// save material issuance into local database
SaveMaterialIssuance(i,
m["request_no"].ToString(),
m["batch_no"].ToString(),
m["requested_date"].ToString(),
m["expected_date"].ToString(),
m["remarks"].ToString(),
m["completion_status"].ToString());
System.Threading.Thread.Sleep(100);
// get material issuance items
DataRow[] result2 = sqlCe.dt(api.GetMaterialIssuanceItems(i)).Select();
foreach (DataRow l in result2)
{
// save material issuance items into local database
SaveMaterialIssuanceItems(i,
l["issue_id"].ToString(),
l["code"].ToString(),
l["lot_no"].ToString(),
l["qty"].ToString(),
l["status"].ToString(),
l["address"].ToString());
}
}
}
}
public static string get_items_received()
{
string result = string.Empty;
CE sqlCe = new CE();
dt = new DataTable("table");
dt = sqlCe.DataTable("SELECT " +
"did, " +
"mid, " +
"received_qty, " +
"status, " +
"remarks " +
"FROM tbl_delivery_items_received " +
"WHERE delivery_id = " + delivery_id);
if (dt != null)
{
StringWriter sw = new StringWriter();
dt.WriteXml(sw);
result = sw.ToString().Trim().Replace("\r\n", "");
}
return result;
}
public static DataTable getMaterialItemsByBarCode(string b_code)
{
sqlCe = new CE();
return sqlCe.DataTable("SELECT * FROM GetMaterialIssuanceItems WHERE id '" + miid + "' AND code = '" + b_code + "'");
}
public static DataTable getMaterialIssuanceItems(int id)
{
sqlCe = new CE();
api = new CERPService();
return sqlCe.dt(api.GetMaterialIssuanceItems(id));
}
internal static bool SaveMaterialIssuanceItems(int id, string issue_id, string code, string l_no, string qty, string status, string address)
{
bool returns = false;
sqlCe = new CE();
using (SqlCeConnection sqlCon = sqlCe.Open())
{
string query = "INSERT INTO GetMaterialIssuanceItems" +
"(id, issue_id, code, lot_no, qty, status, address) " +
"VALUES (@id, @issue_id, @code, @l_no, @qty, @status, @address);";
using (SqlCeCommand sqlCom = new SqlCeCommand(query, sqlCon))
{
sqlCon.Open();
sqlCom.Parameters.AddWithValue("@id", id);
sqlCom.Parameters.AddWithValue("@issue_id", issue_id);
sqlCom.Parameters.AddWithValue("@code", code);
sqlCom.Parameters.AddWithValue("@l_no", l_no);
sqlCom.Parameters.AddWithValue("@qty", qty);
sqlCom.Parameters.AddWithValue("@status", status);
sqlCom.Parameters.AddWithValue("@address", address);
sqlCom.ExecuteNonQuery();
sqlCon.Close();
returns = true;
}
}
return returns;
}
internal static bool SaveMaterialIssuance(int id, string r_no, string b_no, string r_date, string e_date, string remarks, string c_status)
{
bool returns = false;
sqlCe = new CE();
using (SqlCeConnection sqlCon = sqlCe.Open())
{
string query = "INSERT INTO GetMaterialIssuance" +
"(id, request_no, batch_no, requested_date, expected_date, remarks, completion_status) " +
"VALUES (@id, @request_no, @batch_no, @requested_date, @expected_date, @remarks, @completion_status);";
using (SqlCeCommand sqlCom = new SqlCeCommand(query, sqlCon))
{
sqlCon.Open();
sqlCom.Parameters.AddWithValue("@id", id);
sqlCom.Parameters.AddWithValue("@request_no", r_no);
sqlCom.Parameters.AddWithValue("@batch_no", b_no);
sqlCom.Parameters.AddWithValue("@requested_date", config.DefaultDateTimeFormat(Convert.ToDateTime(r_date)));
sqlCom.Parameters.AddWithValue("@expected_date", config.DefaultDateTimeFormat(Convert.ToDateTime(e_date)));
sqlCom.Parameters.AddWithValue("@remarks", remarks);
sqlCom.Parameters.AddWithValue("@completion_status", c_status);
sqlCom.ExecuteNonQuery();
sqlCon.Close();
returns = true;
}
}
return returns;
}
internal static bool id(int id)
{
sqlCe = new CE();
var result = sqlCe.Select("SELECT id FROM GetMaterialIssuance WHERE id = " + id, new int[] { 0 });
foreach (var i in result)
{
return true;
}
return false;
}