public DataTable GetStockInOTList(StockInFGData data)
{
DataTable dt = SearchDAL.GetReceiveOTList(data);
for (int i = 0; i < dt.Rows.Count; ++i)
{
dt.Rows[i]["NO"] = i + 1;
}
return dt;
}
private StockInFGData GetData()
{
StockInFGData data = new StockInFGData();
data.CODE = this.txtSTCode.Text.Trim();
data.RECEIVEFROM = this.ctlReceiveFrom.DateValue;
data.RECEIVETO = this.ctlRecriveTo.DateValue;
data.INVNO = this.txtInvNo.Text.Trim();
data.POCODE = this.txtPoCode.Text.Trim();
data.ORDERFROM = this.ctlOrderFrom.DateValue;
data.ORDERTO = this.ctlOrderTo.DateValue;
data.SENDER = Convert.ToDouble(this.cmbSupplier.SelectedValue);
data.QCCODE = this.txtQCCode.Text.Trim();
data.STATUSFROM = this.cmbStatusFrom.SelectedItem.Value;
data.STATUSTO = this.cmbStatusTo.SelectedItem.Value;
return data;
}
private void SetData(StockInFGData data)
{
if (data.LOID == 0)
{
data.ACTIVE = Constz.ActiveStatus.Active;
data.RECEIVEDATE = DateTime.Now.Date;
data.STATUS = Constz.Requisition.Status.Waiting.Code;
data.CREATEBY = Authz.CurrentUserInfo.UserID;
}
this.txtCode.Text = data.CODE;
this.txtLOID.Text = data.LOID.ToString();
this.txtInvNo.Text = data.INVNO;
this.txtStatus.Text = data.STATUS;
this.txtSender.Text = data.SENDER.ToString();
this.txtSenderCode.Text = "";
this.txtSenderName.Text = "";
this.txtRemark.Text = data.REMARK;
this.txtCreateBy.Text = data.CREATEBY;
this.txtStockInCode.Text = data.CODE;
this.txtQCCode.Text = data.QCCODE;
this.ctlReceiveDate.DateValue = data.RECEIVEDATE;
this.txtStatusName.Text = (data.STATUS == Constz.Requisition.Status.Approved.Code ? Constz.Requisition.Status.Approved.Name : (data.STATUS == Constz.Requisition.Status.Void.Code ? Constz.Requisition.Status.Void.Name : (data.STATUS == Constz.Requisition.Status.QC.Code ? Constz.Requisition.Status.QC.Name : (data.STATUS == Constz.Requisition.Status.Finish.Code ? Constz.Requisition.Status.Finish.Name : Constz.Requisition.Status.Waiting.Name))));
SetSender(data.SENDER);
SetToolbar(data.STATUS);
SetGrvItem(data.STATUS);
if (data.STATUS != Constz.Requisition.Status.Waiting.Code)
{
this.ctlToolbar.BtnSaveShow = false;
this.ctlToolbar.BtnCancelShow = false;
this.btnSearch.Visible = false;
this.txtSenderCode.CssClass = "zTextbox-View";
this.txtInvNo.CssClass = "zTextbox-View";
this.txtSenderCode.ReadOnly = true;
this.txtInvNo.ReadOnly = true;
if (data.STATUS == Constz.Requisition.Status.QC.Code || data.STATUS == Constz.Requisition.Status.Finish.Code)
{
this.ctlToolbar.BtnSubmitShow = false;
}
}
this.ctlToolbar2.BtnSubmitShow = (data.STATUS == Constz.Requisition.Status.Approved.Code);
this.ctlToolbar.ClientClickPrint = ABB.Global.Appz.ReportScript(Constz.Report.F_FG_16_R00, Convert.ToDouble(this.txtLOID.Text == "" ? "0" : this.txtLOID.Text)) + " return false;";
this.ctlToolbar2.ClientClickPrint = ABB.Global.Appz.ReportScript(Constz.Report.StockInSupplier, Convert.ToDouble(this.txtLOID.Text == "" ? "0" : this.txtLOID.Text)) + " return false;";
this.ctlToolbar3.ClientClickPrint = ABB.Global.Appz.ReportScript(Constz.Report.StockinSupplierInitial, Convert.ToDouble(this.txtLOID.Text == "" ? "0" : this.txtLOID.Text)) + " return false;";
}
public StockInFGData GetData(double loid)
{
StockInFGData data = new StockInFGData();
if (DALObj.GetDataByLOID(loid, null))
{
data.LOID = DALObj.LOID;
data.CODE = DALObj.CODE;
data.CREATEBY = DALObj.CREATEBY;
data.SENDER = DALObj.SENDER;
data.QCCODE = DALObj.QCCODE;
data.REMARK = DALObj.REMARK;
data.RECEIVEDATE = DALObj.RECEIVEDATE;
data.STATUS = DALObj.STATUS;
data.INVNO = DALObj.INVNO;
data.RECEIVER = DALObj.RECEIVER;
}
return data;
}
private void SearchData()
{
this.txtRefNo.Text = "";
SearchFlow flow = new SearchFlow();
StockInFGData data = new StockInFGData();
data.CODETO = this.txtCodeTo.Text.Trim();
data.CODEFROM = this.txtCodeFrom.Text.Trim();
data.DATEFROM = this.ctlDateFrom.DateValue;
data.DATETO = this.ctlDateTo.DateValue;
data.PRODUCT = Convert.ToDouble(this.cmbProduct.SelectedValue);
data.SENDER = Convert.ToDouble(this.cmbSupplier.SelectedValue);
this.grvReserve.DataSource = flow.GetProductWHList(data);
this.grvReserve.DataBind();
// this.btnSelect.Visible = (this.grvReserve.Rows.Count > 0);
if (this.grvReserve.SelectedValue == null)
this.txtRefNo.Text = "";
else
this.txtRefNo.Text = this.grvReserve.SelectedValue.ToString();
}
public bool UpdateData(string userID, StockInFGData data)
{
bool ret = true;
if (ValidateData(data))
{
OracleDBObj obj = new OracleDBObj();
obj.CreateConnection();
obj.CreateTransaction();
try
{
UpdateData(userID, data, obj.zTrans);
obj.zTrans.Commit();
obj.CloseConnection();
}
catch (Exception ex)
{
obj.zTrans.Rollback();
obj.CloseConnection();
ret = false;
_error = ex.Message;
}
}
else
ret = false;
return ret;
}
public bool CommitQCData(string userID, StockInFGData data)
{
bool ret = true;
if (ValidateData(data))
{
OracleDBObj obj = new OracleDBObj();
obj.CreateConnection();
obj.CreateTransaction();
try
{
data.QCCODE = OracleDB.GetRunningCode("STOCKIN_QC", data.DOCTYPE.ToString());
data.QCDATE = DateTime.Now.Date;
UpdateData(userID, data, obj.zTrans);
ret = DALObj.CutStock(_LOID, userID, obj.zTrans);
if (!ret) throw new ApplicationException(DALObj.ErrorMessage);
obj.zTrans.Commit();
obj.CloseConnection();
}
catch (Exception ex)
{
obj.zTrans.Rollback();
obj.CloseConnection();
ret = false;
_error = ex.Message;
}
}
else
ret = false;
return ret;
}
private void UpdateData(string userID, StockInFGData data, System.Data.OracleClient.OracleTransaction zTrans)
{
bool ret = true;
DALObj.OnDB = false;
DALObj.GetDataByLOID(data.LOID, zTrans);
DALObj.SENDER = data.SENDER;
DALObj.RECEIVEDATE = data.RECEIVEDATE;
DALObj.REMARK = data.REMARK;
DALObj.STATUS = data.STATUS;
DALObj.INVNO = data.INVNO;
DALObj.QCCODE = data.QCCODE;
DALObj.RECEIVER = data.RECEIVER;
DALObj.DOCTYPE = data.DOCTYPE;
DALObj.QCDATE = data.QCDATE;
if (DALObj.OnDB)
ret = DALObj.UpdateCurrentData(userID, zTrans);
else
ret = DALObj.InsertCurrentData(userID, zTrans);
_LOID = DALObj.LOID;
if (!ret)
{
throw new ApplicationException(DALObj.ErrorMessage);
}
StockInItemDAL itemDAL = new StockInItemDAL();
itemDAL.DeleteDataByStockIn(data.LOID, zTrans);
for (Int16 i = 0; i < data.ITEM.Count; ++i)
{
StockInItemData item = (StockInItemData)data.ITEM[i];
itemDAL.PRODUCT = item.PRODUCT;
itemDAL.QTY = item.QTY;
itemDAL.PRICE = item.PRICE;
itemDAL.STOCKIN = DALObj.LOID;
itemDAL.REFLOID = item.REFLOID;
itemDAL.REFTABLE = "POITEM";
itemDAL.LOTNO = item.LOTNO;
itemDAL.QCQTY = item.QCQTY;
if (item.QCRESULT == Constz.QCResult.Pass.Name)
itemDAL.QCRESULT = Constz.QCResult.Pass.Code;
else if (item.QCRESULT == Constz.QCResult.Fail.Name)
itemDAL.QCRESULT = Constz.QCResult.Fail.Code;
else
itemDAL.QCRESULT = "";
itemDAL.QCREMARK = item.QCREMARK;
itemDAL.ACTIVE = Constz.ActiveStatus.Active;
itemDAL.STATUS = data.STATUS;
itemDAL.UNIT = item.UNIT;
itemDAL.PRICE = item.PRICE;
itemDAL.REMARK = item.REMARK;
itemDAL.OnDB = false;
ret = itemDAL.InsertCurrentData(userID, zTrans);
if (!ret) throw new ApplicationException(DALObj.ErrorMessage);
}
}
public bool ValidateData(StockInFGData data)
{
bool ret = true;
if (data.ITEM.Count == 0)
{
ret = false;
_error = "¡ÃسÒÃкØÃÒ¡ÒÃÊÔ¹¤éÒ";
}
else if (data.INVNO == "")
{
ret = false;
_error = "¡ÃسÒÃкØàÅ¢·ÕèãºÊ觢ͧ";
}
else
{
for (Int16 i = 0; i < data.ITEM.Count; ++i)
{
StockInItemData item = (StockInItemData)data.ITEM[i];
if (item.LOTNO == "")
{
ret = false;
_error = "¡ÃسÒÃÐºØ Lot No";
}
}
}
return ret;
}
public DataTable GetReceiveOTList(StockInFGData whereData)
{
string whereString = " DOCTYPE = " + 23 + " ";
if (whereData.CODE.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "UPPER(CODE) = '" + OracleDB.QRText(whereData.CODE.Trim()).ToUpper() + "' ";
if (whereData.QCCODE.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "UPPER(QCCODE) = '" + OracleDB.QRText(whereData.QCCODE.Trim()).ToUpper() + "' ";
if (whereData.INVNO.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "UPPER(INVNO) = '" + OracleDB.QRText(whereData.INVNO.Trim()).ToUpper() + "' ";
if (whereData.POCODE.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "LOID IN (SELECT DISTINCT STOCKIN FROM STOCKINITEM STI INNER JOIN POITEM POI ON STI.REFTABLE = 'POITEM' AND STI.REFLOID = POI.LOID INNER JOIN PDORDER PO ON POI.PDORDER = PO.LOID WHERE UPPER(PO.CODE) = '" + OracleDB.QRText(whereData.POCODE.Trim()).ToUpper() + "') ";
if (whereData.RECEIVEFROM.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "RECEIVEDATE >= " + OracleDB.QRDate(whereData.RECEIVEFROM) + " ";
if (whereData.RECEIVETO.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "RECEIVEDATE <= " + OracleDB.QRDate(whereData.RECEIVETO) + " ";
if (whereData.ORDERFROM.Year != 1 && whereData.ORDERTO.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "LOID IN (SELECT DISTINCT STOCKIN FROM STOCKINITEM STI INNER JOIN POITEM POI ON STI.REFTABLE = 'POITEM' AND STI.REFLOID = POI.LOID INNER JOIN PDORDER PO ON POI.PDORDER = PO.LOID WHERE ORDERDATE >= " + OracleDB.QRDate(whereData.ORDERFROM) + " AND ORDERDATE <= " + OracleDB.QRDate(whereData.ORDERTO) + ") ";
if (whereData.SENDER != 0)
whereString += (whereString == "" ? "" : "AND ") + "SENDER = " + whereData.SENDER.ToString() + " ";
if (whereData.STATUSFROM.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "RANK >= " + OracleDB.QRText(whereData.STATUSFROM.Trim()) + " ";
if (whereData.STATUSTO.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "RANK <= " + OracleDB.QRText(whereData.STATUSTO.Trim()) + " ";
string sql = "SELECT ROWNUM NO, A.* FROM (SELECT ST.LOID, ST.CODE, ST.DOCTYPE, ST.RECEIVEDATE,ST.SENDER, ST.INVNO,SP.SUPPLIERNAME ,ST.QCCODE, ";
sql += "CASE ST.STATUS WHEN '" + Constz.Requisition.Status.Waiting.Code + "' THEN '" + Constz.Requisition.Status.Waiting.Name + "' ";
sql += "WHEN '" + Constz.Requisition.Status.QC.Code + "' THEN '" + Constz.Requisition.Status.QC.Name + "' ";
sql += "WHEN '" + Constz.Requisition.Status.Approved.Code + "' THEN '" + Constz.Requisition.Status.Approved.Name + "' ";
sql += "WHEN '" + Constz.Requisition.Status.Void.Code + "' THEN '" + Constz.Requisition.Status.Void.Name + "' ";
sql += "WHEN '" + Constz.Requisition.Status.Finish.Code + "' THEN '" + Constz.Requisition.Status.Finish.Name + "' ";
sql += "ELSE '' END AS STATUSNAME, ";
sql += "CASE ST.STATUS WHEN '" + Constz.Requisition.Status.Waiting.Code + "' THEN '" + Constz.Requisition.Status.Waiting.Rank + "' ";
sql += "WHEN '" + Constz.Requisition.Status.QC.Code + "' THEN '" + Constz.Requisition.Status.QC.Rank + "' ";
sql += "WHEN '" + Constz.Requisition.Status.Approved.Code + "' THEN '" + Constz.Requisition.Status.Approved.Rank + "' ";
sql += "WHEN '" + Constz.Requisition.Status.Void.Code + "' THEN '" + Constz.Requisition.Status.Void.Rank + "' ";
sql += "WHEN '" + Constz.Requisition.Status.Finish.Code + "' THEN '" + Constz.Requisition.Status.Finish.Rank + "' ";
sql += "ELSE '' END AS RANK ";
sql += "FROM STOCKIN ST LEFT JOIN SUPPLIER SP ON ST.SENDER = SP.LOID)A ";
sql += (whereString == "" ? "" : "WHERE " + whereString);
sql += "ORDER BY CODE DESC";
return OracleDB.ExecListCmd(sql);
}
public static DataTable GetProductOTList(StockInFGData data)
{
string where = " REMAIN > 0 ";
if (data.PRODUCT != 0)
where += (where == "" ? "" : "AND ") + "PDLOID = " + data.PRODUCT.ToString() + " ";
if (data.CODEFROM.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(CODE) >= '" + OracleDB.QRText(data.CODEFROM.Trim()).ToUpper() + "' ";
if (data.CODETO.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(CODE) <= '" + OracleDB.QRText(data.CODETO.Trim()).ToUpper() + "' ";
if (data.DATEFROM.Year != 1)
where += (where == "" ? "" : "AND ") + "TO_DATE(DUEDATE, 'DDMMYYYY') >= " + OracleDB.QRDate(data.DATEFROM) + " ";
if (data.DATETO.Year != 1)
where += (where == "" ? "" : "AND ") + "TO_DATE(DUEDATE, 'DDMMYYYY') <= " + OracleDB.QRDate(data.DATETO) + " ";
if (data.SENDER != 0)
where += (where == "" ? "" : "AND ") + "SPLOID = " + data.SENDER.ToString() + " ";
string sql = "SELECT * FROM V_STOCKINSUPPLIEROT_LIST ";
sql += (where == "" ? "" : "WHERE " + where);
sql += "ORDER BY CODE ";
return OracleDB.ExecListCmd(sql);
}
public static DataTable GetOrderProductList(StockInFGData data)
{
string where = " REMAIN > 0 ";
if (data.PRODUCT != 0)
where += (where == "" ? "" : "AND ") + "PDLOID = " + data.PRODUCT.ToString() + " ";
if (data.CODEFROM.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(CODE) >= '" + OracleDB.QRText(data.CODEFROM.Trim()).ToUpper() + "' ";
if (data.CODETO.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(CODE) <= '" + OracleDB.QRText(data.CODETO.Trim()).ToUpper() + "' ";
if (data.DATEFROM.Year != 1)
where += (where == "" ? "" : "AND ") + "TO_DATE(DUEDATE, 'DDMMYYYY') >= " + OracleDB.QRDate(data.DATEFROM) + " ";
if (data.DATETO.Year != 1)
where += (where == "" ? "" : "AND ") + "TO_DATE(DUEDATE, 'DDMMYYYY') <= " + OracleDB.QRDate(data.DATETO) + " ";
if (data.SENDER != 0)
where += (where == "" ? "" : "AND ") + "SPLOID = " + data.SENDER.ToString() + " ";
string sql = "SELECT * FROM (SELECT PRI.DUEDATE ,PD.BARCODE,PD.LOID PDLOID, PD.NAME PDNAME, PD.PRICE,PO.LOID, PO.CODE,POI.QTY,A.AMOUNT,POI.QTY-NVL(A.AMOUNT,0) AS REMAIN,U.LOID UNIT, U.NAME UNITNAME,SP.LOID SPLOID,SP.SUPPLIERNAME SUPPLIER,POI.LOID REFLOID ";
sql += "FROM PDORDER PO INNER JOIN POITEM POI ON POI.PDORDER = PO.LOID ";
sql += "INNER JOIN PRITEM PRI ON POI.PRITEM = PRI.LOID INNER JOIN PDREQUEST PR ON PR.LOID = PRI.PDREQUEST ";
sql += "INNER JOIN V_PRODUCT_LIST PD ON POI.PRODUCT = PD.LOID AND PD.TYPE = 'FG' INNER JOIN UNIT U ON POI.UNIT = U.LOID ";
sql += "INNER JOIN SUPPLIER SP ON PO.SUPPLIER = SP.LOID ";
sql += "LEFT JOIN (SELECT REFLOID,SUM(QTY) AMOUNT FROM STOCKINITEM GROUP BY REFLOID,REFTABLE HAVING REFTABLE = 'POITEM') A ";
sql += "ON A.REFLOID = POI.LOID)A ";
sql += (where == "" ? "" : "WHERE " + where);
sql += "ORDER BY CODE ";
return OracleDB.ExecListCmd(sql);
}
private StockInFGData GetData()
{
StockInFGData data = new StockInFGData();
data.LOID = Convert.ToDouble(this.txtLOID.Text == "" ? "0" : this.txtLOID.Text);
data.ACTIVE = Constz.ActiveStatus.Active;
data.CODE = this.txtCode.Text;
data.SENDER = Convert.ToDouble(this.txtSender.Text == "" ? "0" : this.txtSender.Text);
data.ITEM = ItemObj.GetItemList();
data.REMARK = this.txtRemark.Text.Trim();
data.STATUS = this.txtStatus.Text.Trim();
data.RECEIVEDATE = this.ctlReceiveDate.DateValue;
data.INVNO = this.txtInvNo.Text.Trim();
data.DOCTYPE = 23;
data.RECEIVER = Authz.CurrentUserInfo.Warehouse;
return data;
}
public DataTable GetProductOTList(StockInFGData data)
{
return SearchDAL.GetProductOTList(data);
}