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 const string FTP_SERVER = "210.177.12.144";
//private const string FTP_USR_ID = "sfhpmprd01";
//private const string FTP_USR_PW = "M8x72bpaT";
public static bool FileUploadSFTP(string fileName)
{
try
{
using (var client = new Renci.SshNet.SftpClient(FTP_SERVER, 22, FTP_USR_ID, FTP_USR_PW))
{
client.Connect();
if (client.IsConnected)
{
using (var fileStream = new FileStream(Path.Combine(Path.GetTempPath(), fileName), FileMode.Open))
{
client.BufferSize = 4 * 1024; // bypass Payload error large files
client.UploadFile(fileStream, fileName);
AuditLog.Log("BlueX File is uploaded to FTP successfully. [" + fileName + "]");
}
}
}
return(true);
}
catch (Exception e)
{
AuditLog.Log("Exception Line 109 " + e.Message);
return(false);
}
}
private string CheckIDType(string t)
{
int n;
string result = "STAFFID";
try
{
if (t.Length == 4)
{
t = t.Replace(" ", "#");
bool isNumeric = int.TryParse(t, out n);
if (isNumeric)
{
result = "INSUREDNO_4";
}
}
if (t.Length == 7)
{
t = t.Replace(" ", "#");
if ((t.Substring(4, 1) == "W") || (t.Substring(4, 1) == "H") || (t.Substring(4, 1) == "C"))
{
bool isNumeric = int.TryParse(t.Substring(5, 2), out n);
if (isNumeric)
{
result = "INSUREDNO_7";
}
}
}
}
catch (Exception ex)
{
AuditLog.Log("Exception Line 350 " + ex.Message);
}
return(result);
}
public static void Compress(string zipFileName, string[] importFiles)
{
try
{
byte[] buffer = new byte[4096];
using (ZipOutputStream s = new ZipOutputStream(File.Create(Path.Combine(Path.GetTempPath(), zipFileName))))
{
// 設定壓縮比
s.Password = "******";
s.SetLevel(7);
// 逐一將資料夾內的檔案抓出來壓縮，並寫入至目的檔(.ZIP)
foreach (string filename in importFiles)
{
ZipEntry entry = new ZipEntry(filename);
s.PutNextEntry(entry);
using (FileStream fs = File.OpenRead(Path.Combine(Path.GetTempPath(), filename)))
StreamUtils.Copy(fs, s, buffer);
}
}
}
catch (Exception ex)
{
AuditLog.Log("Exception Line 44 " + ex.Message);
}
}
public void GenerateEDI()
{
try
{
CreateTempPair();
CreateBillPair();
OpenFolder();
}
catch (Exception exHandle)
{
ErrorMessage += "Main:" + exHandle.Message + "\n";
AuditLog.Log("Exception Line 64 " + exHandle.Message);
}
finally
{
}
}
private String DateConvert(string d)
{
string result = "";
if (!String.IsNullOrEmpty(d))
{
try
{
DateTime oDate = DateTime.Parse(d);
result = oDate.ToString("yyyyMMdd");
}
catch (Exception ex)
{
AuditLog.Log("Exception Line 368 " + ex.Message);
}
}
return(result);
}
public static bool CheckFileExistsOnFTP(string fileName)
{
try
{
string logMessage = "";
long ftp_fileLen = 0;
using (var sftp = new Renci.SshNet.SftpClient(FTP_SERVER, 22, FTP_USR_ID, FTP_USR_PW))
{
try
{
sftp.Connect();
SftpFile file = sftp.Get(fileName);
ftp_fileLen = file.Attributes.Size;
}
catch (Exception Sftpex)
{
AuditLog.Log("Exception Line 129 " + Sftpex.ToString());
}
}
long fileLen = new System.IO.FileInfo(Path.Combine(Path.GetTempPath(), fileName)).Length;
logMessage = "BlueX File is existed to FTP? ";
if (ftp_fileLen == fileLen)
{
logMessage += "[Yes]";
}
else
{
logMessage += "[No]";
}
AuditLog.Log(logMessage);
return(true);
}
catch (WebException ex)
{
AuditLog.Log("Exception Line 148 " + ex.Message);
FtpWebResponse response = (FtpWebResponse)ex.Response;
if (response.StatusCode == FtpStatusCode.ActionNotTakenFileUnavailable)
{
return(false);
}
}
return(false);
}
public void GenerateEDI()
{
ExcelApp = new Application();
ExcelWorkBook = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
try
{
CreateSheet_GP();
CreateSheet_GP_MED();
CreateSheet_GP_LAB();
CreateSheet_GP_MOP();
CreateSheet_GP_MED_LAB();
CreateSheet_GP_MED_MOP();
CreateSheet_GP_LAB_MOP();
CreateSheet_GP_MED_LAB_MOP();
CreateSheet_SP();
CreateSheet_SP_MED();
CreateSheet_SP_LAB();
CreateSheet_SP_MOP();
CreateSheet_SP_MED_LAB();
CreateSheet_SP_MED_MOP();
CreateSheet_SP_LAB_MOP();
CreateSheet_SP_MED_LAB_MOP();
CreateSheet_LAB();
CreateSheet_PHY();
ExcelWorkBook.Worksheets[1].Delete();
Marshal.ReleaseComObject(ExcelWorkBook);
ExcelApp.Visible = true;
Marshal.ReleaseComObject(ExcelApp);
AuditLog.Log("AIA File is exported.");
}
catch (Exception exHandle)
{
ErrorMessage += "Main:" + exHandle.Message + "\n";
AuditLog.Log("Exception Line 82 " + exHandle.Message);
}
finally
{
}
}
private String DateConvert2(string d)
{
string result = "";
if (!String.IsNullOrEmpty(d))
{
try
{
DateTime oDate = DateTime.Parse(d);
result = oDate.ToString("yyyy-MM-dd");
}
catch (Exception ex)
{
ErrorMessage += "DateConvert:" + ex.Message + " (" + d + ")\n";
AuditLog.Log("Exception Line 834 " + ex.Message);
}
}
return(result);
}
private void CopyToOutput(string fileName)
{
try
{
string outputPath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location) + filePath;
if (!Directory.Exists(outputPath))
{
System.IO.Directory.CreateDirectory(outputPath);
}
System.IO.File.Copy(System.IO.Path.Combine(Path.GetTempPath(), fileName), System.IO.Path.Combine(outputPath, fileName), true);
AuditLog.Log("BlueX File is created. [" + Path.Combine(filePath, fileName) + "]");
}
catch (Exception ex)
{
AuditLog.Log("Exception Line 261 " + ex.Message);
}
}
private void backgroundWorker1_RunWorkerCompleted_1(object sender, RunWorkerCompletedEventArgs e)
{
string message = "";
txtBatchNo.ReadOnly = false;
dpInvoiceDate.Enabled = true;
pb_loading.Visible = false;
btnGenerator.Enabled = true;
if (ReportSelected)
{
if (String.IsNullOrEmpty(ExportErrorMessage))
{
message = "EDI exported successfully.";
message += "\nTotal of exported Vouchers = " + ExportVouchers;
AuditLog.Log("UX: EDI exported successfully. Total of exported Vouchers = " + ExportVouchers);
if (cmb_submit.SelectedIndex == 1)
{
message += "\n\nFTP uploaded " + (SubmissionResult ? "successfully" : "failure") + ".";
AuditLog.Log("UX: FTP uploaded " + (SubmissionResult ? "successfully" : "failure") + ".");
}
}
else
{
message = "EDI exported with error as below.\nTotal of exported Vouchers = " + ExportVouchers + "\n-------------------------------------\n" + ExportErrorMessage;
AuditLog.Log("UX: EDI exported with error as below. Total of exported Vouchers = " + ExportVouchers);
AuditLog.Log("UX: " + ExportErrorMessage.Replace("\n", ", "));
if (cmb_submit.SelectedIndex == 1)
{
message += "\nFTP uploaded is pending because of error.";
AuditLog.Log("UX: FTP uploaded is pending because of error.");
}
}
}
else
{
message = "No EDI format provided for this company.";
}
MessageBox.Show(message, "EDI Generator", MessageBoxButtons.OK, MessageBoxIcon.None, MessageBoxDefaultButton.Button1, (MessageBoxOptions)0x40000);
}
private void btnGenerator_Click(object sender, EventArgs e)
{
ReportSelected = false;
isAutoSubmission = false;
if (String.IsNullOrEmpty(txtCompanyCode.Text))
{
MessageBox.Show("Please provide the correct batch no.", "EDI Generator");
return;
}
if (!txtVoucherStatus.Text.Equals("CONFIRMED"))
{
MessageBox.Show("Vouchers are waiting for verification.", "EDI Generator");
return;
}
if (cmb_submit.SelectedIndex == 1)
{
var confirmResult = MessageBox.Show("Are you sure you want to upload the result to FTP?", "Are you sure?", MessageBoxButtons.YesNo);
if (confirmResult == DialogResult.No)
{
return;
}
else
{
isAutoSubmission = true;
}
}
AuditLog.Log("User goes to export EDI. (Batch No:" + txtBatchNo.Text + ", Company: " + txtCompanyCode.Text.Trim() + ")");
if (cmb_submit.SelectedIndex == 1)
{
AuditLog.Log("User requests for auto submission.");
}
btnGenerator.Enabled = false;
btnGenerator.Refresh();
pb_loading.Visible = true;
pb_loading.Refresh();
txtBatchNo.ReadOnly = true;
dpInvoiceDate.Enabled = false;
backgroundWorker1.RunWorkerAsync();
}
private void SetupDataContent(Worksheet sheet, System.Data.DataTable dt, string strtype)
{
string trackVoucherNo = "";
int execel_row = 2;
List <decimal> trx_preAmt = null;
List <string> trx_proCode = null;
List <string> trx_proDesc = null;
List <string> trx_h_remark = null;
foreach (System.Data.DataRow r in dt.Rows)
{
try
{
string voucher_remark = "";
trackVoucherNo = r["VOUCHER_NO"].ToString();
System.Data.DataTable dt2 = db.query("SELECT * FROM humphrey.voucher_fee_line WHERE BATCH_NO = '" + Batch_No + "' AND COMPANY_CODE = '" + r["COMPANY_CODE"].ToString() + "' AND VOUCHER_NO = '" + r["VOUCHER_NO"].ToString() + "' ORDER BY LINE_NO;");
foreach (System.Data.DataRow r2 in dt2.Rows)
{
trx_preAmt = null;
trx_proCode = null;
trx_proDesc = null;
trx_h_remark = null;
if (!strtype.Equals("LAB"))
{
voucher_remark = GetProDesc(r["TYPE"].ToString()) + ((GetProDesc(r["TYPE"].ToString()) == GetProDesc(r2["FEE_CODE"].ToString())) ? "" : "+" + GetProDesc(r2["FEE_CODE"].ToString()));
}
else
{
voucher_remark = GetProDesc(r2["FEE_CODE"].ToString());
}
trx_preAmt = new List <decimal>();
trx_preAmt.Add(Convert.ToDecimal(r2["FEE"].ToString()));
if (r2["FEE_CODE"].ToString().ToUpper() == "EXTRA MED")
{
if (Med_Split)
{
if (!String.IsNullOrEmpty(r["DP_TYPE"].ToString()))
{
try
{
trx_preAmt = new List <decimal>();
decimal longM = Convert.ToDecimal(((String.IsNullOrEmpty(r["DP_TYPE"].ToString()) ? "0" : r["DP_TYPE"].ToString())));
decimal fullM = Convert.ToDecimal(((String.IsNullOrEmpty(r2["FEE"].ToString()) ? "0" : r2["FEE"].ToString())));
trx_preAmt.Add(fullM - longM); //Short Med
trx_preAmt.Add(longM); //Long Med
}
catch (Exception ex2)
{
ErrorMessage += "V" + trackVoucherNo + " : (1)" + ex2.Message + "\n";
AuditLog.Log("Exception Line 588 " + ex2.Message);
}
}
}
}
if (r2["FEE_CODE"].ToString().ToUpper() == "LAB/XRAY")
{
string strLabCode = r["LAB_XRAY_CODE"].ToString();
decimal sum = 0;
decimal input = Convert.ToDecimal(r2["FEE"].ToString());
if (!String.IsNullOrEmpty(strLabCode))
{
try
{
string[] arr_LabCode = strLabCode.Split(',');
trx_proCode = new List <string>();
trx_proDesc = new List <string>();
trx_preAmt = new List <decimal>();
trx_h_remark = new List <string>();
foreach (string s in arr_LabCode)
{
if (!String.IsNullOrEmpty(s))
{
string cpt_code = s.Trim();
string sub_code = "";
if (cpt_code.IndexOf("-") > 0)
{
string[] arr_code = cpt_code.Split('-');
cpt_code = arr_code[0].Trim();
if (arr_code.Length > 1)
{
sub_code = arr_code[1].Trim();
}
}
string sql = "SELECT CODE, DESCRIPTION, FEE FROM lab_mop_mapping m WHERE INACTIVE = 0 AND TYPE = 'LAB' AND CODE = '" + cpt_code + "'";
if (!String.IsNullOrEmpty(sub_code))
{
sql += " AND subcode = '" + sub_code + "'";
}
sql += " AND (COMPANY_CODE = '" + r["COMPANY_CODE"].ToString().Substring(0, 3) + "' OR COMPANY_CODE = '" + r["COMPANY_CODE"].ToString() + "')";
sql += " AND (DR_CODE = '' OR DR_CODE like '%@" + r["DR_CODE"].ToString() + "@%') ORDER BY DR_CODE DESC, COMPANY_CODE DESC";
System.Data.DataTable dt3 = db.query(sql);
if (dt3.Rows.Count > 0)
{
trx_proCode.Add(dt3.Rows[0]["CODE"].ToString());
trx_proDesc.Add(dt3.Rows[0]["DESCRIPTION"].ToString());
trx_preAmt.Add(Convert.ToDecimal(dt3.Rows[0]["FEE"].ToString()));
trx_h_remark.Add("");
sum += Convert.ToDecimal(dt3.Rows[0]["FEE"].ToString());
}
else
{
trx_proCode.Add(s.Trim());
trx_proDesc.Add("");
trx_preAmt.Add(input);
trx_h_remark.Add("＃NO LAB FOUND＃");
sum += input;
}
}
}
if (input != sum)
{
for (int i = 0; i < trx_h_remark.Count; i++)
{
if (String.IsNullOrEmpty(trx_h_remark[i]))
{
trx_h_remark[i] = "＃TOTAL NOT MATCHED＃";
}
}
}
}
catch (Exception ex3)
{
ErrorMessage += "V" + trackVoucherNo + " : (2)" + ex3.Message + "\n";
AuditLog.Log("Exception Line 662 " + ex3.Message);
}
}
else
{
trx_h_remark = new List <string>();
trx_h_remark.Add("＃LAB MISSING＃");
}
}
if (r2["FEE_CODE"].ToString().ToUpper() == "SURGICAL")
{
string strSurgicalCode = r["SURGICAL_CODE"].ToString();
decimal sum = 0;
decimal input = Convert.ToDecimal(r2["FEE"].ToString());
if (!String.IsNullOrEmpty(strSurgicalCode))
{
try
{
string[] arr_SurgicalCode = strSurgicalCode.Split(',');
trx_proCode = new List <string>();
trx_proDesc = new List <string>();
trx_preAmt = new List <decimal>();
trx_h_remark = new List <string>();
foreach (string s in arr_SurgicalCode)
{
if (!String.IsNullOrEmpty(s))
{
string cpt_code = s.Trim();
string sub_code = "";
if (cpt_code.IndexOf("-") > 0)
{
string[] arr_code = cpt_code.Split('-');
cpt_code = arr_code[0].Trim();
if (arr_code.Length > 1)
{
sub_code = arr_code[1].Trim();
}
}
string sql = "SELECT CODE, DESCRIPTION, FEE FROM lab_mop_mapping m WHERE INACTIVE = 0 AND TYPE = 'MOP' AND CODE = '" + cpt_code + "'";
if (!String.IsNullOrEmpty(sub_code))
{
sql += " AND subcode = '" + sub_code + "'";
}
sql += " AND (COMPANY_CODE = '" + r["COMPANY_CODE"].ToString().Substring(0, 3) + "' OR COMPANY_CODE = '" + r["COMPANY_CODE"].ToString() + "')";
sql += " AND (DR_CODE = '' OR DR_CODE like '%@" + r["DR_CODE"].ToString() + "@%') ORDER BY DR_CODE DESC, COMPANY_CODE DESC";
System.Data.DataTable dt3 = db.query(sql);
//System.Data.DataTable dt3 = db.query("SELECT CODE, DESCRIPTION, FEE FROM lab_mop_mapping m WHERE TYPE = 'MOP' AND CODE = '" + s.Trim() + "' AND INACTIVE = 0;");
if (dt3.Rows.Count > 0)
{
trx_proCode.Add(dt3.Rows[0]["CODE"].ToString());
trx_proDesc.Add(dt3.Rows[0]["DESCRIPTION"].ToString());
trx_preAmt.Add(Convert.ToDecimal(dt3.Rows[0]["FEE"].ToString()));
trx_h_remark.Add("");
sum += Convert.ToDecimal(dt3.Rows[0]["FEE"].ToString());
}
else
{
trx_proCode.Add(s.Trim());
trx_proDesc.Add("");
trx_preAmt.Add(input);
trx_h_remark.Add("＃NO MOP FOUND＃");
sum += input;
}
}
}
if (input != sum)
{
for (int i = 0; i < trx_h_remark.Count; i++)
{
if (String.IsNullOrEmpty(trx_h_remark[i]))
{
trx_h_remark[i] = "＃TOTAL NOT MATCHED＃";
}
}
}
}
catch (Exception ex3)
{
ErrorMessage += "V" + trackVoucherNo + " : (3)" + ex3.Message + "\n";
AuditLog.Log("Exception Line 742 " + ex3.Message);
}
}
else
{
trx_h_remark = new List <string>();
trx_h_remark.Add("＃MOP MISSING＃");
}
}
string trx_company_doctor_code = "";
string sql2 = "SELECT COMPANY_DR_CODE FROM company_doctor WHERE DR_CODE = '" + r["DR_CODE"].ToString() + "' AND COMPANY_CODE = '" + r["COMPANY_CODE"].ToString().Trim() + "' AND (TYPE1 = '" + r["TYPE"].ToString() + "' OR TYPE2 = '" + r["TYPE"].ToString() + "') AND '" + DateConvert2(r["TREATMENT_DATE"].ToString()) + "' < IFNULL(TERM_DATE, '2099-12-31')";
System.Data.DataTable dt4 = db.query(sql2);
if (dt4.Rows.Count == 1)
{
trx_company_doctor_code = dt4.Rows[0]["COMPANY_DR_CODE"].ToString();
}
else
{
trx_h_remark = new List <string>();
for (int i = 1; i <= trx_preAmt.Count; i++)
{
trx_h_remark.Add("＃Wrong Company Doctor Information＃");
}
}
if (!String.IsNullOrEmpty(r["DR_TERM_DATE"].ToString()))
{
if ((DateTime.Parse(r["TREATMENT_DATE"].ToString()) - DateTime.Parse(r["DR_TERM_DATE"].ToString())).TotalHours > 1)
{
trx_h_remark = new List <string>();
for (int i = 1; i <= trx_preAmt.Count; i++)
{
trx_h_remark.Add("＃Doctor terminated＃");
}
}
}
for (int i = 1; i <= trx_preAmt.Count; i++)
{
try
{
if (trx_preAmt[i - 1] > 0)
{
if (trx_proCode == null)
{
sheet.Cells[execel_row, 13] = GetProCode(r2["FEE_CODE"].ToString(), strtype, i.ToString());
}
else
{
if (trx_proCode != null && trx_proCode.Count >= i)
{
sheet.Cells[execel_row, 13] = trx_proCode[i - 1];
}
if (trx_proDesc != null && trx_proDesc.Count >= i)
{
sheet.Cells[execel_row, 14] = trx_proDesc[i - 1];
}
}
sheet.Cells[execel_row, 15] = trx_preAmt[i - 1];
sheet.Cells[execel_row, 1] = trx_company_doctor_code;
sheet.Cells[execel_row, 2] = r["DR_E_NAME"].ToString();
sheet.Cells[execel_row, 3] = r["INVOICE_NO"].ToString();
sheet.Cells[execel_row, 4] = DateConvert(invoice_date);
sheet.Cells[execel_row, 5] = r["MEMBER_CODE"].ToString();
sheet.Cells[execel_row, 6] = r["MEMBER_E_NAME"].ToString();
sheet.Cells[execel_row, 7] = DateConvert(r["TREATMENT_DATE"].ToString());
sheet.Cells[execel_row, 8] = ((r["TYPE"].ToString() == "PHY" || r["TYPE"].ToString() == "PHY2" || r["TYPE"].ToString() == "CU") ? "PH" : r["TYPE"].ToString());
sheet.Cells[execel_row, 9] = r["DIAG_CODE1"].ToString();
sheet.Cells[execel_row, 10] = r["DIAG_DESC1"].ToString();
sheet.Cells[execel_row, 11] = r["DIAG_CODE2"].ToString();
sheet.Cells[execel_row, 12] = r["DIAG_DESC2"].ToString();
sheet.Cells[execel_row, 16] = ((r2["LINE_NO"].ToString() == "1") ? r["CO_PAY"].ToString() : "0");
sheet.Cells[execel_row, 17] = DateConvert(r["SL_FROM"].ToString());
sheet.Cells[execel_row, 18] = DateConvert(r["SL_TO"].ToString());
sheet.Cells[execel_row, 19] = r["VOUCHER_NO"].ToString();
sheet.Cells[execel_row, 25] = r["MEMBER_STAFF_NO"].ToString();
sheet.Cells[execel_row, 24] = voucher_remark;
if (trx_h_remark != null && trx_h_remark.Count >= i)
{
sheet.Cells[execel_row, 26] = trx_h_remark[i - 1];
if (!String.IsNullOrEmpty(trx_h_remark[i - 1]))
{
sheet.Cells[execel_row, 26].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
}
}
execel_row++;
}
}
catch (Exception ex3)
{
ErrorMessage += "V" + trackVoucherNo + " : (4)" + ex3.Message + "\n";
AuditLog.Log("Exception Line 801 " + ex3.Message);
}
}
}
}
catch (Exception exx)
{
ErrorMessage += "V" + trackVoucherNo + " : (5)" + exx.Message + "\n";
AuditLog.Log("Exception Line 810 " + exx.Message);
}
}
sheet.Columns.AutoFit();
}
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;
}
}
private void CreateBillPair()
{
string trackVoucherNo = "";
System.Data.DataTable dt = db.query("SELECT cd.COMPANY_DR_CODE, cd.LOC_CODE, v.CO_PAY, v.POLICY_NO,v.INSURED_NO,v.MEMBER_E_NAME,v.TYPE,v.DIAG_CODE1,v.DIAG_CODE2,v.TREATMENT_DATE,v.SICK_LEAVE, f.* FROM voucher_line v LEFT JOIN voucher_fee_line f ON v.COMPANY_CODE=f.COMPANY_CODE AND v.BATCH_NO=f.BATCH_NO AND v.VOUCHER_NO=f.VOUCHER_NO 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.BATCH_NO = '" + Batch_No + "' ORDER BY VOUCHER_NO, LINE_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("BH" + DateConvert(invoice_date) + "HPM.txt", data);
data = "";
foreach (System.Data.DataRow r in dt.Rows)
{
try
{
trackVoucherNo = r["VOUCHER_NO"].ToString();
if (decimal.Parse(r["FEE"].ToString()) > 0)
{
data += r["POLICY_NO"].ToString().Replace(",", ".").PadLeft(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["FEE_CODE"].ToString()).PadRight(8);
data += r["DIAG_CODE1"].ToString().PadRight(8);
data += r["DIAG_CODE2"].ToString().PadRight(8);
data += GetBenefitCode(r["FEE_CODE"].ToString()).PadRight(3);
if (r["LINE_NO"].ToString().Equals("1"))
{
data += ((decimal)decimal.Parse(r["FEE"].ToString()) - (decimal)decimal.Parse(r["CO_PAY"].ToString())).ToString("0.00").PadLeft(11);
}
else
{
data += ((decimal)decimal.Parse(r["FEE"].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";
}
}
catch (Exception ex)
{
AuditLog.Log("Exception Line 196 " + ex.Message);
}
}
Library.WriteFile("BD" + DateConvert(invoice_date) + "HPM.txt", data);
string zipFile = CompressZip("BILL");
CopyToOutput(zipFile);
if (isAutoSubmission)
{
if (String.IsNullOrEmpty(ErrorMessage))
{
if (File.Exists(Path.Combine(Path.GetTempPath(), zipFile)))
{
FTP_RESULT_B = UploadToFtp(zipFile);
}
}
}
}
}
