private void btnLogin_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(txtUsername.Text) || String.IsNullOrEmpty(txtPassword.Text))
{
MessageBox.Show("Please enter the username and password.", "EDI Generator");
return;
}
bool isLogin = false;
MySQLdb db = new MySQLdb();
DataTable dt = db.query("SELECT * FROM staff where STAFF_CODE = '" + txtUsername.Text + "' AND PWD = '" + txtPassword.Text + "';");
if (dt.Rows.Count > 0)
{
isLogin = true;
}
if (isLogin)
{
AuditLog.User = txtUsername.Text;
AuditLog.Log("User logged on.");
FormGenerator f = new FormGenerator(); // This is bad
f.StartPosition = FormStartPosition.Manual;
f.Location = new Point(this.Location.X, this.Location.Y);
this.Hide();
f.ShowDialog();
this.Close();
}
else
{
MessageBox.Show("Login Failure. Please check username or password.", "EDI Generator");
}
}
private void txtBatchNo_TextChanged(object sender, EventArgs e)
{
DataTable dt = db.query("SELECT IFNULL(BATCH_NO, '') AS 'BATCH_NO', COUNT(VOUCHER_NO) AS 'TotalV', IFNULL(GROUP_CONCAT(distinct company_code SEPARATOR ', '), '') AS 'COMP', IFNULL(GROUP_CONCAT(distinct STATUS SEPARATOR ', '), '') AS 'STATUS' FROM voucher_line where BATCH_NO = '" + txtBatchNo.Text + "';");
if (dt.Rows.Count > 0)
{
txtVoucherStatus.Text = dt.Rows[0]["STATUS"].ToString();
txtTotalVouchers.Text = dt.Rows[0]["TotalV"].ToString();
txtCompanyCode.Text = dt.Rows[0]["COMP"].ToString();
if (dt.Rows[0]["COMP"].ToString().Equals("BLUEX"))
{
lb_submit.Visible = true;
cmb_submit.Visible = true;
cmb_submit.SelectedIndex = 0;
}
else
{
cmb_submit.SelectedIndex = 0;
lb_submit.Visible = false;
cmb_submit.Visible = false;
}
}
}
private void CreateSheet_GP()
{
string condition = " WHERE v.BATCH_NO = '" + Batch_No + "' AND TYPE = 'GP' AND `EXTRA_MED` = 0 AND `LAB_XRAY` = 0 AND `SURGICAL` = 0 ORDER BY VOUCHER_NO;";
System.Data.DataTable dt = db.query(SQL + condition);
if (dt.Rows.Count > 0)
{
ExcelWorkBook.Worksheets.Add(After: ExcelWorkBook.Sheets[ExcelWorkBook.Sheets.Count]);
Worksheet sheet = ExcelWorkBook.Worksheets[ExcelWorkBook.Worksheets.Count];
ExcelWorkBook.Worksheets[ExcelWorkBook.Worksheets.Count].Name = "GP";
SetupColumnHeader(sheet);
SetupDataContent(sheet, dt, "GP");
Marshal.ReleaseComObject(sheet);
TotalVouchers += dt.Rows.Count;
}
}
private void CreateTempPair()
{
string trackVoucherNo = "";
string lastVoucherNo = "";
System.Data.DataTable dt = db.query("SELECT cd.COMPANY_DR_CODE, cd.LOC_CODE, v.*, d.TERM_DATE AS DR_TERM_DATE, (SELECT FEE FROM voucher_fee_line vf WHERE v.BATCH_NO = vf.BATCH_NO AND v.VOUCHER_NO = vf.VOUCHER_NO AND v.COMPANY_CODE = vf.COMPANY_CODE AND vf.LINE_NO = 1) AS 'FEE', (SELECT COUNT(*) FROM company_doctor d WHERE v.DR_CODE=d.DR_CODE AND d.COMPANY_CODE = 'BLUEX' AND LOC_CODE <> '' AND v.TREATMENT_DATE < IFNULL(TERM_DATE, '2099-12-31') ) AS 'NUMOFDOC' FROM voucher_line v INNER JOIN doctor d ON v.DR_CODE=d.DR_CODE LEFT JOIN company_doctor cd ON v.DR_CODE=cd.DR_CODE AND cd.COMPANY_CODE = 'BLUEX' AND v.TREATMENT_DATE < IFNULL(cd.TERM_DATE, '2099-12-31') WHERE v.FEE_AMT > 0 AND v.BATCH_NO = '" + Batch_No + "' ORDER BY VOUCHER_NO;");
if (dt.Rows.Count > 0)
{
string data = "";
data += ("HPM").PadRight(5);
data += DateConvert(invoice_date).PadLeft(8);
data += " " + dt.Rows.Count.ToString().PadLeft(5, '0');
data += "T";
Library.WriteFile("TH" + DateConvert(invoice_date) + "HPM.txt", data);
data = "";
foreach (System.Data.DataRow r in dt.Rows)
{
try
{
trackVoucherNo = r["VOUCHER_NO"].ToString();
data += r["POLICY_NO"].ToString().Replace(",", ".").PadRight(10);
data += "".PadLeft(3) + " ";
string IDtype = CheckIDType(r["INSURED_NO"].ToString());
if ((IDtype == "INSUREDNO_4") || (IDtype == "INSUREDNO_7"))
{
data += r["INSURED_NO"].ToString().PadRight(7);
data += "".PadLeft(16);
}
else
{
data += "".PadLeft(7);
data += r["INSURED_NO"].ToString().PadRight(16);
}
data += r["MEMBER_E_NAME"].ToString().Trim().PadRight(30);
data += r["VOUCHER_NO"].ToString().PadRight(7);
data += r["COMPANY_DR_CODE"].ToString().PadRight(5);
data += GetServiceCode(r["TYPE"].ToString()).PadRight(8);
data += r["DIAG_CODE1"].ToString().PadRight(8);
data += r["DIAG_CODE2"].ToString().PadRight(8);
data += GetBenefitCode(r["TYPE"].ToString()).PadRight(3);
data += ((decimal)decimal.Parse(r["FEE"].ToString()) - (decimal)decimal.Parse(r["CO_PAY"].ToString())).ToString("0.00").PadLeft(11);
data += " " + DateConvert(r["TREATMENT_DATE"].ToString()).PadRight(8);
data += r["LOC_CODE"].ToString().PadRight(4);
data += r["SICK_LEAVE"].ToString().PadLeft(3);
data += ("HPM").PadRight(5);
data += "\n";
//##########################################Checking#################################################
if (!String.IsNullOrEmpty(r["POLICY_NO"].ToString()) && r["POLICY_NO"].ToString().Length != 10)
{
ErrorMessage += "V" + trackVoucherNo + " : Wrong Policy No. \n";
}
if (!String.IsNullOrEmpty(r["VOUCHER_NO"].ToString()) && r["VOUCHER_NO"].ToString().Length > 7)
{
ErrorMessage += "V" + trackVoucherNo + " : Wrong Voucher No. \n";
}
if (!String.IsNullOrEmpty(r["MEMBER_STAFF_NO"].ToString()) || !String.IsNullOrEmpty(r["DP_TYPE"].ToString()))
{
ErrorMessage += "V" + trackVoucherNo + " : Insured No. should be combined. \n";
}
if ((r["NUMOFDOC"].ToString() != "1") && (lastVoucherNo != trackVoucherNo))
{
ErrorMessage += "V" + trackVoucherNo + " : " + (r["NUMOFDOC"].ToString() == "0" ? "Missing" : "Wrong") + " Company Doctor Information. \n";
}
if (!String.IsNullOrEmpty(r["DR_TERM_DATE"].ToString()))
{
if ((DateTime.Parse(r["TREATMENT_DATE"].ToString()) - DateTime.Parse(r["DR_TERM_DATE"].ToString())).TotalHours > 1)
{
ErrorMessage += "V" + trackVoucherNo + " : Doctor terminated. \n";
}
}
//##########################################Checking#################################################
lastVoucherNo = trackVoucherNo;
}
catch (Exception ex)
{
AuditLog.Log("Exception Line 119 " + ex.Message);
}
}
Library.WriteFile("TD" + DateConvert(invoice_date) + "HPM.txt", data);
string zipFile = CompressZip("TEMP");
CopyToOutput(zipFile);
if (isAutoSubmission)
{
if (String.IsNullOrEmpty(ErrorMessage))
{
if (File.Exists(Path.Combine(Path.GetTempPath(), zipFile)))
{
FTP_RESULT_T = UploadToFtp(zipFile);
}
}
}
TotalVouchers += dt.Rows.Count;
}
}