private void Search()
{
try
{
string armid = DataControls.GetSelectedValueComboBoxToString(cbbProvince);
DataTable dt = new DataTable();
pSD18 = new List <PersonSD18>();
string sql = query.reportPrintNewPerson(armid);
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, dcore.getConnection());
dcore.getConnection().Open();
using (MySql.Data.MySqlClient.MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
pSD18.Add(new PersonSD18(reader));
}
}
dt = Function.ConvertToDataTable <PersonSD18>(pSD18);
DisplayReport(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dcore.getConnection().Close();
}
}
private void Btn_saveIndictment_Click(object sender, EventArgs e)
{
if (FlagFunction == "save")
{
if (MessageBox.Show("ต้องการเพิ่มข้อมูลนี้ ใช่หรือไม่", "เพิ่มข้อมูล", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
string sqlInsert = "";
try
{
sqlInsert += "INSERT INTO indictmenttab (NavyID,Cause,Date_captured)\n";
sqlInsert += "VALUES ('" + navyid + "', '" + txtBox_Indictment.Text + "', '" + ConvertDate(txtBox_capture.Text.Trim()) + "')\n";
}
catch (Exception ex) { MessageBox.Show("กรุณากรอกข้อมูลให้ถูกต้อง"); }
string result1 = "";
DBConnection.runCmdTransaction(sqlInsert, dcore.getConnection(), out result1);
if (result1 != "OK")
{
MessageBox.Show("มีปัญหาในการเพิ่มข้อมูล กรุณาติดต่อ ผู้ดูแลระบบโดยด่วน!! " + result1);
return;
}
MessageBox.Show("เพิ่มข้อมูลเรียบร้อย");
}
}
else if (FlagFunction == "edit")
{
if (MessageBox.Show("ต้องการแก้ไขข้อมูลนี้ ใช่หรือไม่", "แก้ไขข้อมูล", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
string sqlUpdate = "";
try
{
sqlUpdate += "update indictmenttab SET Cause = '" + txtBox_Indictment.Text.Trim() + "', Date_captured = '" + ConvertDate(txtBox_capture.Text.Trim()) + "'\n";
sqlUpdate += "WHERE Cause = '" + IndictmentTxt + "' AND NavyID = '" + navyid + "'\n";
}
catch (Exception ex) { MessageBox.Show("กรุณากรอกข้อมูลให้ถูกต้อง"); }
string result1 = "";
DBConnection.runCmdTransaction(sqlUpdate, dcore.getConnection(), out result1);
if (result1 != "OK")
{
MessageBox.Show("มีปัญหาในการแก้ไขข้อมูล กรุณาติดต่อ ผู้ดูแลระบบโดยด่วน!! " + result1);
return;
}
MessageBox.Show("อัพเดตข้อมูลเรียบร้อย");
}
}
this.Close();
}
public void ShowReportPrintHistoryBook(System.Data.DataTable dataSource, string yearin)
{
try
{
Reports.ReportDisplay f = new Reports.ReportDisplay();
if (dataSource.Rows.Count > 0)
{
System.Data.DataColumn dc = new System.Data.DataColumn("imagepath", typeof(string));
dataSource.Columns.Add(dc);
string imageserver = dcore.getConnection().DataSource;
yearin = yearin.Replace("/", ".");
foreach (System.Data.DataRow dr in dataSource.Rows)
{
string imagepath = @"\\" + imageserver + @"\navyimages\" + yearin + @"\" + dr["NAVYID"].ToString() + @".jpg";
dr["imagepath"] = imagepath;
}
f.ReportViewer.SetDisplayMode(Microsoft.Reporting.WinForms.DisplayMode.PrintLayout);
f.DisplayReport(dataSource, "Navy.Reports.ReportHistoryBook.rdlc");
f.ReportViewer.LocalReport.EnableExternalImages = true;
Refresh(f.ReportViewer);
f.Show();
}
else
{
}
}
catch
{
throw;
}
}
private void Remove_Indictment_Click(object sender, EventArgs e)
{
if (gv_Indictment.SelectedRows.Count > 0)
{
if (MessageBox.Show("ต้องการลบข้อมูลนี้ ใช่หรือไม่", "ลบข้อมูล", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
string IndictmentCause = gv_Indictment.SelectedRows[0].Cells["indictment"].Value.ToString();
string sqlDelete = "";
sqlDelete += "DELETE FROM indictmenttab WHERE Cause = '" + IndictmentCause + "';\n";
string result1 = "";
DBConnection.runCmdTransaction(sqlDelete, dcore.getConnection(), out result1);
if (result1 != "OK")
{
MessageBox.Show("มีปัญหาในการลบข้อมูล กรุณาติดต่อ ผู้ดูแลระบบโดยด่วน!! " + result1);
return;
}
MessageBox.Show("ลบข้อมูลเรียบร้อย");
//Refresh Form
GetandSetInitialForm(navyid);
}
}
}
private void btnSubmit_Click(object sender, EventArgs e)
{
if (gvResult.SelectedRows.Count > 0)
{
if (MessageBox.Show("ต้องการคัดลอกข้อมูลนี้ไปยังฐานข้อมูลทหารใหม่ ใช่หรือไม่", "คัดลอกข้อมูล", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
int rowsAffect = 0;
string navyid = gvResult.SelectedRows[0].Cells["navyid"].Value.ToString();
try
{
if (rbAddNew.Checked)
{
//no action
MessageBox.Show("ยังไม่มีฟังก์ชั่นการทำงานรองรับในส่วนนี้");
}
else if (rbEditOrDelete.Checked)
{
//get data from [person] [trans_detail], [punishment_detail], [dataoldyearin]
using (MySql.Data.MySqlClient.MySqlConnection con = dcoreNavAll.getConnection())
{
dcoreNavAll.openConnection();
dtPerson = new DataTable();
dtTransDetail = new DataTable();
dtPunishmentDetail = new DataTable();
dtDataOldYearin = new DataTable();
dtIndicment = new DataTable();
dtPerson.Load((new MySql.Data.MySqlClient.MySqlCommand("SELECT * FROM person WHERE NAVYID = '" + navyid + "';", con)).ExecuteReader());
dtTransDetail.Load((new MySql.Data.MySqlClient.MySqlCommand("SELECT * FROM trans_detail WHERE NAVYID = '" + navyid + "';", con)).ExecuteReader());
dtPunishmentDetail.Load((new MySql.Data.MySqlClient.MySqlCommand("SELECT * FROM punishment_details WHERE NAVYID = '" + navyid + "';", con)).ExecuteReader());
dtDataOldYearin.Load((new MySql.Data.MySqlClient.MySqlCommand("SELECT * FROM dataoldyearin WHERE NAVYID = '" + navyid + "';", con)).ExecuteReader());
dtIndicment.Load((new MySql.Data.MySqlClient.MySqlCommand("SELECT * FROM indictmenttab WHERE NavyID = '" + navyid + "';", con)).ExecuteReader());
dcoreNavAll.closeConnection();
}
//insert data to NavDB
using (MySql.Data.MySqlClient.MySqlConnection con = dcoreNav.getConnection())
{
dcoreNav.openConnection();
string sqlPerson = "";
foreach (DataRow dr in dtPerson.Rows)
{
//sqlPerson += "INSERT INTO `person` (`STATUSCODE`, `educ`, `BATT`, `COMPANY`, `PLATOON`, `PSEQ`, `YEARIN`, `NAME`, `SNAME`, `ID8`, `PERCENT`, `UNIT1`, `UNIT2`, `POSTCODE`, `UNIT4`, `ITEM`, `UNIT3`, `ASK`, `NUMBER`, `UNIT0`, `REM`, `REM2`, `BIRTHDATE`, `ID13`, `EDUCODE0`, `EDUCODE1`, `EDUCODE2`, `ID`, `ISSCHOOL`, `NAVYID`, `ARMID`, `REGCODE`, `OCCCODE`, `RECIVEEDU1`, `RECIVEEDU2`, `SCHOOLCODE`, `SKILLCODE`, `TOWNCODE`, `SWIMCODE`, `SALCODE`, `ADDRESS`, `ADDRESS_MU`, `ADDRESS_SOIL`, `ADDRESS_ROAD`, `FATHER`, `FSNAME`, `MOTHER`, `MSNAME`, `HEIGHT`, `WIDTH`, `PERTYPE`, `RUNCODE`, `PERCENT1`, `PERSTAT`, `REPDATE`, `ARMSTUDY`, `REGDATE`, `RETIREDATE`, `MARK`, `BLOOD`, `REASON`, `IS_REQUEST`, `SORDER`, `NLABEL`, `NUMBOX`, `RECORDDATE`, `RECORDBY`, `IS_IMPORT`, `PIC`, `OLDYEARIN`, `FLAG`, `particular`, `origincode`, `FlagMove`, `AddressMove`, `Copy_of_MOVEDATE`, `MOVEDATE`, `serve`, `serve_doc`, `status_request`, `patient_status`, `kpt`, `shift_release`) " +
// "VALUES ('AA', NULL, '4', '6', '3', 30, '1/53', 'อำพล', 'จอมแพ่ง', 'พ.ท.0065', 60.010, '1', '10', '1', '0', 0, '1', NULL, NULL, NULL, NULL, NULL, '1989/02/21', 1930100106277, NULL, '3', '006', '2533600065', '', 55308545, '36', '1', 'B', NULL, NULL, '36010093100220', 'B', '360110', '0', NULL, '38/1', '5', '', '', 'อวบ', 'จอมแพ่ง', 'อัมพร', 'จอมแพ่ง', 171, '87/90', '1', 2160, NULL, NULL, '2010-5-2', '0', '2010-5-1', '0000-0-0', 'ข้อมือขวา', 'O', NULL, '100', NULL, 39, NULL, '2010-5-4 10:42:46', 'user', '0', NULL, NULL, 'F', NULL, '1', 'T', '0', '9999-1-1', '2010-7-5', '2.0', '0', NULL, NULL, NULL, '');";
sqlPerson += "INSERT INTO `person` (`STATUSCODE`, `educ`, `BATT`, `COMPANY`, `PLATOON`, `PSEQ`, `YEARIN`, `NAME`, `SNAME`, `ID8`, `PERCENT`, `UNIT1`, `UNIT2`, `POSTCODE`, `UNIT4`, `ITEM`, `UNIT3`, `ASK`, `NUMBER`, `UNIT0`, `REM`, `REM2`, `BIRTHDATE`, `ID13`, `EDUCODE0`, `EDUCODE1`, `EDUCODE2`, `ID`, `ISSCHOOL`, `NAVYID`, `ARMID`, `REGCODE`, `OCCCODE`, `RECIVEEDU1`, `RECIVEEDU2`, `SCHOOLCODE`, `SKILLCODE`, `TOWNCODE`, `SWIMCODE`, `SALCODE`, `ADDRESS`, `ADDRESS_MU`, `ADDRESS_SOIL`, `ADDRESS_ROAD`, `FATHER`, `FSNAME`, `MOTHER`, `MSNAME`, `HEIGHT`, `WIDTH`, `PERTYPE`, `RUNCODE`, `PERCENT1`, `PERSTAT`, `REPDATE`, `ARMSTUDY`, `REGDATE`, `RETIREDATE`, `MARK`, `BLOOD`, `REASON`, `IS_REQUEST`, `SORDER`, `NLABEL`, `NUMBOX`, `RECORDDATE`, `RECORDBY`, `IS_IMPORT`, `PIC`, `OLDYEARIN`, `FLAG`, `particular`, `origincode`, `FlagMove`, `AddressMove`, `Copy_of_MOVEDATE`, `MOVEDATE`, `serve`, `serve_doc`, `status_request`, `patient_status`, `kpt`, `shift_release`) " +
"VALUES (@STATUSCODE, @educ, @BATT, @COMPANY, @PLATOON, @PSEQ, @YEARIN, @NAME, @SNAME, @ID8, @PERCENT, @UNIT1, @UNIT2, @POSTCODE, @UNIT4, @ITEM, @UNIT3, @ASK, @NUMBER, @UNIT0, @REM, @REM2, @BIRTHDATE, @ID13, @EDUCODE0, @EDUCODE1, @EDUCODE2, @ID, @ISSCHOOL, @NAVYID, @ARMID, @REGCODE, @OCCCODE, @RECIVEEDU1, @RECIVEEDU2, @SCHOOLCODE, @SKILLCODE, @TOWNCODE, @SWIMCODE, @SALCODE, @ADDRESS, @ADDRESS_MU, @ADDRESS_SOIL, @ADDRESS_ROAD, @FATHER, @FSNAME, @MOTHER, @MSNAME, @HEIGHT, @WIDTH, @PERTYPE, @RUNCODE, @PERCENT1, @PERSTAT, @REPDATE, @ARMSTUDY, @REGDATE, @RETIREDATE, @MARK, @BLOOD, @REASON, @IS_REQUEST, @SORDER, @NLABEL, @NUMBOX, @RECORDDATE, @RECORDBY, @IS_IMPORT, @PIC, @OLDYEARIN, @FLAG, @particular, @origincode, @FlagMove, @AddressMove, @Copy_of_MOVEDATE, @MOVEDATE, @serve, @serve_doc, @status_request, @patient_status, @kpt, @shift_release);";
MySqlCommand cmd = new MySqlCommand(sqlPerson, con);
string[] columnNames = (from dc in dtPerson.Columns.Cast <DataColumn>() select dc.ColumnName).ToArray();
foreach (string cname in columnNames)
{
cmd.Parameters.AddWithValue("@" + cname, dr[cname] == null ? null : dr[cname]);
}
rowsAffect += cmd.ExecuteNonQuery();
}
string sqlTransDetail = "";
foreach (DataRow dr in dtTransDetail.Rows)
{
//sqlTransDetail += "INSERT INTO `trans_detail` (`TRANS_NO`, `ID`, `NavyID`, `TRANS_TYPE`, `CODE1`, `Yearin`, `Batt`, `Company`, `Membercode`, `Membercode5`, `Link`, `DOC_DATE`, `START_DATE`, `END_DATE`, `START_DATE_JUDGE`, `END_DATE_JUDGE`, `REMARK`, `REF_NO`, `REF_NO_2`, `PLACE`, `PLACE_JUDGE`, `ECODE1`, `ECODE2`, `OCCSP`, `REASONBK`, `CAUSE_JUDGE`, `INTIME`, `LEAVETIME`, `INYEAR`, `INMONTH`, `INDAY`, `NAMEPPOST`, `LEANAME`, `HOSPITAL`, `SCHOOL`, `PRISON`, `RE_PRISON`, `CEDIT1`, `CEDIT2`, `Flag`, `Num`, `UPDBY`, `UPDDATE`) " +
//"VALUES (22049, '2574000556', 55713859, 'D', 'C', '3/57', '', '', 'A534JDF', '', '', '2015-11-23', '2015-11-6 00:00:00', '2015-11-20 00:00:00', NULL, NULL, NULL, '450/58', NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, '1', NULL, 0, 0, 14, '', '', NULL, NULL, NULL, NULL, 'ใบรายงานเหตุ', 'แผนกสวัสดิการ กบก.ศฝท.ยศ.ทร.', '0', NULL, 'ple', '2015-11-30 11:38:03');\n";
sqlTransDetail = "INSERT INTO `trans_detail` (";
// get column
string[] columnNames = (from dc in dtTransDetail.Columns.Cast <DataColumn>() select dc.ColumnName).ToArray();
// generate column to insert
foreach (string cname in columnNames)
{
sqlTransDetail += "`" + cname + "`,";
}
sqlTransDetail = sqlTransDetail.Remove(sqlTransDetail.Length - 1, 1);
sqlTransDetail += ") \n VALUES \n(";
// generate values to insert
foreach (string cname in columnNames)
{
sqlTransDetail += "@" + cname + ",";
}
sqlTransDetail = sqlTransDetail.Remove(sqlTransDetail.Length - 1, 1);
sqlTransDetail += ");\n";
MySqlCommand cmd = new MySqlCommand(sqlTransDetail, con);
foreach (string cname in columnNames)
{
cmd.Parameters.AddWithValue("@" + cname, dr[cname] == null ? null : dr[cname]);
}
rowsAffect += cmd.ExecuteNonQuery();
}
string sqlPunishmentDetail = "";
foreach (DataRow dr in dtPunishmentDetail.Rows)
{
//sqlPunishmentDetail += "INSERT INTO `punishment_details` (`Punish_No`, `NavyID`, `Trans_Type`, `Code1`, `Batt`, `Company`, `Yearin`, `Membercode`, `Membercode5`, `Link`, `Fine`, `Other`, `Cedit1`, `Cedit2`, `Ref_No`, `Red_No`, `Black_No`, `Doc_Date`, `Trans_No`, `Trans_Note`, `Start_Date`, `End_Date`, `Law_Date`, `Out_Date`, `InYear`, `InMonth`, `InDay`, `InHour`, `Place`, `NamePPost1`, `ShortPpos`, `LeaName1`, `Level`, `NamePPost`, `LeaName`, `Flag`, `Num`, `UpdBy`, `UpdDate`, `ShortName`) " +
// "VALUES (17, '54703860', 'K', 'A', '', '', '1/47', 'A534JB', '', '', 0, '', 'คำสั่งลงทัณฑ์ทางวินัย', 'A534JBA', 'กห. 0534.8.2/231', '', '', '2005-09-13', 4263, 'ขาดราชการ ครั้งที่ 1 ในเวลาปกติ (กลับเอง)', '2005-09-12', '2005-09-14', '', '', 0, 0, 3, 2, '', 'ผบ. กนร.ศฝท.ยศ.ทร.', 'ผบ.กนร.', 'น.อ.ณัฏฐนันท์ วิเศษสมวงศ์', '4', '', '', '1', '4', 'chy', '2005-12-13 13:06:15', NULL);";
sqlPunishmentDetail = "INSERT INTO `punishment_details` (";
// get column
string[] columnNames = (from dc in dtPunishmentDetail.Columns.Cast <DataColumn>() select dc.ColumnName).ToArray();
// generate column to insert
foreach (string cname in columnNames)
{
sqlPunishmentDetail += "`" + cname + "`,";
}
sqlPunishmentDetail = sqlPunishmentDetail.Remove(sqlPunishmentDetail.Length - 1, 1);
sqlPunishmentDetail += ") \n VALUES \n(";
// generate values to insert
foreach (string cname in columnNames)
{
sqlPunishmentDetail += "@" + cname + ",";
}
sqlPunishmentDetail = sqlPunishmentDetail.Remove(sqlPunishmentDetail.Length - 1, 1);
sqlPunishmentDetail += ");\n";
MySqlCommand cmd = new MySqlCommand(sqlPunishmentDetail, con);
foreach (string cname in columnNames)
{
cmd.Parameters.AddWithValue("@" + cname, dr[cname] == null ? null : dr[cname]);
}
rowsAffect += cmd.ExecuteNonQuery();
}
string sqlDataOldYearin = "";
foreach (DataRow dr in dtDataOldYearin.Rows)
{
//sqlDataOldYearin += "INSERT INTO `dataoldyearin` (`ID`, `navyid`, `ONCE`, `COMPANY`, `BATT`, `PLATOON`, `RUNCODE`, `PSEQ`, `YEARIN`, `p_company`, `p_batt`, `p_platoon`, `p_runcode`, `p_pseq`, `p_yearin`, `UPDBY`, `UPDDATE`) " +
// "VALUES ('2444800002', 54401659, '1', '2', '2', '2', NULL, '04', NULL, '2', '2', '2', NULL, '04', NULL, NULL, '0000-0-0 00:00:00');";
sqlDataOldYearin = "INSERT IN TO `dataoldyearin` (";
// get column
string[] columnNames = (from dc in dtDataOldYearin.Columns.Cast <DataColumn>() select dc.ColumnName).ToArray();
// generate column to insert
foreach (string cname in columnNames)
{
sqlDataOldYearin += "`" + cname + "`,";
}
sqlDataOldYearin = sqlDataOldYearin.Remove(sqlDataOldYearin.Length - 1, 1);
sqlDataOldYearin += ") \n VALUES \n(";
// generate values to insert
foreach (string cname in columnNames)
{
sqlDataOldYearin += "@" + cname + ",";
}
sqlDataOldYearin = sqlDataOldYearin.Remove(sqlDataOldYearin.Length - 1, 1);
sqlDataOldYearin += ");\n";
MySqlCommand cmd = new MySqlCommand(sqlDataOldYearin, con);
foreach (string cname in columnNames)
{
cmd.Parameters.AddWithValue("@" + cname, dr[cname] == null ? null : dr[cname]);
}
rowsAffect += cmd.ExecuteNonQuery();
}
//====== Move Indictment ======
string sqlIndictmentDetail = "";
foreach (DataRow dr in dtIndicment.Rows)
{
sqlIndictmentDetail = "INSERT INTO `indictmenttab` (";
// get column
string[] columnNames = (from dc in dtIndicment.Columns.Cast <DataColumn>() select dc.ColumnName).ToArray();
// generate column to insert
foreach (string cname in columnNames)
{
sqlIndictmentDetail += "`" + cname + "`,";
}
sqlIndictmentDetail = sqlIndictmentDetail.Remove(sqlIndictmentDetail.Length - 1, 1);
sqlIndictmentDetail += ") \n VALUES \n(";
// generate values to insert
foreach (string cname in columnNames)
{
sqlIndictmentDetail += "@" + cname + ",";
}
sqlIndictmentDetail = sqlIndictmentDetail.Remove(sqlIndictmentDetail.Length - 1, 1);
sqlIndictmentDetail += ");\n";
MySqlCommand cmd = new MySqlCommand(sqlIndictmentDetail, con);
foreach (string cname in columnNames)
{
cmd.Parameters.AddWithValue("@" + cname, dr[cname] == null ? null : dr[cname]);
}
rowsAffect += cmd.ExecuteNonQuery();
}
dcoreNav.closeConnection();
}
//// // ** temp comment ** // //
////delete data from NavBD_all
//string sqlDelete = "";
//sqlDelete += "DELETE FROM person WHERE NAVYID = '" + navyid + "';\n";
//sqlDelete += "DELETE FROM trans_detail WHERE NAVYID = '" + navyid + "';\n";
//sqlDelete += "DELETE FROM punishment_details WHERE NAVYID = '" + navyid + "';\n";
//sqlDelete += "DELETE FROM dataoldyearin WHERE NAVYID = '" + navyid + "';\n";
//string result1 = "";
//DBConnection.runCmdTransaction(sqlDelete, dcoreNavAll.getConnection(), out result1);
//if (result1 != "OK")
//{
// MessageBox.Show("มีปัญหาในการลบข้อมูล กรุณาติดต่อ ผู้ดูแลระบบโดยด่วน!! " + result1);
// return;
//}
MessageBox.Show("คัดลอกข้อมูลเรียบร้อย");
Search();
}
}
catch (Exception ex)
{
MessageBox.Show("พบข้อผิดพลาดในการทำรายการ .. " + ex.Message);
if (rowsAffect > 0)
{
//rollback
string sqlDelete = "";
sqlDelete += "DELETE FROM person WHERE NAVYID = '" + navyid + "';\n";
sqlDelete += "DELETE FROM trans_detail WHERE NAVYID = '" + navyid + "';\n";
sqlDelete += "DELETE FROM punishment_details WHERE NAVYID = '" + navyid + "';\n";
sqlDelete += "DELETE FROM dataoldyearin WHERE NAVYID = '" + navyid + "';\n";
sqlDelete += "DELETE FROM indictmenttab WHERE NavyID = '" + navyid + "';\n";
string result1 = "";
dcoreNav.closeConnection();
DBConnection.runCmdTransaction(sqlDelete, dcoreNav.getConnection(), out result1);
}
}
}
else
{
MessageBox.Show("กรุณาเลือกข้อมูลก่อน");
}
}
else
{
MessageBox.Show("กรุณาเลือกข้อมูลก่อน");
}
}
