public ExecuteModels DelWorksheet(WorksheetModels val) { ExecuteModels ecm = new ExecuteModels(); HomeController hc = new HomeController(); using (SqlConnection con = hc.ConnectDatabase()) { string _SQL = "delete from transport where tran_id = " + val.tran_id; using (SqlCommand cmd = new SqlCommand(_SQL, con)) { try { if (Int32.Parse(cmd.ExecuteNonQuery().ToString()) == 1) { ecm.result = 0; ecm.code = "OK"; } else { ecm.result = 1; ecm.code = _SQL; } } catch (Exception ex) { ecm.result = 1; ecm.code = ex.Message; } } con.Close(); } return(ecm); }
public ExecuteModels UpdateWorksheet(WorksheetModels val) { ExecuteModels ecm = new ExecuteModels(); string _SQL_Set = string.Empty; string[] Col_Arr = { "tran_code", "number_po", "cus_id", "branch_id", "contact_id", "product_id", "trunk_id", "driver_id_1", "driver_id_2", "driver_id_3", "license_id_head", "license_id_tail", "remark", "tran_status_id", "update_by_user_id", "sheet_name", "cont1", "cont2", "driver_id_4", "bugget_tran", "date_work", "date_start", "date_end", "value_order", "type_tran", "size_cont1", "size_cont2", "condition_tran", "special_order", "tank_number", "style_tank", "weight_tank" }; string[] Val_Arr = { val.tran_code, val.number_po, val.cus_id, val.branch_id, val.contact_id, val.product_id, val.trunk_id, val.driver_id_1, val.driver_id_2, val.driver_id_3, val.license_id_head, val.license_id_tail, val.remark, val.tran_status_id, val.update_by_user_id, val.sheet_name, val.cont1, val.cont2, val.driver_id_4, val.bugget_tran, val.date_work, val.date_start, val.date_end, val.value_order, val.type_tran, val.size_cont1, val.size_cont2, val.condition_tran, val.special_order, val.tank_number, val.style_tank, val.weight_tank }; for (int n = 0; n <= Val_Arr.Length - 1; n++) { if (Val_Arr[n] != null) { _SQL_Set += Col_Arr[n] + " = N'" + Val_Arr[n] + "', "; } } HomeController hc = new HomeController(); using (SqlConnection con = hc.ConnectDatabase()) { string _SQL = "UPDATE transport set " + _SQL_Set + " update_by_user_id = 1 where tran_id = " + val.tran_id; using (SqlCommand cmd = new SqlCommand(_SQL, con)) { try { if (Int32.Parse(cmd.ExecuteNonQuery().ToString()) == 1) { ecm.result = 0; ecm.code = "OK"; } else { ecm.result = 1; ecm.code = _SQL; } } catch (Exception ex) { ecm.result = 1; ecm.code = ex.Message; } } con.Close(); } return(ecm); }
public ExecuteModels InsertWorksheet(WorksheetModels val) { ExecuteModels ecm = new ExecuteModels(); HomeController hc = new HomeController(); using (SqlConnection con = hc.ConnectDatabase()) { string _no = ""; string SQL_COUNT = "SELECT (COUNT(*) + 1 ) AS NO FROM [TT1995_CheckList].[dbo].[transport] WHERE create_date BETWEEN '" + DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00' AND '" + DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59' "; using (SqlCommand cmd = new SqlCommand(SQL_COUNT, con)) { DataTable _Dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(_Dt); da.Dispose(); foreach (DataRow _Item in _Dt.Rows) { _no = _Item["NO"].ToString(); } } _no = int.Parse(_no).ToString("000"); var tran_code = "CHECK" + DateTime.Now.ToString("yyMMdd") + _no; //Date String date1 = val.date_work; date1 = Regex.Replace(date1, " \\(.*\\)$", ""); DateTime date_work = DateTime.ParseExact(date1, "ddd MMM dd yyyy HH:mm:ss 'GMT'zzz", System.Globalization.CultureInfo.InvariantCulture); String date2 = val.date_start; date2 = Regex.Replace(date2, " \\(.*\\)$", ""); DateTime date_start = DateTime.ParseExact(date2, "ddd MMM dd yyyy HH:mm:ss 'GMT'zzz", System.Globalization.CultureInfo.InvariantCulture); String date3 = val.date_end; date3 = Regex.Replace(date3, " \\(.*\\)$", ""); DateTime date_end = DateTime.ParseExact(date3, "ddd MMM dd yyyy HH:mm:ss 'GMT'zzz", System.Globalization.CultureInfo.InvariantCulture); string _SQL = "INSERT INTO [dbo].[transport] (tran_code,number_po ,cus_id ,branch_id ,contact_id ,product_id ,trunk_id " + ",driver_id_1 ,driver_id_2 ,driver_id_3 ,license_id_head ,license_id_tail ,remark ,tran_status_id ,create_by_user_id ,sheet_name ,cont1,cont2 ,driver_id_4 " + " , bugget_tran , date_work , date_start , date_end , value_order , type_tran , size_cont1 , size_cont2 , condition_tran , special_order , tank_number , style_tank , weight_tank) " + " output inserted.tran_id VALUES (N'" + tran_code + "', N'" + val.number_po + "', '" + val.cus_id + "', '" + val.branch_id + "', '" + val.contact_id + "', '" + val.product_id + "'" + ", '" + val.trunk_id + "' ,'" + val.driver_id_1 + "' , '" + val.driver_id_2 + "', '" + val.driver_id_3 + "', '" + val.license_id_head + "' , '" + val.license_id_tail + "'" + ", N'" + val.remark + "' , 1 , " + val.create_by_user_id + " , '" + val.sheet_name + "' , '" + val.cont1 + "', '" + val.cont2 + "', '" + val.driver_id_4 + "'" + ",'" + val.bugget_tran + "', '" + date_work + "', '" + date_start + "', '" + date_end + "', '" + val.value_order + "', '" + val.type_tran + "', '" + val.size_cont1 + "', '" + val.size_cont2 + "', '" + val.condition_tran + "', '" + val.special_order + "', '" + val.tank_number + "', '" + val.style_tank + "', '" + val.weight_tank + "')"; using (SqlCommand cmd = new SqlCommand(_SQL, con)) { try { var id_return = Int32.Parse(cmd.ExecuteScalar().ToString()); if (id_return >= 1) { ecm.result = 0; ecm.code = "OK"; ecm.id_return = id_return.ToString(); } } catch (Exception ex) { ecm.result = 1; ecm.code = ex.Message; } } con.Close(); } return(ecm); }