public static StcrdMinVM ToViewModel(this StcrdMin s)
{
if (s == null)
{
return(null);
}
return(new StcrdMinVM
{
stcrdmin = s,
warranty_type = s.warranty_type == WarrantyType.TYPE.DEFAULT ? WarrantyType.Default : WarrantyType.Specify
});
}
//public static List<ArtrnMin> InvoiceList(string data_path, bool warranty_iv = true, bool warranted_iv = false, INVOICE_TYPE invoice_type = INVOICE_TYPE.IV)
//{
// if (!File.Exists(data_path + "artrn.dbf"))
// throw new Exception("ค้นหาไฟล์ artrn.dbf ไม่พบ");
// if (!File.Exists(data_path + "stcrd.dbf"))
// throw new Exception("ค้นหาไฟล์ stcrd.dbf ไม่พบ");
// if (!File.Exists(data_path + "stmas.dbf"))
// throw new Exception("ค้นหาไฟล์ stmas.dbf ไม่พบ");
// if (!File.Exists(data_path + "isacc.dbf"))
// throw new Exception("ค้นหาไฟล์ isacc.dbf ไม่พบ");
// if (!File.Exists(data_path + "artrnrm.dbf"))
// throw new Exception("ค้นหาไฟล์ artrnrm.dbf ไม่พบ");
// DataTable dt = new DataTable();
// using (OleDbConnection conn = new OleDbConnection(@"Provider=VFPOLEDB.1;Data Source=" + data_path))
// {
// conn.Open();
// var cmd = conn.CreateCommand();
// if (warranty_iv)
// {
// cmd.CommandText = "Select artrn.docnum, artrn.docdat, artrn.cuscod, artrn.num2, armas.cusnam From artrn ";
// cmd.CommandText += "Left Join armas On artrn.cuscod = armas.cuscod ";
// cmd.CommandText += "Where artrn.docnum In ";
// cmd.CommandText += "(Select stcrd.docnum From stcrd Left Join stmas On stcrd.stkcod=stmas.stkcod Left Join isacc On stmas.acccod=isacc.acccod Where isacc.method='X') ";
// cmd.CommandText += "And rectyp='" + ((int)invoice_type).ToString() + "' And num2=0 ";
// cmd.CommandText += "Order By docdat,docnum ASC";
// }
// else
// {
// cmd.CommandText = "Select artrn.docnum, artrn.docdat, artrn.cuscod, artrn.num2, armas.cusnam From artrn ";
// cmd.CommandText += "Left Join armas On artrn.cuscod = armas.cuscod ";
// cmd.CommandText += "Where artrn.docnum In ";
// cmd.CommandText += "(Select stcrd.docnum From stcrd Left Join stmas On stcrd.stkcod=stmas.stkcod Left Join isacc On stmas.acccod=isacc.acccod Where isacc.method='X') ";
// cmd.CommandText += "And rectyp='" + ((int)invoice_type).ToString() + "' ";
// cmd.CommandText += "Order By docdat,docnum ASC";
// }
// OleDbDataAdapter da = new OleDbDataAdapter(cmd);
// da.Fill(dt);
// conn.Close();
// }
// List<ArtrnMin> artrn = new List<ArtrnMin>();
// foreach (DataRow row in dt.Rows)
// {
// try
// {
// ArtrnMin a = new ArtrnMin
// {
// cuscod = !row.IsNull("cuscod") ? row["cuscod"].ToString().Trim() : string.Empty,
// cusnam = !row.IsNull("cusnam") ? row["cusnam"].ToString().Trim() : string.Empty,
// docnum = !row.IsNull("docnum") ? row["docnum"].ToString().Trim() : string.Empty,
// docdat = !row.IsNull("docdat") ? (DateTime?)row["docdat"] : null,
// warranty_spec = !row.IsNull("num2") ? ((double)row["num2"] > 0 ? "Y" : "") : ""
// };
// artrn.Add(a);
// }
// catch (Exception)
// {
// continue;
// }
// }
// return artrn;
//}
public static Invoice InVoice(string data_path, string docnum)
{
if (!File.Exists(data_path + "artrn.dbf"))
{
throw new Exception("ค้นหาไฟล์ artrn.dbf ไม่พบ");
}
if (!File.Exists(data_path + "stcrd.dbf"))
{
throw new Exception("ค้นหาไฟล์ stcrd.dbf ไม่พบ");
}
if (!File.Exists(data_path + "stmas.dbf"))
{
throw new Exception("ค้นหาไฟล์ stmas.dbf ไม่พบ");
}
if (!File.Exists(data_path + "isacc.dbf"))
{
throw new Exception("ค้นหาไฟล์ isacc.dbf ไม่พบ");
}
if (!File.Exists(data_path + "artrnrm.dbf"))
{
throw new Exception("ค้นหาไฟล์ artrnrm.dbf ไม่พบ");
}
Invoice inv = new Invoice();
using (OleDbConnection conn = new OleDbConnection(@"Provider=VFPOLEDB.1;Data Source=" + data_path))
{
conn.Open();
DataTable dt = new DataTable();
var cmd = conn.CreateCommand();
cmd.CommandText = "Select artrn.docnum, artrn.docdat, artrn.cuscod, artrn.slmcod, artrn.sonum, artrn.num2, armas.cusnam, armas.addr01, armas.addr02, armas.addr03, armas.zipcod, armas.telnum From artrn Left Join armas On artrn.cuscod = armas.cuscod Where TRIM(docnum)='" + docnum.Trim() + "'";
using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{
dt.Clear();
da.Fill(dt);
if (dt.Rows.Count == 0)
{
return(null);
}
inv.artrn = new ArtrnDesc
{
addr01 = !dt.Rows[0].IsNull("addr01") ? dt.Rows[0]["addr01"].ToString().Trim() : string.Empty,
addr02 = !dt.Rows[0].IsNull("addr02") ? dt.Rows[0]["addr02"].ToString().Trim() : string.Empty,
addr03 = !dt.Rows[0].IsNull("addr03") ? dt.Rows[0]["addr03"].ToString().Trim() : string.Empty,
cuscod = !dt.Rows[0].IsNull("cuscod") ? dt.Rows[0]["cuscod"].ToString().Trim() : string.Empty,
cusnam = !dt.Rows[0].IsNull("cusnam") ? dt.Rows[0]["cusnam"].ToString().Trim() : string.Empty,
docdat = !dt.Rows[0].IsNull("docdat") ? (DateTime?)dt.Rows[0]["docdat"] : null,
docnum = !dt.Rows[0].IsNull("docnum") ? dt.Rows[0]["docnum"].ToString().Trim() : string.Empty,
slmcod = !dt.Rows[0].IsNull("slmcod") ? dt.Rows[0]["slmcod"].ToString().Trim() : string.Empty,
sonum = !dt.Rows[0].IsNull("sonum") ? dt.Rows[0]["sonum"].ToString().Trim() : string.Empty,
telnum = !dt.Rows[0].IsNull("telnum") ? dt.Rows[0]["telnum"].ToString().Trim() : string.Empty,
zipcod = !dt.Rows[0].IsNull("zipcod") ? dt.Rows[0]["zipcod"].ToString().Trim() : string.Empty,
num2 = !dt.Rows[0].IsNull("num2") ? (double)dt.Rows[0]["num2"] : 0
};
}
cmd = conn.CreateCommand();
cmd.CommandText = "Select stcrd.tqucod, stcrd.disc, stcrd.docnum, stcrd.docdat, stcrd.seqnum, stcrd.stkcod, stcrd.stkdes, stcrd.loccod, stcrd.trnqty, stcrd.trnval, stcrd.unitpr, stmas.warmonth, isacc.method, x.rem_remark as rem_remark From stcrd ";
cmd.CommandText += "Left Join stmas On stcrd.stkcod=stmas.stkcod ";
cmd.CommandText += "Left Join isacc On stmas.acccod=isacc.acccod ";
cmd.CommandText += "Left Join (Select artrnrm.docnum as rem_docnum, artrnrm.seqnum as rem_seqnum, artrnrm.remark as rem_remark From artrnrm Where artrnrm.docnum='" + docnum + "' AND SUBSTR(artrnrm.seqnum,1,1)!='@' AND LOWER(artrnrm.remark) LIKE 'warranty.%') AS x On x.rem_docnum=stcrd.docnum AND SUBSTR(x.rem_seqnum,1,3)=stcrd.seqnum ";
cmd.CommandText += "Where TRIM(stcrd.docnum)='" + docnum + "' and isacc.method='X' Order By stcrd.seqnum";
using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{
dt.Clear();
da.Fill(dt);
inv.stcrds = new List <StcrdMinVM>();
for (int i = 0; i < dt.Rows.Count; i++)
{
var st = new StcrdMin
{
disc = !dt.Rows[i].IsNull("disc") ? dt.Rows[i]["disc"].ToString() : string.Empty,
docdat = !dt.Rows[i].IsNull("docdat") ? (DateTime?)dt.Rows[i]["docdat"] : null,
docnum = !dt.Rows[i].IsNull("docnum") ? dt.Rows[i]["docnum"].ToString().Trim() : string.Empty,
cost_method = !dt.Rows[i].IsNull("method") ? dt.Rows[i]["method"].ToString() : string.Empty,
loccod = !dt.Rows[i].IsNull("loccod") ? dt.Rows[i]["loccod"].ToString().Trim() : string.Empty,
seqnum = !dt.Rows[i].IsNull("seqnum") ? dt.Rows[i]["seqnum"].ToString() : string.Empty,
stkcod = !dt.Rows[i].IsNull("stkcod") ? dt.Rows[i]["stkcod"].ToString().Trim() : string.Empty,
stkdes = !dt.Rows[i].IsNull("stkdes") ? dt.Rows[i]["stkdes"].ToString().Trim() : string.Empty,
tqucod = !dt.Rows[i].IsNull("tqucod") ? dt.Rows[i]["tqucod"].ToString().Trim() : string.Empty,
trnqty = !dt.Rows[i].IsNull("trnqty") ? (double)dt.Rows[i]["trnqty"] : 0,
trnval = !dt.Rows[i].IsNull("trnval") ? (double)dt.Rows[i]["trnval"] : 0,
unitpr = !dt.Rows[i].IsNull("unitpr") ? (double)dt.Rows[i]["unitpr"] : 0,
warranty_default = !dt.Rows[i].IsNull("warmonth") ? (dt.Rows[i]["warmonth"].ToString().Trim().Length > 0 ? Convert.ToInt32(dt.Rows[i]["warmonth"].ToString().Trim()) : 0) : 0,
warranty_remark = !dt.Rows[i].IsNull("rem_remark") && dt.Rows[i]["rem_remark"].ToString().ToLower().StartsWith("warranty.") ? dt.Rows[i]["rem_remark"].ToString().ToLower().TrimStart(("warranty.").ToCharArray()).Trim() : string.Empty,
warranty_period = 0,
};
int period = 0;
Int32.TryParse(st.warranty_remark, out period);
st.warranty_period = period == 0 ? st.warranty_default : period;
st.warranty_type = period == 0 ? WarrantyType.TYPE.DEFAULT : WarrantyType.TYPE.SPECIFY;
inv.stcrds.Add(st.ToViewModel());
}
}
conn.Close();
}
return(inv);
}