public static List <PriceDtl> GetPriceDetails(string prcid, string prcdate)
{
string sql = string.Format(@"SELECT uid, prcid, prcdate,coalesce( m.prodid,v.prodid) prodid,coalesce( m.code,v.code) code,
coalesce( m.codename,v.codename) codename, coalesce(unitprice,0) unitprice, v.modifieddate, v.lastuser
from prod m
left join
(
SELECT uid, prcid, prcdate, p.prodid,i.code,i.codename, unitprice, p.modifieddate, p.lastuser
FROM prcdtl p left outer join prod i on p.prodid= i.prodid where prcdate='{0}' and prcid='{1}'
) v on v.prodid= m.prodid where m.status=0 "
, prcdate, prcid);
DataTable dt = BaseADOPG.GetData(sql);
if (dt.Rows.Count == 0)
{
string sqlo = string.Format(@"SELECT max(prcdate) prcdate FROM prcdtl where prcdate <'{0}'"
, prcdate);
DataTable dto = BaseADOPG.GetData(sqlo);
DateTime d = Convert.ToDateTime(prcdate);
if (dto.Rows.Count > 0)
{
if (dto.Rows[0]["prcdate"] != DBNull.Value)
{
d = Convert.ToDateTime(dto.Rows[0]["prcdate"]);
}
}
// DateTime d = Convert.ToDateTime(prcdate);
sql = string.Format(@"SELECT uid, prcid, prcdate,coalesce( m.prodid,v.prodid) prodid,coalesce( m.code,v.code) code,
coalesce( m.codename,v.codename) codename, coalesce(unitprice,0) unitprice, v.modifieddate, v.lastuser
from prod m
left join
(
SELECT uid, prcid, prcdate, p.prodid,i.code,i.codename, unitprice, p.modifieddate, p.lastuser
FROM prcdtl p left outer join prod i on p.prodid= i.prodid where prcdate='{0}' and prcid='{1}'
) v on v.prodid= m.prodid where m.status=0 "
, d.ToString("yyyy-MM-dd"), prcid);
dt = BaseADOPG.GetData(sql);
}
IEnumerable <DataRow> rows = dt.Select().AsEnumerable();
var list = from row in rows
select new PriceDtl()
{
uid = Convert2NumTool <Int32> .ConvertVal(row["uid"]),
prcid = prcid, /* row["prcid"].ToString()*/
prcdate = Convert2NumTool <DateTime> .ConvertVal(row["prcdate"]).ToString("yyyy-MM-dd"),
prodid = Convert2NumTool <Int32> .ConvertVal(row["prodid"]),
unitprice = Convert2NumTool <double> .ConvertVal(row["unitprice"]),
code = Convert2NumTool <string> .ConvertVal(row["code"]),
codename = Convert2NumTool <string> .ConvertVal(row["codename"]),
lastuser = Convert2NumTool <string> .ConvertVal(row["lastuser"]),
modifieddate = Convert2NumTool <DateTime> .ConvertVal(row["modifieddate"])
};
return(list.ToList());
}
public JsonResult SaveProds([FromBody] Prod[] prods)
{
string msg = "";
if (!CSys.OpenCon(ref con))
{
JsonResult errjson = Json(new
{
ok = "no",
Error = "connection error."
});
return(errjson);
}
NpgsqlTransaction sqlTrans;
sqlTrans = con.BeginTransaction();
BaseADOPG.ExceuteSql("Delete from prod ", con, sqlTrans);
dt = BaseADOPG.GetData("Select * from prod");
DataRow nrow;
foreach (Prod itm in prods)
{
nrow = dt.NewRow();
nrow["prodid"] = itm.prodid;
nrow["code"] = itm.code;
nrow["codename"] = itm.codename;
nrow["prodgroup"] = itm.prodgroup;
nrow["modifieddate"] = DateTime.Now;
nrow["lastuser"] = "******";
dt.Rows.Add(nrow);
}
bool success = false;
CAdapter.GenerateProdCommand(ref da);
if (UpdateTable(ref dt, sqlTrans))
{
sqlTrans.Commit();
success = true;
msg = "Saved successfully";
}
else
{
sqlTrans.Rollback();
msg = _err;
}
JsonResult restultJson = Json(new
{
ok = (success) ? "yes" : "no",
Error = msg
});
con.Close();
return(restultJson);
}
public static List <PriceHdr> GetPriceID()
{
DataTable dt = BaseADOPG.GetData("select * from prchdr");
IEnumerable <DataRow> rows = dt.Select().AsEnumerable();
var list = from row in rows
select new PriceHdr()
{
prcid = row["prcid"].ToString(),
prcname = row["prcname"].ToString()
};
return(list.ToList());
}
public static List <PrcSet> GetPrcSet()
{
string sql = string.Format(@"Select * from prchdr");
DataTable dt = BaseADOPG.GetData(sql);
IEnumerable <DataRow> rows = dt.Select().AsEnumerable();
var list = from row in rows
select new PrcSet()
{
prcid = row["prcid"].ToString(),
prcname = row["prcname"].ToString()
};
return(list.ToList());
}
private static bool CheckLogin(UserInfo user, string hashedPassword)
{
DataRow[] dr;
DataTable dtUser = BaseADOPG.GetData("Select * from Aduser where ID = '" + user.name + "' AND PWord = '" + hashedPassword + "' ");
dr = dtUser.Select("ID = '" + user.name + "'");
if (dr.Length > 0)
{
user.fullname = dr[0]["name"].ToString().ToUpper();
}
return(dr.Length > 0);
}
public JsonResult SavePriceSet([FromBody] PrcSet[] prcsets)
{
string msg = "";
if (!CSys.OpenCon(ref con))
{
JsonResult errjson = Json(new
{
ok = "no",
Error = "connection error."
});
return(errjson);
}
NpgsqlTransaction sqlTrans;
sqlTrans = con.BeginTransaction();
// BaseADOPG.ExceuteSql("Delete from prchdr",con,sqlTrans);
dt = BaseADOPG.GetData("Select * from prchdr");
foreach (DataRow row in dt.Select())
{
row.Delete();
}
UpdatePrcSet(prcsets);
bool success = false;
CAdapter.GeneratePrcHdrCommand(ref da);
if (UpdateTable(ref dt, sqlTrans))
{
sqlTrans.Commit();
success = true;
msg = "Saved successfully";
}
else
{
sqlTrans.Rollback();
msg = _err;
}
JsonResult restultJson = Json(new
{
ok = (success) ? "yes" : "no",
Error = msg
});
con.Close();
return(restultJson);
}
private static bool CheckLoginNormal(UserInfo user, string hashedPassword)
{
DataRow[] dr;
DataTable dtUser = BaseADOPG.GetData("Select * from Aduser where id = '" + user.name + "' AND pword = '" + hashedPassword + "' ");
//DataTable dtCust = BaseADOPG.GetData("Select CustCode,CustName from sySaCustAcc where CustCode = '" + user.name + "' AND Active = 1 ");
dr = dtUser.Select("id = '" + user.name + "'");
if (dr.LongLength > 0)
{
user.fullname = dr[0]["name"].ToString().ToUpper();
user.access = "";
}
return(dr.Length > 0);
}
public JsonResult SavePriceSet([FromBody] PriceSet prcset)
{
string msg = "";
if (!CSys.OpenCon(ref con))
{
JsonResult errjson = Json(new
{
ok = "no",
Error = "connection error."
});
return(errjson);
}
// BaseADOPG.ExceuteSql("Delete from from prcdtl where where prcdate='" + prcset.prcdate + "' and prcid='" + prcset.prcid + "'", con, sqlTrans);
dt = BaseADOPG.GetData("Select * from prcdtl where prcdate='" + prcset.prcdate + "' and prcid='" + prcset.prcid + "'");
UpdatePrcdtl(prcset);
bool success = false;
NpgsqlTransaction sqlTrans;
sqlTrans = con.BeginTransaction();
CAdapter.GeneratePrcDtlCommand(ref da);
if (UpdateTable(ref dt, sqlTrans))
{
sqlTrans.Commit();
success = true;
msg = "Saved successfully";
}
else
{
sqlTrans.Rollback();
msg = _err;
}
JsonResult restultJson = Json(new
{
ok = (success) ? "yes" : "no",
Error = msg
});
con.Close();
return(restultJson);
}
public static List <Prod> GetProd()
{
DataTable dt = BaseADOPG.GetData("select * from prod");
IEnumerable <DataRow> rows = dt.Select().AsEnumerable();
var list = from row in rows
select new Prod()
{
prodid = Convert2NumTool <Int32> .ConvertVal(row["prodid"]),
code = row["code"].ToString(),
codename = row["codename"].ToString(),
prodgroup = row["prodgroup"].ToString(),
lastuser = row["lastuser"].ToString(),
modifieddate = Convert2NumTool <DateTime> .ConvertVal(row["modifieddate"]),
status = Convert2NumTool <Int32> .ConvertVal(row["status"]),
};
return(list.ToList());
}
private static bool CheckLoginNormal(UserInfo user, string hashedPassword)
{
DataRow[] dr;
DataTable dtUser = BaseADOPG.GetData("Select * from CustAcct where id = '" + user.name + "' AND pword = '" + hashedPassword + "' ");
//DataTable dtCust = BaseADOPG.GetData("Select CustCode,CustName from sySaCustAcc where CustCode = '" + user.name + "' AND Active = 1 ");
Console.WriteLine("Select * from CustAcct where id = '" + user.name + "' AND pword = '" + hashedPassword + "' ");
dr = dtUser.Select("id = '" + user.name + "'");
if (dr.LongLength > 0)
{
user.fullname = dr[0]["id"].ToString().ToUpper();
user.companyCode = dr[0]["CustomerCode"].ToString().ToUpper();
bool chgpass = Convert2NumTool <bool> .ConvertVal(dr[0]["chgpass"]);
if (chgpass)
{
user.access = "chgpass";
}
}
return(dr.Length > 0);
}