private void chkNull(DotDfDetail p)
{
long chk = 0;
decimal chk1 = 0;
p.MNC_DOC_CD = p.MNC_DOC_CD == null ? "" : p.MNC_DOC_CD;
p.MNC_DOC_DAT = p.MNC_DOC_DAT == null ? "" : p.MNC_DOC_DAT;
p.MNC_FN_CD = p.MNC_FN_CD == null ? "" : p.MNC_FN_CD;
p.MNC_FN_DAT = p.MNC_FN_DAT == null ? "" : p.MNC_FN_DAT;
p.MNC_DF_DATE = p.MNC_DF_DATE == null ? "" : p.MNC_DF_DATE;
p.MNC_FN_TYP_DESC = p.MNC_FN_TYP_DESC == null ? "" : p.MNC_FN_TYP_DESC;
p.MNC_DATE = p.MNC_DATE == null ? "" : p.MNC_DATE;
p.MNC_PAT_NAME = p.MNC_PAT_NAME == null ? "" : p.MNC_PAT_NAME;
p.MNC_FN_TYP_CD = p.MNC_FN_TYP_CD == null ? "" : p.MNC_FN_TYP_CD;
p.MNC_DOT_CD_DF = p.MNC_DOT_CD_DF == null ? "" : p.MNC_DOT_CD_DF;
p.MNC_DOT_GRP_CD = p.MNC_DOT_GRP_CD == null ? "" : p.MNC_DOT_GRP_CD;
p.MNC_DOT_NAME = p.MNC_DOT_NAME == null ? "" : p.MNC_DOT_NAME;
p.MNC_PAY_FLAG = p.MNC_PAY_FLAG == null ? "" : p.MNC_PAY_FLAG;
p.MNC_PAY_DAT = p.MNC_PAY_DAT == null ? "" : p.MNC_PAY_DAT;
p.MNC_REF_NO = p.MNC_REF_NO == null ? "" : p.MNC_REF_NO;
p.MNC_REF_DAT = p.MNC_REF_DAT == null ? "" : p.MNC_REF_DAT;
p.MNC_EMP_CD = p.MNC_EMP_CD == null ? "" : p.MNC_EMP_CD;
p.MNC_DF_DET_TYPE = p.MNC_DF_DET_TYPE == null ? "" : p.MNC_DF_DET_TYPE;
p.status_insert_manual = p.status_insert_manual == null ? "" : p.status_insert_manual;
p.MNC_DOC_YR = long.TryParse(p.MNC_DOC_YR, out chk) ? chk.ToString() : "0";
p.MNC_DOC_NO = long.TryParse(p.MNC_DOC_NO, out chk) ? chk.ToString() : "0";
p.MNC_FN_NO = long.TryParse(p.MNC_FN_NO, out chk) ? chk.ToString() : "0";
p.MNC_TIME = long.TryParse(p.MNC_TIME, out chk) ? chk.ToString() : "0";
p.MNC_HN_NO = long.TryParse(p.MNC_HN_NO, out chk) ? chk.ToString() : "0";
p.MNC_HN_YR = long.TryParse(p.MNC_HN_YR, out chk) ? chk.ToString() : "0";
p.MNC_AN_NO = long.TryParse(p.MNC_AN_NO, out chk) ? chk.ToString() : "0";
p.MNC_AN_YR = long.TryParse(p.MNC_AN_YR, out chk) ? chk.ToString() : "0";
p.MNC_PRE_NO = long.TryParse(p.MNC_PRE_NO, out chk) ? chk.ToString() : "0";
p.MNC_PAY_NO = long.TryParse(p.MNC_PAY_NO, out chk) ? chk.ToString() : "0";
p.MNC_PAY_YR = long.TryParse(p.MNC_PAY_YR, out chk) ? chk.ToString() : "0";
p.MNC_DF_GROUP = long.TryParse(p.MNC_DF_GROUP, out chk) ? chk.ToString() : "0";
p.MNC_PAY_TYP = long.TryParse(p.MNC_PAY_TYP, out chk) ? chk.ToString() : "0";
p.MNC_DF_AMT = decimal.TryParse(p.MNC_DF_AMT, out chk1) ? chk1.ToString() : "0";
p.MNC_FN_AMT = decimal.TryParse(p.MNC_FN_AMT, out chk1) ? chk1.ToString() : "0";
p.MNC_PAY_RATE = decimal.TryParse(p.MNC_PAY_RATE, out chk1) ? chk1.ToString() : "0";
}
private void initConfig()
{
dotdfd = new DotDfDetail();
dotdfd.MNC_DOC_CD = "MNC_DOC_CD";
dotdfd.MNC_DOC_YR = "MNC_DOC_YR";
dotdfd.MNC_DOC_NO = "MNC_DOC_NO";
dotdfd.MNC_DOC_DAT = "MNC_DOC_DAT";
dotdfd.MNC_FN_CD = "MNC_FN_CD";
dotdfd.MNC_FN_NO = "MNC_FN_NO";
dotdfd.MNC_FN_DAT = "MNC_FN_DAT";
dotdfd.MNC_DF_DATE = "MNC_DF_DATE";
dotdfd.MNC_FN_TYP_DESC = "MNC_FN_TYP_DESC";
dotdfd.MNC_DF_AMT = "MNC_DF_AMT";
dotdfd.MNC_FN_AMT = "MNC_FN_AMT";
dotdfd.MNC_DATE = "MNC_DATE";
dotdfd.MNC_TIME = "MNC_TIME";
dotdfd.MNC_HN_NO = "MNC_HN_NO";
dotdfd.MNC_HN_YR = "MNC_HN_YR";
dotdfd.MNC_AN_NO = "MNC_AN_NO";
dotdfd.MNC_AN_YR = "MNC_AN_YR";
dotdfd.MNC_PAT_NAME = "MNC_PAT_NAME";
dotdfd.MNC_PRE_NO = "MNC_PRE_NO";
dotdfd.MNC_FN_TYP_CD = "MNC_FN_TYP_CD";
dotdfd.MNC_DOT_CD_DF = "MNC_DOT_CD_DF";
dotdfd.MNC_DOT_GRP_CD = "MNC_DOT_GRP_CD";
dotdfd.MNC_DOT_NAME = "MNC_DOT_NAME";
dotdfd.MNC_PAY_FLAG = "MNC_PAY_FLAG";
dotdfd.MNC_PAY_DAT = "MNC_PAY_DAT";
dotdfd.MNC_PAY_NO = "MNC_PAY_NO";
dotdfd.MNC_PAY_YR = "MNC_PAY_YR";
dotdfd.MNC_REF_NO = "MNC_REF_NO";
dotdfd.MNC_REF_DAT = "MNC_REF_DAT";
dotdfd.MNC_EMP_CD = "MNC_EMP_CD";
dotdfd.MNC_DF_GROUP = "MNC_DF_GROUP";
dotdfd.MNC_PAY_TYP = "MNC_PAY_TYP";
dotdfd.MNC_PAY_RATE = "MNC_PAY_RATE";
dotdfd.MNC_DF_DET_TYPE = "MNC_DF_DET_TYPE";
dotdfd.status_insert_manual = "status_insert_manual";
dotdfd.table = "dotdf_detail";
}
private void BtnImportDfGen_Click(object sender, EventArgs e)
{
//throw new NotImplementedException();
String dfdate = "";
int i = 20000;
DateTime date = new DateTime();
DateTime.TryParse(DateTime.Now.ToString(), out date);
if (date.Year > 2500)
{
date = date.AddYears(-543);
}
else if (date.Year < 2000)
{
date = date.AddYears(543);
}
dfdate = date.ToString("yyyy-MM-dd", new CultureInfo("en-US"));
foreach (Row row in grfSelect.Rows)
{
String doccd = "", docyr = "", docno = "", docdat = "", fncd = "", fnno = "", fndat = "", chk = "", fndsc = "", dfamt = "", vsdate = "", vstime = "", hnno = "", hnyr = "", patname = "", preno = "", paidtypecode = "", dtrid = "", dtrname = "";
String anno = "", anyr = "";
chk = row[colchk] != null ? row[colchk].ToString() : "";
if (!chk.ToLower().Equals("true"))
{
continue;
}
doccd = row[coldoccd] != null ? row[coldoccd].ToString() : "";
docyr = row[coldoc_yr] != null ? row[coldoc_yr].ToString() : "";
docno = row[coldocno] != null ? row[coldocno].ToString() : "";
docdat = row[coldocdat] != null ? row[coldocdat].ToString() : "";
vsdate = row[colvsdate] != null ? row[colvsdate].ToString() : "";
vstime = row[colvstime] != null ? row[colvstime].ToString() : "";
hnno = row[colhnno] != null ? row[colhnno].ToString() : "";
hnyr = row[colhnyr] != null ? row[colhnyr].ToString() : "";
patname = row[colpttname] != null ? row[colpttname].ToString() : "";
preno = row[colpreno] != null ? row[colpreno].ToString() : "";
paidtypecode = row[colpaidtype] != null ? row[colpaidtype].ToString() : "";
dtrid = row[coldtrcode] != null ? row[coldtrcode].ToString() : "";
dtrname = row[coldtrname] != null ? row[coldtrname].ToString() : "";
anno = row[colanno] != null ? row[colanno].ToString() : "";
anyr = row[colanyr] != null ? row[colanyr].ToString() : "";
fncd = "470";
fndsc = "ค่าแพทย์ CHECKUP";
dfamt = "10";
DotDfDetail dotdfd = new DotDfDetail();
dotdfd.MNC_DOC_CD = doccd; //pk
dotdfd.MNC_DOC_YR = docyr; //pk
dotdfd.MNC_DOC_NO = docno; //pk
dotdfd.MNC_DOC_DAT = docdat; //pk
dotdfd.MNC_FN_CD = fncd; //pk
dotdfd.MNC_FN_NO = i.ToString(); //pk
dotdfd.MNC_FN_DAT = docdat; //pk
//dotdfd.MNC_DF_DATE = dfdate;
dotdfd.MNC_DF_DATE = bc.datetoDB(vsdate); // ต้องเป็น vsdate จากการดู data ของ วันต่างๆ
dotdfd.MNC_FN_TYP_DESC = fndsc;
dotdfd.MNC_DF_AMT = dfamt;
dotdfd.MNC_FN_AMT = dfamt;
dotdfd.MNC_DATE = bc.datetoDB(vsdate);
dotdfd.MNC_TIME = vstime;
dotdfd.MNC_HN_NO = hnno;
dotdfd.MNC_HN_YR = hnyr;
dotdfd.MNC_AN_NO = anno;
dotdfd.MNC_AN_YR = anyr;
dotdfd.MNC_PAT_NAME = patname;
dotdfd.MNC_PRE_NO = preno;
dotdfd.MNC_FN_TYP_CD = paidtypecode;
dotdfd.MNC_DOT_CD_DF = dtrid;
dotdfd.MNC_DOT_GRP_CD = "0";
dotdfd.MNC_DOT_NAME = dtrid + "0" + dtrname;
dotdfd.MNC_PAY_FLAG = "Y";
dotdfd.MNC_PAY_DAT = ""; // null
dotdfd.MNC_PAY_NO = ""; // null
dotdfd.MNC_PAY_YR = ""; // null
dotdfd.MNC_REF_NO = ""; // null
dotdfd.MNC_REF_DAT = ""; // null
dotdfd.MNC_EMP_CD = ""; // null
dotdfd.MNC_DF_GROUP = "1";
dotdfd.MNC_PAY_TYP = "2";
dotdfd.MNC_PAY_RATE = dfamt;
dotdfd.MNC_DF_DET_TYPE = ""; // null
dotdfd.status_insert_manual = "1";
String re = bc.bcDB.dotdfdDB.insert(dotdfd);
i++;
}
MessageBox.Show("gen ข้อมูล ค่าแพทย์ checkup เรียบร้อย", "");
}
public String insert(DotDfDetail p)
{
String sql = "", chk = "";
try
{
chkNull(p);
sql = "select * from " + dotdfd.table
+ " Where " + dotdfd.MNC_DOC_CD + "= '" + p.MNC_DOC_CD + "'"
+ " and " + dotdfd.MNC_DOC_YR + "= '" + p.MNC_DOC_YR + "'"
+ " and " + dotdfd.MNC_DOC_NO + "= '" + p.MNC_DOC_NO + "'"
+ " and " + dotdfd.MNC_DOC_DAT + "= '" + p.MNC_DOC_DAT + "'"
+ " and " + dotdfd.MNC_FN_CD + "= '" + p.MNC_FN_CD + "'"
+ " and " + dotdfd.MNC_FN_DAT + "= '" + p.MNC_FN_DAT + "'"
+ " and " + dotdfd.MNC_HN_NO + "= '" + p.MNC_HN_NO + "'"
+ " and " + dotdfd.MNC_HN_YR + "= '" + p.MNC_HN_YR + "'"
+ " and " + dotdfd.MNC_PRE_NO + "= '" + p.MNC_PRE_NO + "'"
+ " and " + dotdfd.MNC_DATE + "= '" + p.MNC_DATE + "'";
DataTable dt = conn.selectData(conn.connMainHIS, sql);
if (dt.Rows.Count > 0)
{
return("");
}
sql = "Delete from " + dotdfd.table
+ " Where " + dotdfd.MNC_DOC_CD + "= '" + p.MNC_DOC_CD + "'"
+ " and " + dotdfd.MNC_DOC_YR + "= '" + p.MNC_DOC_YR + "'"
+ " and " + dotdfd.MNC_DOC_NO + "= '" + p.MNC_DOC_NO + "'"
+ " and " + dotdfd.MNC_DOC_DAT + "= '" + p.MNC_DOC_DAT + "'"
+ " and " + dotdfd.MNC_FN_CD + "= '" + p.MNC_FN_CD + "'"
+ " and " + dotdfd.MNC_FN_DAT + "= '" + p.MNC_FN_DAT + "'"
+ " and " + dotdfd.MNC_HN_NO + "= '" + p.MNC_HN_NO + "'"
+ " and " + dotdfd.MNC_HN_YR + "= '" + p.MNC_HN_YR + "'"
+ " and " + dotdfd.MNC_PRE_NO + "= '" + p.MNC_PRE_NO + "'"
+ " and " + dotdfd.MNC_DATE + "= '" + p.MNC_DATE + "'"
+ " and " + dotdfd.status_insert_manual + "= '1' ";
chk = conn.ExecuteNonQuery(conn.connMainHIS, sql);
sql = "Insert Into " + dotdfd.table + "(" + dotdfd.MNC_DOC_CD + "," + dotdfd.MNC_DOC_YR + "," + dotdfd.MNC_DOC_NO + "," +
dotdfd.MNC_DOC_DAT + "," + dotdfd.MNC_FN_CD + "," + dotdfd.MNC_FN_NO + "," +
dotdfd.MNC_FN_DAT + "," + dotdfd.MNC_DF_DATE + "," + dotdfd.MNC_FN_TYP_DESC + "," +
dotdfd.MNC_DF_AMT + "," + dotdfd.MNC_FN_AMT + "," + dotdfd.MNC_DATE + "," +
dotdfd.MNC_TIME + "," + dotdfd.MNC_HN_NO + "," + dotdfd.MNC_HN_YR + "," +
dotdfd.MNC_AN_NO + "," + dotdfd.MNC_AN_YR + "," + dotdfd.MNC_PAT_NAME + "," +
dotdfd.MNC_PRE_NO + "," + dotdfd.MNC_FN_TYP_CD + "," + dotdfd.MNC_DOT_CD_DF + "," +
dotdfd.MNC_DOT_GRP_CD + "," + dotdfd.MNC_DOT_NAME + "," + dotdfd.MNC_PAY_FLAG + "," +
dotdfd.MNC_DF_GROUP + "," + dotdfd.MNC_PAY_TYP + "," + dotdfd.MNC_PAY_RATE + "," +
dotdfd.status_insert_manual +
") " +
"Values('" + p.MNC_DOC_CD + "','" + p.MNC_DOC_YR + "','" + p.MNC_DOC_NO + "','" +
p.MNC_DOC_DAT + "','" + p.MNC_FN_CD + "','" + p.MNC_FN_NO + "','" +
p.MNC_FN_DAT + "','" + p.MNC_DF_DATE.Replace("'", "''") + "','" + p.MNC_FN_TYP_DESC.Replace("'", "''") + "','" +
p.MNC_DF_AMT + "','" + p.MNC_FN_AMT + "','" + p.MNC_DATE + "','" +
p.MNC_TIME + "','" + p.MNC_HN_NO + "','" + p.MNC_HN_YR + "','" +
p.MNC_AN_NO + "','" + p.MNC_AN_YR + "','" + p.MNC_PAT_NAME + "','" +
p.MNC_PRE_NO + "','" + p.MNC_FN_TYP_CD + "','" + p.MNC_DOT_CD_DF + "','" +
p.MNC_DOT_GRP_CD + "','" + p.MNC_DOT_NAME + "','" + p.MNC_PAY_FLAG + "','" +
p.MNC_DF_GROUP + "','" + p.MNC_PAY_TYP + "','" + p.MNC_PAY_RATE + "','1'" +
") ";
chk = conn.ExecuteNonQuery(conn.connMainHIS, sql);
}
catch (Exception ex)
{
chk = ex.Message + " " + ex.InnerException;
new LogWriter("e", "insert Insert sql " + sql + " ex " + chk);
}
return(chk);
}