public List <Impm1_Enquiry> Get_Impm1_Enquiry_List(Impm request)
{
List <Impm1_Enquiry> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
string strSql = "Select IsNull(Impm1.ProductCode,'') AS ProductCode, IsNull(Impm1.ProductName,'') AS ProductName," +
"IsNull(Impm1.GoodsReceiveorIssueNo,'') AS GoodsReceiveorIssueNo, IsNull(Impm1.RefNo,'') AS RefNo," +
"IsNull(Impm1.StoreNo,'') AS StoreNo, " +
"(CASE Impm1.DimensionFlag When '1' THEN Impm1.BalancePackingQty When '2' THEN Impm1.BalanceWholeQty ELSE Impm1.BalanceLooseQty END) AS BalanceQty " +
"From Impm1 Where ";
string strFilter = " (CASE Impm1.DimensionFlag When '1' THEN Impm1.BalancePackingQty When '2' THEN Impm1.BalanceWholeQty ELSE Impm1.BalanceLooseQty END) >0 ";
if (!string.IsNullOrEmpty(request.ProductCode))
{
strFilter = strFilter + " AND ProductCode='" + Modfunction.SQLSafe(request.ProductCode) + "'";
}
else if (!string.IsNullOrEmpty(request.WarehouseCode) && !string.IsNullOrEmpty(request.StoreNo))
{
strFilter = strFilter + " AND WarehouseCode='" + Modfunction.SQLSafe(request.WarehouseCode) + "' And StoreNo='" + Modfunction.SQLSafe(request.StoreNo) + "'";
}
else if (!string.IsNullOrEmpty(request.ProductTrxNo))
{
strFilter = strFilter + " AND ProductTrxNo = " + int.Parse(request.ProductTrxNo);
}
Result = db.Select <Impm1_Enquiry>(strSql + strFilter);
}
}
catch { throw; }
return(Result);
}
private string generateOnhandNo()
{
ONHAND_D pair;
var prefixRules = "";
var runningNo = "";
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
try
{
List <sanm1> sanm1 = db.Select <sanm1>("SELECT Prefix, NextNo FROM sanm1 WHERE NumberType = 'OMOH'");
prefixRules = Modfunction.CheckNull(sanm1[0].Prefix);
runningNo = Modfunction.CheckNull(sanm1[0].NextNo);
pair = generateTransactionNo(prefixRules, runningNo);
string strSql = "";
strSql = "NextNo=" + Modfunction.SQLSafeValue(pair.NextNo) + "";
db.Update("sanm1",
strSql,
"numbertype='OMOH' ");
}
catch (Exception ex)
{
throw ex;
}
}
return(pair.TrxNo);
}
public object Get_Impm1_Transfer_List(Impm request)
{
List <Impm1_Transfer_Tree> ResultTrees = new List <Impm1_Transfer_Tree>();
List <Impm1_Transfer> Results = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
if ((request.WarehouseCode != null && request.WarehouseCode != "") && (request.StoreNo != null && request.StoreNo != ""))
{
string strSql = "Select Impm1.TrxNo, Impm1.BatchLineItemNo, IsNull(ProductCode,'') AS name, IsNull(ProductCode,'') AS ProductCode," +
"IsNull(ProductName,'') AS ProductName, IsNull(GoodsReceiveorIssueNo,'') AS GoodsReceiveorIssueNo, IsNull(UserDefine1,'') AS UserDefine1," +
"b.QtyBal, '' AS FromToStoreNo, 0 AS ScanQty , '' as TreeLineItemNo ,''objectTrxNo " +
"From Impm1 Join (Select (Select top 1 Imit1.StatusCode from imit1 Where imit1.GoodsTransferNoteNo = a.GoodsReceiveorIssueNo) AS ImitStatus, a.TrxNo, " +
"(CASE a.DimensionFlag When '1' THEN a.BalancePackingQty When '2' THEN a.BalanceWholeQty ELSE a.BalanceLooseQty END) AS QtyBal From Impm1 a ) b on b.TrxNo = impm1.TrxNo " +
"Where WarehouseCode='" + request.WarehouseCode + "' And ( impm1.TrxType =1 or impm1.TrxType =3) And StoreNo='" + request.StoreNo + "' And (b.ImitStatus = 'EXE' or ImitStatus is null) And b.QtyBal>0 ";
if (request.CustomerCode != null && request.CustomerCode != "")
{
strSql = strSql + " AND CustomerCode = " + Modfunction.SQLSafeValue(request.CustomerCode);
}
strSql = strSql + " order by impm1.ProductCode ";
Results = db.Select <Impm1_Transfer>(strSql);
for (int i = 0; i < Results.Count; i++)
{
string BatchNo = Results[i].name;
Impm1_Transfer impm1 = Results[i];
bool blnExistBatchNo = false;
foreach (Impm1_Transfer_Tree ResultTree in ResultTrees)
{
if (ResultTree.name.Equals(BatchNo))
{
blnExistBatchNo = true;
impm1.TreeLineItemNo = ResultTree.tree.Count;
impm1.objectTrxNo = ResultTree.tree[0].objectTrxNo;
ResultTree.tree.Add(impm1);
}
}
if (!blnExistBatchNo)
{
Impm1_Transfer_Tree impm1_tree = new Impm1_Transfer_Tree();
impm1_tree.name = BatchNo;
impm1_tree.tree = new List <Impm1_Transfer>();
impm1.TreeLineItemNo = 0;
impm1.objectTrxNo = ResultTrees.Count;
impm1_tree.tree.Add(impm1);
ResultTrees.Add(impm1_tree);
}
}
}
}
}
catch { throw; }
return(ResultTrees);
}
public int Update_Saal(Saal request)
{
int Result = -1;
int intResult = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
if (request.PrimaryKeyLineItemNo > 0)
{
intResult = db.Scalar <int>(
"Select Max(lineItemNo) from saal1 where Tablename = {0} AND PrimaryKeyName = {1} AND PrimaryKeyValue = {2} and PrimaryKeyLineItemNo = {3}",
request.TableName, request.PrimaryKeyName, request.PrimaryKeyValue, request.PrimaryKeyLineItemNo
);
if (request.TableName == "Imgr2" || request.TableName == "Imgi2")
{
db.ExecuteSql("Update " + request.TableName + " Set " + request.FieldName +
" = " + Modfunction.SQLSafeValue(request.NewValue) + " Where TrxNo = " +
Modfunction.SQLSafeValue(request.PrimaryKeyValue) + " AND LineItemNo = " +
Modfunction.SQLSafeValue(request.PrimaryKeyLineItemNo.ToString()));
}
}
else
{
intResult = db.Scalar <int>(
"Select Max(lineItemNo) from saal1 where Tablename = {0} AND PrimaryKeyName = {1} AND PrimaryKeyValue = {2} ",
request.TableName, request.PrimaryKeyName, request.PrimaryKeyValue
);
}
if (intResult > 0)
{
intResult = intResult + 1;
}
else
{
intResult = 1;
}
if (request.PrimaryKeyLineItemNo > 0)
{
db.ExecuteSql("insert into saal1 (Tablename, PrimaryKeyName, PrimaryKeyValue,PrimaryKeyLineItemNo ,LineItemNo, FieldName, NewValue, OldValue) values ('" +
Modfunction.SQLSafe(request.TableName) + "','" + Modfunction.SQLSafe(request.PrimaryKeyName) + "'," + Modfunction.SQLSafeValue(request.PrimaryKeyValue) + "," + Modfunction.SQLSafeValue(request.PrimaryKeyLineItemNo.ToString()) + "," + intResult.ToString() + ",'" +
Modfunction.SQLSafe(request.FieldName) + "'," + Modfunction.SQLSafeValue(request.NewValue) + "," + Modfunction.SQLSafeValue(request.OldValue) + ")");
}
else
{
db.ExecuteSql("insert into saal1 (Tablename, PrimaryKeyName, PrimaryKeyValue,PrimaryKeyLineItemNo ,LineItemNo, FieldName, NewValue, OldValue) values ('" +
Modfunction.SQLSafe(request.TableName) + "','" + Modfunction.SQLSafe(request.PrimaryKeyName) + "'," + Modfunction.SQLSafeValue(request.PrimaryKeyValue) + "," + Modfunction.SQLSafeValue(request.PrimaryKeyLineItemNo.ToString()) + "," + intResult.ToString() + ",'" +
Modfunction.SQLSafe(request.FieldName) + "'," + Modfunction.SQLSafeValue(request.NewValue) + "," + Modfunction.SQLSafeValue(request.OldValue) + ")");
}
}
}
catch { throw; }
return(Result);
}
public int Comfirm_Smar1(Smar request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection())
{
if (request.UpdateAllString != null && request.UpdateAllString != "")
{
JArray ja = (JArray)JsonConvert.DeserializeObject(request.UpdateAllString);
if (ja != null)
{
for (int i = 0; i < ja.Count(); i++)
{
string strSql = "";
string PhoneNumber = ja[i]["PhoneNumber"].ToString();
string PassWord = ja[i]["PassWord"].ToString();
string YogaStudioName = ja[i]["YogaStudioName"].ToString();
string Sex = ja[i]["Sex"].ToString();
string Remark = ja[i]["Remark"].ToString();
string AreaCode = ja[i]["AreaCode"].ToString();
strSql = "insert into Samr1( " +
" YogaStudioName," +
" AreaCode," +
" Sex," +
" Remark ," +
" PhoneNumber ," +
" PassWord ," +
" CreateDateTime," +
" UpdateDateTime" +
" )" +
"values( " +
Modfunction.SQLSafeValue(YogaStudioName) + "," +
Modfunction.SQLSafeValue(AreaCode) + "," +
Modfunction.SQLSafeValue(Sex) + "," +
Modfunction.SQLSafeValue(Remark) + "," +
Modfunction.SQLSafeValue(PhoneNumber) + "," +
Modfunction.SQLSafeValue(PassWord) + "," +
"GETDATE()," +
"GETDATE()" +
") ";
db.ExecuteSql(strSql);
}
Result = 1;
}
}
}
}
catch { throw; }
return(Result);
}
private void grd_sasr1_SelectionChanged(object sender, EventArgs e)
{
if (grd_sasr1.RowCount > 0)
{
if (grd_sasr1.CurrentRow != null)
{
{
txt_RequestDes.Text = Modfunction.CheckNull(grd_sasr1.Rows[grd_sasr1.CurrentRow.Index].Cells["RequestDescription"].Value).ToString().Replace("\n", "\r\n");
txt_TestSummary.Text = grd_sasr1.Rows[grd_sasr1.CurrentRow.Index].Cells["TestSummary"].Value.ToString().Replace("\n", "\r\n");
this.txt_RequestDescription.Text = grd_sasr1.Rows[grd_sasr1.CurrentRow.Index].Cells["RequestDescription"].Value.ToString().Replace("\n", "\r\n");
txt_ProgramingSummary.Text = Modfunction.CheckNull(grd_sasr1.Rows[grd_sasr1.CurrentRow.Index].Cells["ProgrammingSummary"].Value).ToString().Replace("\n", "\r\n");
}
}
}
}
public int ConfirmAll_Jmjm1(Jmjm request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection())
{
if (request.confirmAllString != null && request.confirmAllString != "")
{
JArray ja = (JArray)JsonConvert.DeserializeObject(request.confirmAllString);
if (ja != null)
{
for (int i = 0; i < ja.Count(); i++)
{
string strJobNo = "";
string strActualArrivalDate = "";
string strDeliveryDate = "";
if (ja[i]["JobNo"] != null || ja[i]["JobNo"].ToString() != "")
{
strJobNo = ja[i]["JobNo"].ToString();
}
strActualArrivalDate = ja[i]["ActualArrivalDate"].ToString();
strDeliveryDate = ja[i]["DeliveryDate"].ToString();
if (strJobNo != "")
{
if (strActualArrivalDate != "")
{
db.Update("Jmjm3",
" DateTime = '" + Modfunction.SQLSafe(strActualArrivalDate) + "'",
" JobNo='" + strJobNo + "' and Description = 'ACTUAL ARRIVAL DATE'");
}
if (strDeliveryDate != "")
{
db.Update("Jmjm1",
" DeliveryDateTime = '" + Modfunction.SQLSafe(strDeliveryDate) + "'",
" JobNo='" + strJobNo + "'");
}
}
}
Result = 1;
}
}
}
}
catch { throw; }
return(Result);
}
public void SaveInsertGroupNameEvent(string strInsertNextGroupCode, int intLineItem, string strJobNo, string strUserId)
{
List <Jmjm3> ResultJmjm3 = null;
List <Jmeg2> ResultJmeg2 = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("TMS"))
{
int intMaxLineItemNo = 0;
string strSelectJmjm3 = "Select Max(LineItemNo) as LineItemNo from Jmjm3 Where JobNo = '" + strJobNo + "'";
ResultJmjm3 = db.Select <Jmjm3>(strSelectJmjm3);
if (ResultJmjm3.Count > 0)
{
intMaxLineItemNo = ResultJmjm3[0].LineItemNo + 1;
}
string strSelectJmeg2 = "Select EventCode from Jmeg2 Where GroupCode = '" + strInsertNextGroupCode + "'";
ResultJmeg2 = db.Select <Jmeg2>(strSelectJmeg2);
if (ResultJmeg2.Count > 0)
{
if (intLineItem > 0)
{
string StrUpdateJmjm7 = "Update Jmjm7 Set JobLineItemNo = JobLineItemNo + " + ResultJmeg2.Count + " Where JobNo = '" + strJobNo + "' AND JobLineItemNo > " + intLineItem;
db.ExecuteSql(StrUpdateJmjm7);
string StrUpdateJmjm4 = "Update Jmjm4 Set JobLineItemNo = JobLineItemNo + " + ResultJmeg2.Count + " Where JobNo = '" + strJobNo + "' AND JobLineItemNo > " + intLineItem;
db.ExecuteSql(StrUpdateJmjm4);
string StrUpdateJmjm3 = "update Jmjm3 Set LineItemNo = LineItemNo + " + ResultJmeg2.Count + " Where JobNo = '" + strJobNo + "' AND LineItemNo > " + intLineItem;
db.ExecuteSql(StrUpdateJmjm3);
intMaxLineItemNo = intLineItem + 1;
}
for (int i = 0; i < ResultJmeg2.Count; i++)
{
string StrInsertJmjm3 = "INSERT INTO Jmjm3 (JobNo, LineItemNo,EventCode, Description, ShowETrackFlag,Remark,UpdateBy, UpdateDateTime) " + "Select '" + strJobNo + "'," + intMaxLineItemNo + ",EventCode,Description,EtrackFlag,Remark,'" + strUserId + "', getdate() from Jmje1 Where EventCode= '" + ResultJmeg2[i].EventCode + "'";
db.ExecuteSql(StrInsertJmjm3);
UpdateEventListToJmjm4(Modfunction.CheckNull(ResultJmeg2[i].EventCode), intMaxLineItemNo, strJobNo);
intMaxLineItemNo = intMaxLineItemNo + 1;
}
}
}
}
catch { throw; }
}
public Impr1 Get_Impr1_Single(Impr request)
{
Impr1 Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
List <Impa1> impa1 = db.Select <Impa1>("Select * from Impa1");
string strBarCodeFiled = impa1[0].BarCodeField;
string[] strBarCodeList = strBarCodeFiled.Split(',');
if (strBarCodeList.Length <= 1)
{
string strSql = "Select * From Impr1 Where IsNull(ProductCode,'')<>'' And IsNull(StatusCode,'')<>'DEL' And " + strBarCodeFiled + "='" + Modfunction.SQLSafe(request.BarCode) + "'";
Result = db.QuerySingle <Impr1>(strSql);
}
else
{
string strBarCodeFilter = "";
for (int i = 0; i < strBarCodeList.Length; i++)
{
if (strBarCodeFilter == "")
{
strBarCodeFilter = strBarCodeList[i] + "='" + Modfunction.SQLSafe(request.BarCode) + "'";
}
else
{
strBarCodeFilter = strBarCodeFilter + " OR " + strBarCodeList[i] + "='" + Modfunction.SQLSafe(request.BarCode) + "'";
}
}
if (strBarCodeFilter != "")
{
string strSql = "Select * From Impr1 Where IsNull(ProductCode,'')<>'' And IsNull(StatusCode,'')<>'DEL' And (" + strBarCodeFilter + ")";
Result = db.QuerySingle <Impr1>(strSql);
}
else
{
string strSql = "Select top 0 * From Impr1 ";
Result = db.QuerySingle <Impr1>(strSql);
}
}
}
}
catch { throw; }
return(Result);
}
public List <Impa1> Get_Impa1_List()
{
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
string strSQL = "Select isNull(ShowCycleCountAppFlag,'N') as ShowCycleCountAppFlag,BarCodeField ,isnull(AppTallyConfirmStatus,'') as AppTallyConfirmStatus, isnull(AppPutawayConfirmStatus,'') as AppPutawayConfirmStatus ,isnull(AppPickConfirmStatus,'') as AppPickConfirmStatus ,isnull(AppIssueVerifyStatus,'') as AppIssueVerifyStatus from Impa1";
ResultImpa1 = db.Select <Impa1>(strSQL);
AppTallyConfirmStatus = Modfunction.CheckNull(ResultImpa1[0].AppTallyConfirmStatus);
AppPutawayConfirmStatus = Modfunction.CheckNull(ResultImpa1[0].AppPutawayConfirmStatus);
AppPickConfirmStatus = Modfunction.CheckNull(ResultImpa1[0].AppPickConfirmStatus);
AppIssueVerifyStatus = Modfunction.CheckNull(ResultImpa1[0].AppIssueVerifyStatus);
}
}
catch { throw; }
return(ResultImpa1);
}
public int Confirm_Imit1(Imit request)
{
int Result = -1;
Boolean blnSameCustomer = false;
string OldCustomerCode = "", CustomerCode = "", CustomerName = "";
if (request.NewStoreNoList != null || request.NewStoreNoList != "")
{
string[] NewSotreNoDetail = request.NewStoreNoList.Split(',');
string[] LineItemNoDetail = request.LineItemNoList.Split(',');
string[] Impm1TrxNoDetail = request.Impm1TrxNoList.Split(',');
string[] QtyDetail = request.QtyList.Split(',');
for (int intI = 0; intI < NewSotreNoDetail.Length; intI++)
{
Result = Insert_Imit2Detail(int.Parse(Impm1TrxNoDetail[intI]), int.Parse(request.TrxNo), int.Parse(LineItemNoDetail[intI]), int.Parse(QtyDetail[intI]), NewSotreNoDetail[intI], request.UpdateBy, ref CustomerCode, ref CustomerName);
if (OldCustomerCode == "" && CustomerCode != "")
{
OldCustomerCode = CustomerCode;
blnSameCustomer = true;
}
else if (OldCustomerCode != CustomerCode)
{
blnSameCustomer = false;
}
}
if (blnSameCustomer)
{
}
}
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
//string strSql = "EXEC spi_Imit_Confirm " + int.Parse(request.TrxNo) + ",'" + request.UpdateBy + "'"; 'yicong 20161019
string strCustoemrUpdate = "";
if (blnSameCustomer)
{
strCustoemrUpdate = ",CustomerCode = " + Modfunction.SQLSafeValue(CustomerCode);
}
string strSql = "Update Imit1 set StatusCode='EXE',UpdateBy='" + request.UpdateBy + "'" + strCustoemrUpdate + " where TrxNo='" + request.TrxNo + "' ";
Result = db.SqlScalar <int>(strSql);
}
}
catch { throw; }
return(Result);
}
public int LoginCheck(WmsLogin request)
{
int Result = -1;
try
{
string com = "SELECT count(*) FROM saus1 WHERE UserId =" + Modfunction.SQLSafeValue(request.UserName) + " And Password="******"";
using (var db = DbConnectionFactory.OpenDbConnection())
{
Result = db.Scalar <int>(com);
}
}
catch
{
Result = -1;
}
return(Result);
}
public int confirm_Tobk1(Tobk request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection())
{
string str;
str = " Note = " + Modfunction.SQLSafeValue(request.Remark) + ",OnBehalfName = " + Modfunction.SQLSafeValue(request.OnBehalfName) + ",StatusCode = 'POD'";
db.Update(request.TableName,
str,
" BookingNo='" + request.Key + "'");
}
}
catch { throw; }
return(Result);
}
public List <Imgr2_Receipt> Get_Imgr2_Receipt_List(Imgr request)
{
List <Imgr2_Receipt> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
string strSql = "Select Imgr2.*,'' As QtyStatus, " +
"" + getBarCodeListSelect() +
"(Select Top 1 SerialNoFlag From Impr1 Where TrxNo=Imgr2.ProductTrxNo) AS SerialNoFlag," +
"0 AS ScanQty,GoodsReceiptNoteNo " +
"From Imgr2 " +
"Left Join Imgr1 On Imgr2.TrxNo = Imgr1.TrxNo " +
"Where Imgr1.GoodsReceiptNoteNo='" + Modfunction.SQLSafe(request.GoodsReceiptNoteNo) + "'";
Result = db.Select <Imgr2_Receipt>(strSql);
}
}
catch { throw; }
return(Result);
}
public string GoodsTransfer(string strAsn, string strConfirmBy)
{
string Result = "";
string strBarCode = GetBarCode("UserDefine01");
DataTable dtWhScan = GetSQLCommandReturnDT("Select *,Impr1.TrxNo AS PrductTrxNo,Impr1.CustomerCode AS ProductCustomerCode from WhScan join Impr1 On Impr1." + strBarCode + " = WhScan.BarCode Where AsnNo = " + Modfunction.SQLSafeValue(strAsn) + " AND ComfirmBy = " + Modfunction.SQLSafeValue(strConfirmBy));
Boolean blnSameCustomer = true;
if (dtWhScan != null && dtWhScan.Rows.Count > 0)
{
string strCustomer = Modfunction.CheckNull(dtWhScan.Rows[0]["ProductCustomerCode"]);
for (int i = 1; i < dtWhScan.Rows.Count; i++)
{
if (strCustomer != Modfunction.CheckNull(dtWhScan.Rows[0]["ProductCustomerCode"]))
{
blnSameCustomer = false;
break;
}
}
Result = SaveImit(dtWhScan, blnSameCustomer);
}
return(Result);
}
public string Insert_Aemt1(List <Pid_AEMT1> objAemt1)
{
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
string KeyMAwbNo = setKeyMAwbNo(Modfunction.CheckNull(objAemt1[0].MAwbNo));
for (int i = 0; i < objAemt1.Count; i++)
{
string strSql = "";
string TallyById = "";
string MAwbNo = Modfunction.CheckNull(objAemt1[i].MAwbNo);
string PID_NO = Modfunction.CheckNull(objAemt1[i].PID_NO);
string LOC_CODE = Modfunction.CheckNull(objAemt1[i].LOC_CODE);
strSql = "insert into Aemt1( " +
" KeyMAwbNo ," +
" MAwbNo," +
" PID_NO, " +
" LOC_CODE, " +
" TallyById," +
" TallyByDateTime" +
" )" +
"values( " +
Modfunction.SQLSafeValue(KeyMAwbNo) + ", " +
Modfunction.SQLSafeValue(MAwbNo) + " , " +
Modfunction.SQLSafeValue(PID_NO) + " , " +
Modfunction.SQLSafeValue(LOC_CODE) + " , " +
Modfunction.SQLSafeValue(TallyById) + "," +
" GetDate()" +
") ";
db.ExecuteSql(strSql);
}
;
}
}
catch { throw; }
return("");
}
public int InsertResult(WhScan request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection())
{
string strSQLCommand = "";
if (request.ClearData == "Y")
{
strSQLCommand = "Delete WhScan Where ComfirmBy = " + Modfunction.SQLSafeValue(request.UserID) + " AND ConfrimType = " + Modfunction.SQLSafeValue(request.ConfrimType);
Result = db.Scalar <int>(strSQLCommand);
}
strSQLCommand = "insert into whscan(AsnNo,BarCode,ConfirmBy,CustomerCode,GoodsIssueNoteNo,ConfrimType,OldStoreNo,ProductCode,Qty,StoreNo,SerialNo,WarehouseCode,OldWarehouseCode,IssueNoteLineItemNo) Values(" + Modfunction.SQLSafeValue(request.AsnNo) + "," + Modfunction.SQLSafeValue(request.BarCode) + "," + Modfunction.SQLSafeValue(request.UserID) + "," + Modfunction.SQLSafeValue(request.CustomerCode) + "," + Modfunction.SQLSafeValue(request.GoodsIssueNoteNo) + "," + Modfunction.SQLSafeValue(request.ConfrimType) + "," + Modfunction.SQLSafeValue(request.OldStoreNo) + "," + Modfunction.SQLSafeValue(request.ProductCode) + "," + Modfunction.SQLSafeValue(request.Qty) + "," + Modfunction.SQLSafeValue(request.StoreNo) + "," + Modfunction.SQLSafeValue(request.SerialNo) + "," + Modfunction.SQLSafeValue(request.WarehouseCode) + "," + Modfunction.SQLSafeValue(request.OldWarehouseCode) + "," + Modfunction.SQLSafeValue(request.IssueNoteLineItemNo) + ")";
Result = db.Scalar <int>(strSQLCommand);
}
}
catch
{
Result = -1;
}
return(Result);
}
public int Update_verify_imgi1(Imgi request)
{
Get_Impa1_List();
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
if (Modfunction.CheckNull(AppIssueVerifyStatus) != "")
{
Result = db.Update <Imgi1>(
new
{
StatusCode = AppIssueVerifyStatus
},
p => p.TrxNo == int.Parse(request.TrxNo)
);
}
}
}
catch { throw; }
return(Result);
}
public int confirm_Aemp1WithAido1(Aemp_Aido request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection())
{
if (request.TableName == "Aemp1")
{
db.Update(request.TableName,
" Remark = '" + request.Remark + "',StatusCode = 'POD',ActualPickUpDateTime=getdate(),SignBy='" + Modfunction.CheckNull(request.SignBy) + "'",
" TrxNo='" + request.Key + "'");
}
else if (request.TableName == "Sido1")
{
db.Update(request.TableName,
" Remark = '" + request.Remark + "',StatusCode = 'POD',SignBy='" + Modfunction.CheckNull(request.SignBy) + "'",
" TrxNo='" + request.Key + "'");
}
else
{
db.Update(request.TableName,
" Remark = '" + request.Remark + "',StatusCode = 'POD',ActualDeliveryDateTime=getdate(),SignBy='" + Modfunction.CheckNull(request.SignBy) + "'",
" DeliveryOrderNo='" + request.Key + "'");
}
}
}
catch { throw; }
return(Result);
}
public List <Todr1> GetTodr1(Tms_Login request) //20160511
{
List <Todr1> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("TMS"))
{
var strSQL = "";
if (BlnContactNo == false)
{
strSQL = "select isnull(DriverCode,'') as DriverCode,isnull(DriverName,'') as DriverName from todr1 where ContactNo2=" + Modfunction.SQLSafeValue(request.ContactNo);
Result = db.Select <Todr1>(strSQL);
}
else
{
strSQL = "select isnull(DriverCode,'') as DriverCode,isnull(DriverName,'') as DriverName from todr1 where ContactNo1=" + Modfunction.SQLSafeValue(request.ContactNo);
Result = db.Select <Todr1>(strSQL);
}
}
}
catch { throw; }
return(Result);
}
public List <Impm1_UserDefine> Get_Impm1_List(Impm request)
{
List <Impm1_UserDefine> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
List <Impa1> impa1 = db.Select <Impa1>("Select * from Impa1");
string strBarCodeFiled = impa1[0].BarCodeField;
string[] strBarCodeList = strBarCodeFiled.Split(',');
string strSql = "";
string strSqlDetail = "";
if (strBarCodeList.Length <= 1)
{
strSqlDetail = "Select Cast(" + strBarCodeFiled + " AS nvarchar(2000)) AS UserDefine1,TrxNo from Impr1 Where " + strBarCodeFiled + " = '" + Modfunction.SQLSafe(request.UserDefine1) + "'";
}
else
{
for (int i = 0; i < strBarCodeList.Length; i++)
{
string strSqlNew = "Select Cast(" + strBarCodeList[i] + " AS nvarchar(2000)) AS UserDefine1,TrxNo from Impr1 Where " + strBarCodeList[i] + " = '" + Modfunction.SQLSafe(request.UserDefine1) + "'";
if (strSqlDetail == "")
{
strSqlDetail = strSqlNew;
}
else
{
strSqlDetail = strSqlDetail + " union all " + strSqlNew;
}
}
}
if (strSqlDetail != "")
{
strSql = "Select Top 10 * From (" + strSqlDetail + ") a " +
"Order By a.UserDefine1 ASC";
Result = db.Select <Impm1_UserDefine>(strSql);
}
}
}
catch { throw; }
return(Result);
}
public int LoginCheck(Wms_Login request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
if (request.UpdateAllString != null && request.UpdateAllString != "")
{
JArray ja = (JArray)JsonConvert.DeserializeObject(request.UpdateAllString);
if (ja != null)
{
for (int i = 0; i < ja.Count(); i++)
{
string strSqlSaus1 = "";
string strUserId = ja[i]["UserId"].ToString();
string strPassword = ja[i]["Password"].ToString();
//strSqlSaus1 = "Select count(*) From Saus1 Where UserId='" + Modfunction.CheckNull(strUserId) + "' And Password='******'";
strSqlSaus1 = "Select count(*) From Saus1 Where UserId='" + Modfunction.CheckNull(strUserId) + "'";
Result = db.Scalar <int>(strSqlSaus1);
}
}
}
}
}
catch { throw; }
return(Result);
}
public string GetUserInfo(Event_Login request)
{
string Result = "";
try
{
using (var db = DbConnectionFactory.OpenDbConnection("TMS"))
{
Result = db.QuerySingle <string>("Select Top 1 ISNULL(DriverName,'') From Jmjm4 Where PhoneNumber=" + Modfunction.SQLSafeValue(request.PhoneNumber));
}
}
catch { throw; }
return(Result);
}
public List <Imgi2_Verify> Get_Imgi2_Verify_List(Imgi request)
{
List <Imgi2_Verify> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
string strSql = "";
strSql = "Select RowNum = ROW_NUMBER() OVER (ORDER BY Imgi2.StoreNo ASC), " +
"Imgi2.*, " +
"(Select Top 1 UserDefine1 From Impm1 Where TrxNo=Imgi2.ReceiptMovementTrxNo) AS SerialNo," +
"" + getBarCodeListSelect() +
"(Select Top 1 SerialNoFlag From Impr1 Where TrxNo=Imgi2.ProductTrxNo) AS SerialNoFlag," +
"(CASE Imgi2.DimensionFlag When '1' THEN Imgi2.PackingQty When '2' THEN Imgi2.WholeQty ELSE Imgi2.LooseQty END) AS Qty, " +
"0 AS QtyBal, 0 AS ScanQty,ReceiptMovementTrxNo " +
"From Imgi2 " +
"Left Join Imgi1 On Imgi2.TrxNo=Imgi1.TrxNo " +
"Where (IsNull(Imgi1.StatusCode,'')='USE' Or IsNull(Imgi1.StatusCode,'')='CMP') And Imgi1.GoodsIssueNoteNo='" + Modfunction.SQLSafe(request.GoodsIssueNoteNo) + "'";
Result = db.Select <Imgi2_Verify>(strSql);
}
}
catch { throw; }
return(Result);
}
public Impr1 GetList(List_Impr1 request)
{
Impr1 Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
Result = db.QuerySingle <Impr1>(
"Select * From Impr1 Where IsNull(ProductCode,'')<>'' And IsNull(StatusCode,'')<>'DEL' And UserDefine01=" + Modfunction.SQLSafeValue(request.BarCode)
);
}
}
catch { throw; }
return(Result);
}
public int UpdateAll_Aemp1WithAido1(Aemp_Aido request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection())
{
if (request.UpdateAllString != null && request.UpdateAllString != "")
{
JArray ja = (JArray)JsonConvert.DeserializeObject(request.UpdateAllString);
if (ja != null)
{
for (int i = 0; i < ja.Count(); i++)
{
if (ja[i]["TableName"] == null || ja[i]["TableName"].ToString() == "")
{
continue;
}
string strKey = ja[i]["Key"].ToString();
string strTableName = ja[i]["TableName"].ToString();
string strRemark = "";
string strStatusCode = "";
if (ja[i]["Remark"] != null || ja[i]["Remark"].ToString() != "")
{
strRemark = ja[i]["Remark"].ToString();
}
if (ja[i]["StatusCode"] != null || ja[i]["StatusCode"].ToString() != "")
{
strStatusCode = ja[i]["StatusCode"].ToString();
}
if (strStatusCode.ToLower() == "cancel")
{
string strJobNo = "";
if (ja[i]["JobNo"] != null || ja[i]["JobNo"].ToString() != "")
{
strJobNo = ja[i]["JobNo"].ToString();
}
if (strJobNo != "")
{
int intMaxLineItemNo = 1;
List <Jmjm3> list1 = db.Select <Jmjm3>("Select Max(LineItemNo) LineItemNo from Jmjm3 Where JobNo = " + Modfunction.SQLSafeValue(strJobNo));
if (list1 != null)
{
if (list1[0].LineItemNo > 0)
{
intMaxLineItemNo = list1[0].LineItemNo + 1;
}
}
db.Insert(new Jmjm3
{
JobNo = strJobNo,
DateTime = DateTime.Now,
UpdateDatetime = DateTime.Now,
LineItemNo = intMaxLineItemNo,
AutoFlag = "N",
StatusCode = "CANCEL",
UpdateBy = ja[0]["DriverCode"] == null ? "" : Modfunction.SQLSafe(ja[0]["DriverCode"].ToString()),
Remark = Modfunction.SQLSafe(strRemark),
Description = ja[0]["CancelDescription"] == null ? "" : Modfunction.SQLSafe(ja[0]["CancelDescription"].ToString())
});
if (strTableName == "Aemp1")
{
db.Update(strTableName,
" Remark = '" + Modfunction.SQLSafe(strRemark) + "'",
" TrxNo='" + strKey + "'");
}
else if (strTableName == "Sido1")
{
db.Update(strTableName,
" Remark = '" + Modfunction.SQLSafe(strRemark) + "'",
" TrxNo='" + strKey + "'");
}
else
{
db.Update(strTableName,
" Remark = '" + Modfunction.SQLSafe(strRemark) + "'",
" DeliveryOrderNo='" + strKey + "'");
}
}
}
else
{
if (strTableName == "Aemp1")
{
db.Update(strTableName,
" Remark = '" + Modfunction.SQLSafe(strRemark) + "',StatusCode = '" + strStatusCode + "',SignBy='" + Modfunction.CheckNull(request.SignBy) + "'",
" TrxNo='" + strKey + "'");
}
else if (strTableName == "Sido1")
{
db.Update(strTableName,
" Remark = '" + Modfunction.SQLSafe(strRemark) + "',StatusCode = '" + strStatusCode + "',SignBy='" + Modfunction.CheckNull(request.SignBy) + "'",
" TrxNo='" + strKey + "'");
}
else
{
db.Update(strTableName,
" Remark = '" + Modfunction.SQLSafe(strRemark) + "',StatusCode = '" + strStatusCode + "',SignBy='" + Modfunction.CheckNull(request.SignBy) + "'",
" DeliveryOrderNo='" + strKey + "'");
}
}
}
Result = 1;
}
}
}
}
catch { throw; }
return(Result);
}
public int ConfirmAll_Imcc2(imcc request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection())
{
if (request.UpdateAllString != null && request.UpdateAllString != "")
{
JArray ja = (JArray)JsonConvert.DeserializeObject(request.UpdateAllString);
if (ja != null)
{
for (int i = 0; i < ja.Count(); i++)
{
int TrxNo;
int LineItemNo;
int ProductTrxNo = 0;
int PackingQty;
int WholeQty;
int LooseQty;
if (ja[i]["TrxNo"] != null || ja[i]["TrxNo"].ToString() != "")
{
if (ja[i]["LineItemNo"] != null || ja[i]["LineItemNo"].ToString() != "")
{
TrxNo = int.Parse(ja[i]["TrxNo"].ToString());
LineItemNo = int.Parse(ja[i]["LineItemNo"].ToString());
if (ja[i]["ProductTrxNo"] != null || ja[i]["ProductTrxNo"].ToString() != "")
{
ProductTrxNo = int.Parse(ja[i]["ProductTrxNo"].ToString());
}
if (ja[i]["PackingQtyTempValue"].ToString() == "")
{
PackingQty = 0;
}
else
{
PackingQty = int.Parse(ja[i]["PackingQtyTempValue"].ToString());
}
if (ja[i]["WholeQtyTempValue"].ToString() == "")
{
WholeQty = 0;
}
else
{
WholeQty = int.Parse(ja[i]["WholeQtyTempValue"].ToString());
}
if (ja[i]["LooseQtyTempValue"].ToString() == "")
{
LooseQty = 0;
}
else
{
LooseQty = int.Parse(ja[i]["LooseQtyTempValue"].ToString());
}
db.ExecuteSql("insert into impm1 (CustomerCode, WarehouseCode, StoreNo,ProductCode ,Description, DimensionFlag, PackingQty, WholeQty,LooseQty,ProductTrxNo,UpdateBy) values (" +
Modfunction.SQLSafeValue(ja[i]["CustomerCode"].ToString()) + "," + Modfunction.SQLSafeValue(ja[i]["WarehouseCode"].ToString()) + "," + Modfunction.SQLSafeValue(ja[i]["StoreNo"].ToString()) + "," + Modfunction.SQLSafeValue(ja[i]["ProductCode"].ToString()) + "," + Modfunction.SQLSafeValue(ja[i]["Description"].ToString()) + "," +
Modfunction.SQLSafeValue(ja[i]["DimensionFlag"].ToString()) + "," + PackingQty + "," + WholeQty + "," + LooseQty + "," + ProductTrxNo + "," + Modfunction.SQLSafeValue(ja[i]["UserId"].ToString()) + ")");
string str;
string strTableName = "imcc2";
str = " PackingQty = " + PackingQty + ",WholeQty = " + WholeQty + ",LooseQty = " + LooseQty + ", UpdateBy=" + Modfunction.SQLSafeValue(ja[i]["UserId"].ToString()) + "";
db.Update(strTableName,
str,
" TrxNo=" + TrxNo + " and LineItemNo=" + LineItemNo + "");
}
}
}
Result = 1;
}
}
}
}
catch { throw; }
return(Result);
}
public List <Imgi1> Get_Imgi1_List(Imgi request)
{
List <Imgi1> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
if (!string.IsNullOrEmpty(request.CustomerCode))
{
if (!string.IsNullOrEmpty(request.StatusCode))
{
Result = db.Select <Imgi1>("Select * from imgi1 where StatusCode = 'EXE' AND Convert(Char(10), IssueDateTime, 20) = Convert(Char(10), getdate(), 20) AND CustomerCode = " + Modfunction.SQLSafeValue(request.CustomerCode));
//Result = db.SelectParam<Imgi1>(
// i => i.CustomerCode != null && i.CustomerCode != "" && i.StatusCode != null && i.StatusCode != "DEL" && i.StatusCode != "EXE" && i.CustomerCode == request.CustomerCode
//).OrderByDescending(i => i.IssueDateTime).ToList<Imgi1>();
}
else
{
Result = db.SelectParam <Imgi1>(
i => i.CustomerCode != null && i.CustomerCode != "" && i.StatusCode != null && i.StatusCode != "DEL" && i.StatusCode != "EXE" && i.StatusCode != "CMP" && i.CustomerCode == request.CustomerCode
).OrderByDescending(i => i.IssueDateTime).ToList <Imgi1>();
}
}
else if (!string.IsNullOrEmpty(request.GoodsIssueNoteNo))
{
if (!string.IsNullOrEmpty(request.StatusCode))
{
Result = db.Select <Imgi1>("Select * from imgi1 where StatusCode = 'EXE' AND Convert(Char(10), IssueDateTime, 20) = Convert(Char(10), getdate(), 20) AND GoodsIssueNoteNo like '" + Modfunction.SQLSafe(request.GoodsIssueNoteNo) + "%'");
//Result = db.SelectParam<Imgi1>(
// i => i.CustomerCode != null && i.CustomerCode != "" && i.StatusCode != null && i.StatusCode != "DEL" && i.StatusCode != "EXE" && i.GoodsIssueNoteNo.StartsWith(request.GoodsIssueNoteNo)
//).OrderByDescending(i => i.IssueDateTime).ToList<Imgi1>();
}
else
{
Result = db.SelectParam <Imgi1>(
i => i.CustomerCode != null && i.CustomerCode != "" && i.StatusCode != null && i.StatusCode != "DEL" && i.StatusCode != "EXE" && i.StatusCode != "CMP" && i.GoodsIssueNoteNo.StartsWith(request.GoodsIssueNoteNo)
).OrderByDescending(i => i.IssueDateTime).ToList <Imgi1>();
}
}
}
}
catch { throw; }
return(Result);
}
public int Update_Imgr2_QtyRemark(Imgr request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
if (request.QtyFieldName == "PackingQty")
{
Result = db.Update <Imgr2>(
new
{
PackingQty = request.QtyRemarkQty,
UpdateBy = request.UserID
},
p => p.TrxNo == int.Parse(request.TrxNo) && p.LineItemNo == int.Parse(request.LineItemNo)
);
Result = db.Update("Impm1", "BalancePackingQty = " + request.QtyRemarkQty
,
" BatchNo = " + Modfunction.SQLSafeValue(request.GoodsReceiptNoteNo) + " AND BatchLineItemNo = " + Modfunction.SQLSafeValue(request.LineItemNo)
);
}
else if (request.QtyFieldName == "WholeQty")
{
Result = db.Update <Imgr2>(
new
{
WholeQty = request.QtyRemarkQty,
UpdateBy = request.UserID
},
p => p.TrxNo == int.Parse(request.TrxNo) && p.LineItemNo == int.Parse(request.LineItemNo)
);
Result = db.Update("Impm1", "BalanceWholeQty = " + request.QtyRemarkQty
,
" BatchNo = " + Modfunction.SQLSafeValue(request.GoodsReceiptNoteNo) + " AND BatchLineItemNo = " + Modfunction.SQLSafeValue(request.LineItemNo)
);
}
else
{
Result = db.Update <Imgr2>(
new
{
LooseQty = request.QtyRemarkQty,
UpdateBy = request.UserID
},
p => p.TrxNo == int.Parse(request.TrxNo) && p.LineItemNo == int.Parse(request.LineItemNo)
);
Result = db.Update("Impm1", "BalanceLooseQty = " + request.QtyRemarkQty
,
" BatchNo = " + Modfunction.SQLSafeValue(request.GoodsReceiptNoteNo) + " AND BatchLineItemNo = " + Modfunction.SQLSafeValue(request.LineItemNo)
);
}
Result = db.Update <Imgr1>(" Remark=isnull(Remark,'') + (case isnull(Remark,'') when '' then '' else char(13)+char(10) end) + " + Modfunction.SQLSafeValue(request.QtyRemark) + ",UpdateDateTime = getdate(),UpdateBy = " + Modfunction.SQLSafeValue(request.UserID)
,
" TrxNo = " + request.TrxNo
);
}
}
catch { throw; }
return(Result);
}
public int UpdateAll_tjms2(Tobk request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection())
{
if (request.UpdateAllString != null && request.UpdateAllString != "")
{
JArray ja = (JArray)JsonConvert.DeserializeObject(request.UpdateAllString);
if (ja != null)
{
for (int i = 0; i < ja.Count(); i++)
{
int ChargeBerthQty;
int ChargeLiftingQty;
if (ja[i]["TrxNo"] == null || ja[i]["TrxNo"].ToString() == "")
{
continue;
}
string strTrxNo = ja[i]["TrxNo"].ToString();
string strLineItemNo = ja[i]["LineItemNo"].ToString();
string SignedByName = ja[i]["SignedByName"].ToString();
string SignedByNric = ja[i]["SignedByNric"].ToString();
string SignedByDesignation = ja[i]["SignedByDesignation"].ToString();
string CompanyName = ja[i]["CompanyName"].ToString();
string strDateCompleted = ja[i]["DateCompleted"].ToString();
string OfficeInChargeName = ja[i]["OfficeInChargeName"].ToString();
if (ja[i]["ChargeBerthQty"].ToString() == "")
{
ChargeBerthQty = 0;
}
else
{
ChargeBerthQty = int.Parse(ja[i]["ChargeBerthQty"].ToString());
}
if (ja[i]["ChargeLiftingQty"].ToString() == "")
{
ChargeLiftingQty = 0;
}
else
{
ChargeLiftingQty = int.Parse(ja[i]["ChargeLiftingQty"].ToString());
}
string ChargeOther = ja[i]["ChargeOther"].ToString();
DateTime dt = DateTime.Now;
if (strDateCompleted != "" && strDateCompleted != null)
{
strDateCompleted = strDateCompleted + " " + dt.GetDateTimeFormats('t')[0].ToString();
}
string str;
if (strLineItemNo != "0")
{
str = " SignedByName = " + Modfunction.SQLSafeValue(SignedByName) + ",SignedByNric= " + Modfunction.SQLSafeValue(SignedByNric) + ",SignedByDesignation= " + Modfunction.SQLSafeValue(SignedByDesignation) + ",DateCompleted=" + Modfunction.SQLSafeValue(strDateCompleted) + ",ChargeBerthQty=" + ChargeBerthQty + ",ChargeLiftingQty=" + ChargeLiftingQty + ",ChargeOther=" + Modfunction.SQLSafeValue(ChargeOther) + "";
db.Update("tjms1",
str,
" TrxNo='" + strTrxNo + "' ");
str = "OfficeInChargeName= " + Modfunction.SQLSafeValue(OfficeInChargeName) + "";
db.Update("tjms2",
str,
" TrxNo='" + strTrxNo + "' ");
str = " CompanyName = " + Modfunction.SQLSafeValue(CompanyName) + "";
db.Update("saco1",
str
);
}
}
Result = 1;
}
}
}
}
catch { throw; }
return(Result);
}
